Summe der Einzelsätze einer Gruppe

[Fuerchau]

Dies ist definitiv falsch:
"Der Count(*) und auch Count(Spalte) ist jeweils ein Count(Distinct, d.h. Duplikate werden NICHT gezählt."

Count(*) zählt alles
Count(Spalte) zählt alles was nicht NULL ist
Count(distinct Spalte) zählt alles was nicht NULL und eindeutig ist.
Dies gilt schon so lange, wie ich SQL mache.
Dies gilt auch für die over()-Funktionen.

Ein "count(*) over(partition by f1, f2) ... group by f1, f2" macht überhaupt keinen Sinn, da das Ergebnis immer 1 ist.
Bei "count(*) over(partition by f1) ... group by f1, f2" ergibt die Zählung die Anzahl der Gruppen F1, allerdings werden dann wieder alle Zeilen ausgegeben.

Ein count(*) over() zählt immer alle Zeilen des Ergebisses.

Die Frage stellt sich nun, was denn genau gezählt werden soll, in der Regel reicht da ein over() ohne einen finalen Group by, da man die Anzahl bestimmter Vorkommen haben will.

Z.B:
select f1, f2
,count(*) over() Gesamtergebniss
,count(*) over(partition by f1) AnzahlGruppenF1
,count(distinct f2) over() AnzahlEindeutigGesamt
from mytable
group by f1, f2

Und was ist gegen eine klassische Prozentrechnung einzuwenden;-)?

[ILEMax]

Na ja Baldur, ich kenne die Theorie nicht.
Aber in der Praxis stimmt das Ergebnis halt nicht.

[Fuerchau]

Also bisher konnte ich die Ergebnisse immer verifizieren. Gerechnet wurde richtig.
Deine Erwartung mag da eine andere sein.
Wie gesagt, ein over() Ergebnis wird auf jede Zeile innerhalb des Resultsets ausgegeben.
Gerechnet wird also erst nach dem das Resultset komplett ist.

Select F1, f2, Count(*) Count0, 
       Count(*) over(partition By F1, F2) Count1,
       Count(*) over(Partition by F1) Count2
  from x
  Group By F1, F2;

Hier wird gezählt nach der Gruppierung von Group By F1, F2!

Du hast also 10 A/B Zeilen und 14 A/C-Zeilen.
Innerhalb der Gruppe F1/F2 kommt diese Zeile je 1 Mal vor.
Innerhalb der Gruppe F1 kommt A eben 2 mal vor.

F1  F2  Count0  Count1  Count2
A   B   10      1       2 
A   C   14      1       2

Das hat mit distinct hier nichts zu tun, da du ja nur 2 Ergebniszeilen hast.
Lass mal das Group by am Ende weg, so dass du alle 34 Zeilen siehst.
Dabei muss der erste Count0 mit over() ergänzt werden.

Count0 liefert dann 34 über alles.
Count1 liefert dann 10 bei A/B und 14 bei AC.
Count2 liefert dann wieder 34, da du nur A hast.

Und solange man keinen Distinct bei Aggregaten angibt, wird auch nicht distinct gezählt.
Was ist daran nun so schwer zu verstehen?

[ILEMax]

Ja, ggf ist meine ERWARTUNG eine andere.

Es ist gut, wenn du hier SQL Untericht gibst!

Da ich jedoch mein ERWARTUNG in#1 beschrieben habe, ERWARTE ich auch eine Antwort die dazu passt.

Und das war

gibt es so ein nettes Count(*) over() auch in einer Form die mir in obigen Bsp

24 A B 10
24 A C 14

also die Summe von 10 + 14 zurück gibt?

Daher ist in diesem Fall deine Antwort lehrreich aber für mein Problem falsch.

[Fuerchau]

Ich hatte 34 geschrieben, aber 24 (10+14) ist natürlich korrekt.
Das geht aber halt nur, in dem du keinen Group By am Ende machst.
Du hast dann 24 Zeilen, in denen Count0 auf 24 steht.

U.u. errecichst du dein Ziel von 2 Zeilen, in dem du einen "select distinct " kodierst. Dann werden doppelte Zeilen eben entfernt.

Also

24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14

Distinct dann:
24 A B 10
24 A C 14

[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.