Subquery in FROM

Ludwigmller

SQL-Guru
Beiträge
168
Hallo,
ich möchte zu jedem Datensatz in der Tabelle doku eine flaeche aus der Tabelle groesse errechnen/summieren, die aber von doku.datum abhängt. Meine Idee war eine Subquery in der FROM-Clause, die so aber offenbar nicht zuläassig ist.
Hier mein Versuch:
SQL:
select
    d.datum,
    d.mitglied_id,
    d.wert,
    subquery.flaeche
 from
     doku as d,
    (
      select          
          sum(g.reihenlaenge * g.reihenabstand) as flaeche
      from
          groesse as g
      where
          g.gueltig_von >= d.datum
          AND (g.gueltig_bis <= d.datum OR g.gueltig_bis = NULL)
          AND g.mitlgied_id = d.mitglied_id
     ) as subquery

Hier Testdaten:
Code:
create table doku
(
  id serial primary key,
  datum date,
  mitglied_id integer,
  wert integer
 
);

create table groesse(
    mitglied_id integer,
      part_id integer primary key,
      reihenlaenge integer,
      reihenabstand integer,
      gueltig_von date,
      gueltig_bis date
);
 

insert into groesse (mitglied_id, part_id, reihenlaenge, reihenabstand, gueltig_von, gueltig_bis) VALUES
(1, 1, 100, 3, '2022-01-01', NULL),
(1, 2, 150, 3, '2022-01-01', '2022-12-31'),
(1, 3, 100, 3, '2023-01-01', '2023-12-31'),
(2, 4, 300, 4, '2010-01-01', NULL),
(3, 5, 200, 3, '2020-01-01', '2021-12-31'),
(3, 6, 180, 3, '2022-01-01', '2023-12-31');

insert into doku (datum, mitglied_id, wert) VALUES
('2022-01-02', 1, 10),
('2023-01-02', 1, 15),
('2022-01-02', 2, 20),
('2023-01-02', 2, 30),
('2021-01-02', 3, 5),
('2022-01-02', 3, 10);

Wie geht es richtig?
 
Zuletzt bearbeitet:
Werbung:
Das ist grundsätzlich zulässig.

Die Referenz zur äußeren Abfrage ist aber nur dann zulässig, wenn es ein LATERAL join ist:

Code:
select
    d.datum,
    d.mitglied_id,
    d.wert,
    subquery.flaeche
from doku as d
  cross join lateral (
      select          
          sum(g.reihenlaenge * g.reihenabstand) as flaeche
      from
          groesse as g
      where
          g.gueltig_von >= d.datum
          AND (g.gueltig_bis <= d.datum OR g.gueltig_bis = NULL)
          AND g.mitlgied_id = d.mitglied_id
  ) as subquery
Ich habe das Komma aus dem FROM entfernt und durch das - in meinen Augen - klarere "cross join" ersetzt. Man sollte einfach kein Komma im FROM verwenden - nicht in 2023 ;)
 
Ein andere Möglichkeit ist, im Sub-Query zu Gruppieren und dann ein "normalen" JOIN zu verwenden:

Code:
select
    d.datum,
    d.mitglied_id,
    d.wert,
    subquery.flaeche
from doku as d
  join (
    select g.mitglied_id        
          sum(g.reihenlaenge * g.reihenabstand) as flaeche
    from groesse as g
    group by g.mitglied_id
    where
  ) as g on g.gueltig_von >= d.datum
       AND (g.gueltig_bis <= d.datum OR g.gueltig_bis = NULL)
       AND g.mitglied_id = d.mitglied_id

Die OR Bedingung ist übrigens in vielen Fällen ein Performanceproblem. Bei einem Datumsfeld welches "kein Ende" repräsentieren soll, ist es besser infinity zu verwenden anstatt NULL.

Alternativ auch ein daterange welches NULL als "ohne Ende" interpretiert

Code:
on daterange(g.gueltig_von, g.gueltig_bis, '[]') @> d.datum

Das funktioniert nicht natürlich auch mit der CROSS JOIN LATERAL Variante.

Ein GiST Index auf die daterange() Expression könnte dann hilfreich sein.
 
Ich bin mir nicht sicher ob hier ein CROSS JOIN überhaupt beabsichtigt ist, vermutlich fehlt einfach die Join-Condition. Aber defintiv keine Liste mit Tabellen per Komma getrennt im FROM-Teil, das ist wirklich pfui.

So wie dein Subquery da steht kannst du es auch 1:1 in den SELECT-Teil hängen:
Code:
select
    d.datum,
    d.mitglied_id,
    d.wert,
  (
      select         
          sum(g.reihenlaenge * g.reihenabstand) as flaeche
      from
          groesse as g
      where
          g.gueltig_von >= d.datum
          AND (g.gueltig_bis <= d.datum OR g.gueltig_bis = NULL)
          AND g.mitlgied_id = d.mitglied_id
     ) as flaeche
 from
     doku as d
Liefert vermutlich das gewünschte Ergebnis, allerdings nicht zwingend ideal. Ideal ist immer eher ein ordentlicher Join mit GROUP BY.
Code:
SELECT    d.datum,d.mitglied_id,d.wert,sum(g.reihenlaenge * g.reihenabstand) as flaeche
FROM    doku d
LEFT JOIN groesse g
ON        g.mitlgied_id = d.mitglied_id
AND        g.gueltig_von >= d.datum
AND    (    g.gueltig_bis <= d.datum
OR        g.gueltig_bis = NULL )
GROUP BY d.datum,d.mitglied_id,d.wert
 
Ich bin mir nicht sicher ob hier ein CROSS JOIN überhaupt beabsichtigt ist, vermutlich fehlt einfach die Join-Condition.
Das Sub-Query liefert genau einen Wert (die Summe) und eine Zeile. Also spielt es keine Rolle ob es ein CROSS JOIN oder ein normaler JOIN ist.

Ideal ist immer eher ein ordentlicher Join mit GROUP BY.
Nach meiner Erfahrung ist ein JOIN gegen ein Sub-Query welches die Aggregation macht, häufig schneller als eine Aggregation die mit dem Ergebnis des JOIN gemacht wird.
 
Ich habe das Komma aus dem FROM entfernt und durch das - in meinen Augen - klarere "cross join" ersetzt. Man sollte einfach kein Komma im FROM verwenden - nicht in 2023 ;)
Aber defintiv keine Liste mit Tabellen per Komma getrennt im FROM-Teil, das ist wirklich pfui.
Warum genau?
Hab das bisher immer so gemacht 🤦‍♂️

Das Problem habe ich jetzt mit CROSS JOIN LATERAL gelöst.
Obiges Beispiel war vereinfacht. Falls jmd nochmal drüber gucken möchte ob ihn was auffällt:
SQL:
WITH cte_pro_tag_schlag AS (
    SELECT
        s.datum,
        s.schlaggruppenid,
        z.schlagid,
        s.schnittart,
        s.akh,
        subquery.flaeche,
        sum(subquery.flaeche) OVER (PARTITION BY s.datum) AS schlaggruppenflaeche, --Flaeche der Schlaggruppe
            round((subquery.flaeche / sum(subquery.flaeche) OVER (PARTITION BY s.datum)),4
            ) AS anteil --Anteil Schlagflaeche an Schlaggruppe
    FROM
        schnitt.schnittstunden AS s
        JOIN grunddaten.schlaggruppenzuordnung AS z
            ON z.schlaggruppenid = s.schlaggruppenid
        CROSS JOIN LATERAL (
            SELECT
                round(sum(p.reihenanzahl * p.reihenabstand * p.reihenlaenge) / 10000, 3) AS flaeche
            FROM
                grunddaten.parzellen AS p
            WHERE
                (p.erstesstandjahr <= extract(YEAR FROM s.datum) OR p.erstesstandjahr IS NULL) --auch Schläge ohne bekanntes erstes Standjahr ausgeben
                AND (grunddaten."f_letztesStandjahr"(p.rodungsdatum) >= extract(YEAR FROM s.datum) OR p.rodungsdatum IS NULL)
                AND (grunddaten."f_letztesStandjahr"(p.gueltig_bis) >= extract(YEAR FROM s.datum) OR p.gueltig_bis IS NULL)
                AND (grunddaten.f_erstes_standjahr(p.gueltig_von) <= extract(YEAR FROM s.datum) OR p.gueltig_von IS NULL)
                AND p.schlagid = z.schlagid
        ) AS subquery
    ORDER BY
        s.datum
)

SELECT
    c.datum,
    c.schlagid,
    c.schnittart,
    ROUND(sum(c.akh * c.anteil),1) AS akh
FROM
    cte_pro_tag_schlag AS c
GROUP BY
    c.datum,
    c.schlagid,
    c.schnittart
ORDER BY
    c.datum
Das "Datum-NULL"-Problem ist noch nicht behoben.
 
Vor rund 30 Jahren wurde in SQL der explizite JOIN Operator eingeführt - für alle Varianten eines Joins. Tabellen mit Komma getrennt im FROM aufzulisten ist einfach Syntax die veraltet ist.

Die "alte" Syntax mit Komma im FROM kann halt leicht dazu führen, dass man eine Join Bedingung vergisst (und damit unabsichtlich einen cross join bekommt). Wenn man wirklich mal einen cross join benötigt, dann finde ich es besser, wenn man das auch explizit hinschreibt. Wenn Du der einzige bist, der den Code sieht macht es keinen Unterschied. Aber wenn man in einem Team arbeitet, dann ist es sehr sinnvoll das explizit hin zu schreiben. Dann weiß jeder sofort, "Ah ja, der CROSS JOIN ist beabsichtigt".
 
Werbung:
Zurück
Oben