Summe der Einzelsätze einer Gruppe

[Fuerchau]

Übrigens geht das auch klassisch:

select * from (
  Select F1, f2, Count(*) Count0, 
    from x
    Group By F1, F2
) a
cross join lateral (
  select count(*) Count1 
   from x
   where a.f1 = x.f1 and a.f2 = x.f2
)

Der Inner group by liefert

10 A B
14 A C

der Join verknüüft einzeln A,B => count 10 und A,C => Count 14

Wenn du lateral join nicht magst, gehts auch mit scalarem subselect:

select a.* 
  , ( select count(*)
        from x
        where a.f1 = x.f1 and a.f2 = x.f2
    ) Count1
from (
  Select F1, f2, Count(*) Count0, 
    from x
    Group By F1, F2
) a

Der join lateral ist wie ein scalarer Subselect zu sehen, der allerdings auch mehr als 1 Spalte liefern kann.

[Andreas_Prouza]

Pass auf, dass du nicht noch bekannt wirst für deine komplizierten Lösungen ;-)

[Fuerchau]

Ja, da ist mir noch eine einfachere Lösung tatsächlich eingefallen:

select * from (
  Select F1, f2, Count(*) Count0, 
    from x
   Group By F1, F2;
)
cross join lateral (
  select count(*) Count1
    from x
)

Der innere Select liefert die Anzahl je Gruppe

A, B, 10
A, C, 14

Der Cross join liefert die Zahl 24 und wird mit jeder Zahl verknüpft, also als Ergebnis
A, B, 10, 24
A, C, 14, 24


Der Join Lateral ist, vor allem in seiner Performance, nicht zu verachten.
Denn dieser bearbeitet den SQL so, dass keine langwierige Optimierung wie bei normelen Joins nötig ist.
Über die Where-Klausel werden die Daten ausschließlich auf die aktuelle Zeile eingeschränkt.

Beispiel:

select * from (
  select f1, f2   -- Liefert nur die Gruppen
    from x
   group by f1, f2
) a
cross join lateral (
  select count(*) Count0   -- Zählt die Ergebnisse der Gruppen f1,f2
    from x
   where a.f1 = x.f1
     and a.f2 = x.f2
)
cross join lateral (
  select count(*) Count1   -- Zählt die Ergebnisse der Gruppen f1
    from x
   where a.f1 = x.f1
)
cross join lateral (
  select count(*) Count2   -- Zählt das Gesamtergebnis
    from x
)

Ich sagte ja schon mal:
Der Join lateral wirkt wie ein scalarer subselect, der allerdings mehr als 1 Spaltenergebnis liefern kann.
In der Kombination mit Values(....) kann man bereits Teilergebnisse berechnen, auf die in folgenden join lateral wieder Bezug genommen werden kann.
Dies vereinfacht z.T. erheblich die SQL's, da sich wiederholende Berechnungen nicht benötigt werden.
Man kann mit dem Ergebnis des lateral wieder einen weiteren Join, eine Where-Klausel oder einen Order by verwenden.

[B.Hauser]

Ich weiß eigentlich nach wie vor nicht, warum Du auf dem LATERAL und verschachtelten Sub-Selects bestehst.
Bei einem CROSS JOIN wird kein LATERAL benötigt.
Also:

select * 
   from (Select F1, f2, Count(*) Count12 
            from x
            Group By F1, F2)
         Cross Join (Select Count(*) Count0 
                       from x);

Reicht völlig aus!
(und wenn man das Semi Colon nach dem Sub-Select in Deinem Beispiel weglassen würde, würde sogar Dein Beispiel laufen!)

Auch im zweiten Beispiel ist ein LATERAL (bzw. 4 LATERALs) völlig überflüssig!
Das folgende Beispiel bringt das gleiche Ergebnis völlig OHNE LATERAL

select a.F1, a.F2, Count0, Count1, Count2 
  from (select f1, f2   
           from x
           group by f1, f2) a
        Cross Join (Select Count(*) as Count0 
                      From x)
        Left Join (Select F1, Count(*) Count1
                     From x 
                     Group By F1) b
                  on a.F1 = b.F1   
        Left Join (Select F2, Count(*) Count2
                     From X
                     Group by F2) c 
                   on a.F2 = c.F2;

Anstatt der verschachtelten Sub-Selects, die spätestens beim 3 verschachtelten Sub-Select nicht mehr lesbar sicn und auch nicht einzeln getestet werden können, würde ich IMMER Common Table Expressions verwenden.
Damit würde das zweite Beispiel mit den vielen Sub-Selects wie folgt aussehen (auch wieder völlig OHNE Lateral:

With a as (Select F1, F2, Count(*) CountF12
              From x
              Group By F1, F2),
     b as (Select F1, Count(*) Count1
             From x
             Group By F1),
     c as (Select F2, Count(*) Count2
             from x
             Group By F2),
     d as (Select Count(*) Count0
              From x)        
Select a.F1, a.F2, Count0, Count1, Count2
  from a Left Outer Join b on a.F1 = b.F1
         Left Outer join c on a.F2 = c.F2
              Cross join d;

Aber warum einfach, wenn's auch kompliziert geht!

[Fuerchau]

Weil es halt einfacher ist als

 Left Join (Select F1, Count(*) Count1
                     From x 
                     Group By F1
           ) b
           on a.F1 = b.F1

und besser vom Optimizier genutzt werden kann:

 
 cross Join lateral (Select 
                      Count(*) Count1
                     From x 
                    where a.F1 = x.F1   
                   ) c

Der Optimizer benötigt keinen Group By und liefert das Ergebnis via Index quasi sofort.
Zumal, wenn du zustzlich einen Enschränkungsfilter im Where hast.
Auch die Referenz im Select ist mit c.* einfacher, da du durchaus mehrere Aggregate im join lateral verwenden kannt, denn die Group By Spalten kannst du im Haupt-Select nicht wiederholen.
Schau es dir im Visual Explain einfach an.

 
 select x.f1, x.f2
       ,c.*
   from x
 cross Join lateral (Select 
                      Count(*) Count1
                     ,sum(W1) Wert1
                     ,avg(W2) Wert2
                     From x 
                    where a.F1 = x.F1   
                   ) c

Und ich weiß nicht, warum du die beste Erfindung seit es SQL gibt, den join lateral, immer noch nicht magst.

Ich habe bei einem Kunden 6 getrennt laufende SQL's für eine Preisfindung, die jeder ca. 20 MS laufen in einen lateral join mit 6 Unions (select ... union select .... limit 1), der laut Explain nur 10 ms läuft, optimiert. Also statt 120 ms nur 10ms.

[Fuerchau]

Es gibt noch einen weiteren gravierenden Vorteil:

select x.f1, x.f2
       ,c.*
   from x
 cross Join lateral (Select 
                      Count(*) Count1
                     From x 
                    where a.F1 = x.F1   
                   ) c

Wie man hier sieht, bezieht sich die Where-Klausel auf den vorgelagerten From.

Bei dem inneren Group-By-Join kann man sich nicht auf den äußeren From beziehen.
Man kann den Bezug nur in der on-Klausel herstellen.

select x.f1, x.f2
       ,c.*
   from x
 left Join (Select f1, Count(*) Count1
             From x 
            group by f1
           ) a on a.F1 = x.F1

Benötigt man nun noch eine zusätzliche Where-Klausel, steht man vor Index-Probleme:

select x.f1, x.f2
       ,a.Count1
   from x
 left Join (Select f1, Count(*) Count1
             From x 
            where status = 1
            group by f1
           ) a on a.F1 = x.F1

Die Where-Klausel kann sich nicht auf die Gruppenfelder beziehen, da sie sonst für andere Gruppen zu einschränkend wird.
Man benötigt nun 2 Indizes, 1 x für den Status, 1 x für den Group By F1. Wobei noch nicht mal sicher ist, dass auch beide genommen werden.

Ganz anders bei left join lateral (ggf. left, wenn NULL erwartbar ist):

select x.f1, x.f2
       ,c.*
   from x
 left Join lateral (Select Count(*) Count1
                     From x 
                     where x.status = 1
                       and a.F1 = x.F1
                   ) a on 1=1

Hier kann ein Index über F1+Status direkt verwendet werden ohne über 2 Indizies mit Bitmaps zu kombinieren.
Der "on 1=1" wird da dann sogar wegoptimiert, da kein Tablezugriff erforderlich ist.
Bei komplexere Where-Klauseln ist das dann schon mal sehr hilfreich, performante SQL's zu stricken.
Gravierend vor allem, wenn unterschiedliche Tabellen erforderlich sind.

Bespiel Kursfindung:

select x.f1, x.f2, x.W1
       ,c.*
   from x
 left Join lateral 
      (Select Kurs, x.w1 * kurs as w1Betrag
        From Kurstabelle k
      where k.waehrung = x.waehrung 
         and k.datum >= x.Datum
        order by k.waehrung, k.datum
         limit 1
     ) c

Wer kennt nicht die CTE-Konstrukte, mit denen man sich via min(datum) group by Key und anschließendem inner join auf das ermittelte Datum auf die Zeile zu kommen.

Vieles ist mit lateral join möglich, wo man sich früher die Gedanken verdrehen musste oder es gar nicht funktioniert hat.

[B.Hauser]

Und ich weiß nicht, warum du die beste Erfindung seit es SQL gibt, den join lateral, immer noch nicht magst.

Weil er in den Beispielen, die du hier anführst völlig überflüssig ist.
Es ist ja nicht so, dass ich den LATERAL nicht verwende, aber nur dann wenn er notwendig ist, z.B. wenn eine Tabellen-Funktion aufgerufen wird und diese als Parameter Werte aus der ersten Tabelle benötigt. In diesem Fall muss LATERAL verwendet werden aber ansonsten nicht.

Ansonsten weiß ich nicht was Du gemessen bzw. verglichen hast. Die folgenden Beispiele liefern alle das gleiche Ergebnis. Die Varianten ohne LATERAL werden identitsch optimiert (ohne Aggregation nur über Hashes), während die Lösung mit Lateral zwei zusätzliche CACHEs und eine Aggregation erfordert, was selbst bei diesen kleinen Daten lt. Visual Explain 0.02 Sekunden kostete.

select x.f1, x.f2
       ,c.*
   from x
 cross Join lateral (Select 
                      Count(*) Count1
                     ,sum(W1) Wert1
                     ,avg(W2) Wert2
                     From x a
                    where a.F1 = x.F1   
                   ) c;

Select x.F1, X.F2, c.*
  from x join (Select F1, Count(*) Count1, Sum(W1) Wert1, Avg(W2) Wert2
                 from x
                 Group by F1) c
               on x.F1 = c.F1;

With c as (Select F1, Count(*) Count1, Sum(W1) Wert1, Avg(W2) Wert2
             from x
             Group By F1)
Select x.F1, X.F2, c.*
  from x join c on x.F1 = c.F1;

[Fuerchau]

Weil ich meist nicht mit kleinen, sondern mit großen Datenmengen arbeite.
Die Vorteile sind nicht zu vernachlässigen und haben mit Table-Function übehaupt nichts zu tun.
Table-Functions lassen sich auch mit normmalen joins verwenden, da man einfach nur "from Table(tablefunction(...))" verwendet. Ein Lateral ist auch hier nicht zangsweise erforderlich.

https://medium.com/@goldengrisha/a-d...n-7b09fcb3b745
https://www.geeksforgeeks.org/sql/la...eyword-in-sql/

Und ein bisschen älter, hatte ich schon mal geposted:
https://www.rpgpgm.com/2021/10/using...al-in-sql.html