Probleme mit Aggregatfunktion aus zwei Tabellen

Hallo,

ich versuche seit ein paar Tagen eine Aggregatfunktion, die für eine zukünftige Arbeitroutine dienen soll, zu erstellen. Nachdem ich eine zweite Tabelle als Datenquelle hinzunehmen musste stimme die Ergebniswerte nicht mehr. Ursache ist der gebildete CROSS JOIN da eine der beiden Tabellen länger ist als die andere. So werden in Tabelle 1 (die kürzere) einige Werte vielfach gezählt. Allein der Wert für ide installierbare Modulfläche, welcher aus Tabelle 2 aggregiert wird stimmt in folgendem Beispiel:

SELECT 
	sum(s.bedarf2050_kwh_a) AS bedarf_kWh_a, 
	sum(s.bedarf2050_kwh_a)*0.2 AS netzverlust,
	sum(s.bedarf2050_kwh_a) + sum(s.bedarf2050_kwh_a)*0.2 AS gesamtbedarf,
	sum(pv.modulflaeche_qm) AS instbar_modulflaeche_qm

FROM 
	siedlungsareale_wbm s, pv_st_potenziale_gis pv
	
WHERE 
	s.vg_solar LIKE '%NWS 2%' 
AND 
	ST_Covers(s.geom, pv.geom);

Anschließend habe ich verschiedene Varianten ausprobiert. Auch das Weglassen der ST_Covers (PostGIS-Funktion) und das Versehen der relevanten Einträge in Tabelle 2 mit dem gleichen Kennstring “NWS 2” brachte hier keine Abhilfe.

Was teilweise richtige Werte liefert, aber als schmutzige Lösung ausfällt ist ein DISTINCT-Eintrag in die Summenformeln:

SELECT 
        SUM(DISTINCT s.bedarf2050_kwh_a) AS bedarf_kWh_a,
        SUM(DISTINCT s.bedarf2050_kwh_a)*0.2 AS netzverlust,
        SUM(DISTINCT s.bedarf2050_kwh_a) + SUM(DISTINCT s.bedarf2050_kwh_a)*0.2 AS gesamtbedarf,
        SUM(pv.modulflaeche_qm) AS instbar_modulflaeche_qm,
        (SUM(DISTINCT s.bedarf2050_kwh_a) + SUM(DISTINCT s.bedarf2050_kwh_a)*0.2)*0.01499 AS startwert_speichergroesse
 
FROM
        siedlungsareale_wbm s, pv_st_potenziale_gis pv
       
WHERE
        pv.vg_solar LIKE '%NWS 2%'

AND
        ST_Covers(s.geom, pv.geom);

Hier erhalte ich für alle Werte aus Tabelle 1 richtige Ergebnisse, für die Modulflächen allerdings nicht. Das liegt daran, dass Einzelwerte der Modulflächen zum Teil mehrmals vorkommen. Der DISTINCT Eintrag wirft diese aus der Berechnung heraus. Das darf natürlich nicht sein, da die Query als Routine dienen soll und später unmöglich geprüft werden kann ob in jedem Einzelfall alle Werte nur einmal vorkommen.

Daher muss eine andere Lösung her. Leider stehe ich mit meinen nicht sehr umfassenden SQL-Kenntnissen etwas auf dem Schlauch. Möglich wäre eine andere Variante des JOIN, jedoch blieben alle eigenen Versuche erfolglos. Auch die Verwendung von DISTINCT für die jeweiligen Tabellen, der sich jedoch auf andere als die zu berechnenden Spalten beziehen müsste. Z.B. auf die jeweilige ID-Spalte.

Im IRC erhielt ich letze Woche einen Tipp dieser Art der Einbindung, brachte jedoch nichts:

FROM siedlungsareale_wbm s 
WHERE pv.vg_solar LIKE '%NWS 2%' AND EXISTS (SELECT TRUE FROM pv_st_potenziale_gis pv WHERE ST_Covers(s.geom, pv.geom));

Evtl. wäre jedoch auch eine Subquery ein sinnvoller Lösungsansatz. Für Ratschläge und Hinweise wäre ich sehr dankbar. Wie kann ich sicherstellen, dass für die Berechnungen der Formeln wirklich nur die vorhandenen Einträge der jeweiligen Tabellen herangezogen werden und keine seltsamen JOIN-Effekte zum Tragen kommen?

Habe mich nun mit einer Kollegin daran gemacht das ganze über eine php Skript auf mehrere Queries zu verteilen und hinterher die Ergebnisse zusammen zu werfen. Jedenfalls als Übergangslösung bis ich die Query zum laufen bringe. Irgendeine Lösung muss es doch geben. Kann ich mir anders kaum vorstellen.

Leider bin ich trotzdem auf ein weiteres, ganz Ähnliches Problem gestoßen. Hier brauche ich zwar nor eine Tabelle. Diese muss jedoch für eine Berechnung zweimal importiert werden, da ein Teil der Berechnung sich auf eine Unterselektion der ersten bezieht. Damit entsteht wieder ein CROSS JOIN und die Einträge der ersten Tabelle werden vervielfacht. Ein LEFT JOIN führt hingegen dazu, dass die erste Tabelle in der Funktion als nicht referenziert bezeichnet wird und daher einen Error ausgibt.

SELECT 
    sum(s.bedarf2050_kwh_a) AS "Bedarf_kWh_a",
    sum(sl.bedarf2050_kwh_a) AS "Bedarf_LW_kWh_a",
    sum(sl.bedarf2050_kwh_a)/sum(s.bedarf2050_kwh_a) AS "Anteil_Bedarf_LW_kWh_a"
	
FROM 
	siedlungsareale_wbm s,

(SELECT * FROM siedlungsareale_wbm s
	WHERE s.vg_solar LIKE '%Test LW%' AND s.siedl_id = 121) AS sl

WHERE 
        s.vg_solar LIKE '%Test LW%';

Es geht insbesondere um die dritte Funktion die den Anteil in Bezug auf den Gesamtbedarf berechnet. Hierfür ist eine Referenz auf beide Tabellenabschnitte notwendig. Das muss doch irgendwie lösbar sein!? Prinzipiell wäre das zwar bestimmt auch mittels Skript und Einzelqueries machbar, aber es muss doch eine SQL-Lösung für derartige Abfragen geben.

Kannst Du das mal an einem Trivialbeispiel erklären? Also einfach Tabellen foo und bar mit je 2 spalten oder so und was du erreichen willst. Das aus deinen Abfragen reverse zu erforschen hab ich nicht so den rechten bock …

Da du hier verschiedene Geometrien verschneidest, wäre es sinnvoll zu erfahren wie die denn aussehen. Können z.B. verschiedene Potentiale in verschiedenen Arealen liegen und umgekehrt. Dies würde bei einem Crossjaoi dazu führen das ggf. dinge mehrfach gezählt werden.

Ich werde mich dran versuchen. Dauert ein bisschen, vielleicht bis morgen, da ich gleich Feierabend habe und meinen Trockenentwurf noch in der Datenbank testen will. Ich hoffe die Reduktion reicht dir dann aus. :wink:

Ok. Für das Problem im ersten Post hatte ich das bereits für stackoverflow schonmal gemacht:

siedlungsareale

id | bedarf2050_kWh_a | a | b | c | vg_solar | geom
---|------------------|---|---|---|----------|-----
1  |               20 |   |   |   | NWS 2    | xxxxx
2  |               10 |   |   |   | NWS 2    | xxxxx
3  |               30 |   |   |   | NWS 2    | xxxxx
4  |                5 |   |   |   | NWS 2    | xxxxx
5  |               15 |   |   |   | NWS 2    | xxxxx

sum = 80

pv_st_potenziale_gis

id |  modulflaeche_qm | x | y | z | geom
---|------------------|---|---|---|---------
1  |            10    |   |   |   | xxxxx
2  |            10    |   |   |   | xxxxx
3  |            20    |   |   |   | xxxxx
4  |            10    |   |   |   | xxxxx
5  |            30    |   |   |   | xxxxx
6  |            30    |   |   |   | xxxxx
7  |            10    |   |   |   | xxxxx
8  |            10    |   |   |   | xxxxx
9  |            10    |   |   |   | xxxxx
10 |            10    |   |   |   | xxxxx

sum = 150



SELECT sum(s.bedarfxxxx) AS bedarf, sum(pv.mflaeche) As mflaeche
FROM siedlungsareale s, pv_st_potenziale_gis pv
WHERE s.vg_solar LIKE '%NWS 2%' AND ST_Covers(s.geom,pv.geom);

Das zu erwartende, richtige Ergebnis wäre in diesem Fall:

bedarf   | mflaeche
---------|----------
      80 |      150

Alle Werte der Spalte bedarf2050_kWh_a aus siedlungsareale und alle der Spalte modulflaeche_qm aus pv_st_potenziale_gis würden jeweils für sich summiert und ausgegeben.

Ausgegeben werden jedoch die Werte:

800  |  300

Und die DISTINCT Query:

SELECT sum(DISTINCT s.bedarfxxxx) AS bedarf, sum(DISTINCT pv.mflaeche) As mflaeche
FROM siedlungsareale s, pv_st_potenziale_gis pv
WHERE s.vg_solar LIKE '%NWS 2%' AND ST_Covers(s.geom,pv.geom);

returns:

bedarf   | mflaeche
---------|-----------
     80  |      60

Richtige Ausgabe bei bedarf, da alle Einzelwerte einzigartig sind. Bei modulflaeche_qm sind jedoch doppelte Einträge weshalb hier fehlerhafte Ergebnisse berechnet werden. DISTINCT kann also nicht die Lösung des Problems sein.

\


Und das zweite Problem/Beispiel:

Hier habe ich eine Selektion und dann nochmal eine Teilselektion einer Tabelle.

siedlungsareale

id   | bedarf2050   | sield_id  |  vg_solar
-----+--------------+-----------+----------------
  1  |        10    |   121     | NWS 2
  2  |        20    |   121     | NWS 2
  3  |        30    |   121     | NWS 2
  4  |        15    |   100     | NWS 2
  5  |         5    |   100     | NWS 2



SELECT
    sum(s.bedarf2050) AS "Bedarf_alle",
    sum(sl.bedarf2050) AS "Bedarf_121",
    sum(sl.bedarf2050)/sum(s.bedarf2050) AS "Anteil_Bedarf"
   FROM
   siedlungsareale_wbm s,
(SELECT * FROM siedlungsareale s
   WHERE s.vg_solar LIKE '%NWS 2%' AND s.siedl_id = 121) AS sl
WHERE
        s.vg_solar LIKE '%NWS 2%';

Diese Query gibt dann statt

  80 |  60 |  0.75



 240 | 300 | 1.25

aus.

Ich hoffe das war trivial genug. Ich musste die Tabellen zwar jetzt etwas anders gestalten um auf die geom-Referenzierung zu verzichten. Sonst hätte ich erst Shapefiles im GIS erzeugen müssen. Das sollte die Ergebnisse jedoch nicht beeinflussen, da ich selbes auch bei den Gesamtdaten gemacht habe was zu gleichen Ergebnissen führte.

Ich habe das ganze jetzt mit einer Kollegin gelöst in dem verschiedene Einezelqueries aus einem php-Skript gemacht werden und die Berechnungen dann teilweise erst im Skript vorgenommen werden. So haben wir jeweils nur Aggregatanfragen aus einer einzigen Tabelle. An einer Lösung wie richtige Aggregate auf verknüpften Tabellen allein mit SQL generiert werden können, bin ich jedoch nach wie vor interessiert.

Prinzipiell könntest du mir auch einen Dump der Tabellen schicken wenn es nicht zu groß ist. Aber heute komm ich wohl eher nicht dazu.

So im ersten Überblick fällt auf, das die falsche Menge genau doppelt so groß ist wie die richtige. Ich würde vermuten, das hier alle Modulflächen 2x gezählt werden, weil sie eben in zwei Polygonen liegen. Ist da ein Umrandungspolygon vorhanden oder überlagern die sich sonst irgendwie? Kannst du die Geometrien z.B. mit QGIS ansehen?

Gruß Uwe

Mit den Geometrien ist alles in Ordnung. In QGIS wie auch in der Datenbank ist jedes Polygon genau einmal vorhanden. Die Doppelungen bzw. Vervielfachungen (für den ersten Wert ist die Summe 10x größer) kommen durch die JOIN-Bedingungen zustande.
Wie gesagt, solange Aggregatabfragen dieser Art ohne jegliche JOINS von Tabellen (ob jetzt tatsächlich zwei unterschiedliche oder eine die mit verschiedenen Selektionsbedingungen mehrmals gejoint wird) nur über einen Datensatz abgefragt werden sind die gelieferten Ergebnisse immer korrekt!

Werden alle Abfragen über gesplittete Queries erledigt und mögliche Berechnungen über mehrere Datensätze erst mittels php-Skript aggregiert, stimmen die Ergebnisse.