GROUP BY aufgerollt
GROUP BY
Mit der GROUP BY-Klausel können mehrere Ergebniszeilen anhand eines oder mehrerer Kriterien zu einer Zeile zusammengefasst und darüber Aggregierungen ausgeführt werden. Wir können so beispielsweise die Menge aller verkauften Artikel pro kunde ermitteln.
SQL> select o.CUSTOMER_ID, sum(QUANTITY) QUANTITY#
2 from ORDERS o
3 join ORDER_ITEMS oi on (o.ORDER_ID = oi.ORDER_ID)
4 join PRODUCT_INFORMATION p on (p.PRODUCT_ID = oi.PRODUCT_ID)
5 where o.CUSTOMER_ID < 106
6 and p.CATEGORY_ID < 20
7 group by o.CUSTOMER_ID
8 order by o.CUSTOMER_ID;
CUSTOMER_ID QUANTITY#
----------- ----------
101 1357
102 95
103 225
104 1458
105 435
Wir können die Aggregation auch über detaillierter gestalten und zusätzlich noch nach der Produktkategorie gruppieren um genauere Einblicke in die Verteilung zu erhalten.
SQL> select o.CUSTOMER_ID, p.CATEGORY_ID, sum(QUANTITY) QUANTITY#
2 from ORDERS o
3 join ORDER_ITEMS oi on (o.ORDER_ID = oi.ORDER_ID)
4 join PRODUCT_INFORMATION p on (p.PRODUCT_ID = oi.PRODUCT_ID)
5 where o.CUSTOMER_ID < 106
6 and p.CATEGORY_ID < 20
7 group by o.CUSTOMER_ID, p.CATEGORY_ID
8 order by o.CUSTOMER_ID, p.CATEGORY_ID;
CUSTOMER_ID CATEGORY_ID QUANTITY#
----------- ----------- ----------
101 11 68
101 12 195
101 13 5
101 14 34
101 15 159
101 16 384
101 17 52
101 19 460
102 11 11
102 12 9
102 15 14
102 16 8
102 19 53
103 12 4
103 15 52
103 16 120
103 19 49
104 11 204
104 15 275
104 16 261
104 19 718
105 12 25
105 14 34
105 15 87
105 17 47
105 19 242
26 rows selected.
Allerdings ist dadurch die Summe aller bestellten Artikel pro Kunde verloren gegangen. Müssen wir also mehrere Abfragen starten um diese Informationen zu erhalten?
ROLLUP
Die Antwort ist natürlich "Nein". Man kann mit der ROLLUP-Erweiterung für GROUP BY auch Zwischensummen ermitteln. Wir passen also das Beispiel entsprechend an und erhalten folgendes Ergebnis.
SQL> select o.CUSTOMER_ID, p.CATEGORY_ID, sum(QUANTITY) QUANTITY#
2 from ORDERS o
3 join ORDER_ITEMS oi on (o.ORDER_ID = oi.ORDER_ID)
4 join PRODUCT_INFORMATION p on (p.PRODUCT_ID = oi.PRODUCT_ID)
5 where o.CUSTOMER_ID < 106
6 and p.CATEGORY_ID < 20
7 group by rollup(o.CUSTOMER_ID, p.CATEGORY_ID)
8 order by o.CUSTOMER_ID, p.CATEGORY_ID;
CUSTOMER_ID CATEGORY_ID QUANTITY#
----------- ----------- ----------
101 11 68
101 12 195
101 13 5
101 14 34
101 15 159
101 16 384
101 17 52
101 19 460
101 1357
102 11 11
102 12 9
102 15 14
102 16 8
102 19 53
102 95
103 12 4
103 15 52
103 16 120
103 19 49
103 225
104 11 204
104 15 275
104 16 261
104 19 718
104 1458
105 12 25
105 14 34
105 15 87
105 17 47
105 19 242
105 435
3570
32 rows selected.
Wie wir sehen, wurden zusätzliche Zeilen in das Ergebnis eingefügt, jeweils eine Zwischensumme für jeden Kunden und nochmal eine Gesamtsumme ganz am Ende. Wir können aber auch nur Teile davon erstellen um z.B. die Zwischensummen für alle bestellten Artikel einer Kategorie über alle Kunden zu ermitteln.
SQL> select o.CUSTOMER_ID, p.CATEGORY_ID, sum(QUANTITY) QUANTITY#
2 from ORDERS o
3 join ORDER_ITEMS oi on (o.ORDER_ID = oi.ORDER_ID)
4 join PRODUCT_INFORMATION p on (p.PRODUCT_ID = oi.PRODUCT_ID)
5 where o.CUSTOMER_ID < 106
6 and p.CATEGORY_ID < 20
7 group by p.CATEGORY_ID, rollup(o.CUSTOMER_ID)
8 order by o.CUSTOMER_ID, p.CATEGORY_ID;
CUSTOMER_ID CATEGORY_ID QUANTITY#
----------- ----------- ----------
101 11 68
101 12 195
101 13 5
101 14 34
101 15 159
101 16 384
101 17 52
101 19 460
102 11 11
102 12 9
102 15 14
102 16 8
102 19 53
103 12 4
103 15 52
103 16 120
103 19 49
104 11 204
104 15 275
104 16 261
104 19 718
105 12 25
105 14 34
105 15 87
105 17 47
105 19 242
11 283
12 233
13 5
14 68
15 587
16 773
17 99
19 1522
34 rows selected.
CUBE
Wir haben nun zwar die Zwischensummen pro Kunde oder pro Produktkategorie ermittelt, nicht aber beides auf einmal. Dafür wird statt des ROLLUP die CUBE-Erweiterung verwendet. Diese erstellt uns Zwischensummen für alle Kombinationen der Gruppenspalten.
SQL> select o.CUSTOMER_ID, p.CATEGORY_ID, sum(QUANTITY) QUANTITY#
2 from ORDERS o
3 join ORDER_ITEMS oi on (o.ORDER_ID = oi.ORDER_ID)
4 join PRODUCT_INFORMATION p on (p.PRODUCT_ID = oi.PRODUCT_ID)
5 where o.CUSTOMER_ID < 106
6 and p.CATEGORY_ID < 20
7 group by cube(o.CUSTOMER_ID, p.CATEGORY_ID)
8 order by o.CUSTOMER_ID, p.CATEGORY_ID;
CUSTOMER_ID CATEGORY_ID QUANTITY#
----------- ----------- ----------
101 11 68
101 12 195
101 13 5
101 14 34
101 15 159
101 16 384
101 17 52
101 19 460
101 1357
102 11 11
102 12 9
102 15 14
102 16 8
102 19 53
102 95
103 12 4
103 15 52
103 16 120
103 19 49
103 225
104 11 204
104 15 275
104 16 261
104 19 718
104 1458
105 12 25
105 14 34
105 15 87
105 17 47
105 19 242
105 435
11 283
12 233
13 5
14 68
15 587
16 773
17 99
19 1522
3570
40 rows selected.
Auch hier ist es wieder möglich, nur Untermengen der Gruppenspalten zu verwenden.
Funktionen
Was machen wir aber nun, wenn es nur die Zwischen- und Endsummen interessieren? Aus den Ergebnissen findet man die entsprechenden Zeilen optisch recht einfach, aber nach NULL filtern ist nicht die ideale Lösung. Dafür gibt es verschiedene Funktionen, die wir dann zum Filtern der Daten verwenden können. Die Funktion GROUPING liefert z.B. "1", wenn die Zeile ein Aggregat über die übergebene Spalte ist, ansonsten "0". Ähnlich funktioniert GROUPING_ID, das uns das Aggregierungslevel anhand der übergebenen Spalten liefert. Schauen wir uns an, wie das funktioniert.
SQL> select o.CUSTOMER_ID, p.CATEGORY_ID, sum(QUANTITY) QUANTITY#,
2 grouping(o.CUSTOMER_ID) cid,
3 grouping(p.CATEGORY_ID) pid,
4 grouping_id(o.CUSTOMER_ID, p.CATEGORY_ID) grp
5 from ORDERS o
6 join ORDER_ITEMS oi on (o.ORDER_ID = oi.ORDER_ID)
7 join PRODUCT_INFORMATION p on (p.PRODUCT_ID = oi.PRODUCT_ID)
8 where o.CUSTOMER_ID < 106
9 and p.CATEGORY_ID < 20
10 group by cube(o.CUSTOMER_ID, p.CATEGORY_ID)
11 order by o.CUSTOMER_ID, p.CATEGORY_ID;
CUSTOMER_ID CATEGORY_ID QUANTITY# CID PID GRP
----------- ----------- ---------- ---------- ---------- ----------
101 11 68 0 0 0
101 12 195 0 0 0
101 13 5 0 0 0
101 14 34 0 0 0
101 15 159 0 0 0
101 16 384 0 0 0
101 17 52 0 0 0
101 19 460 0 0 0
101 1357 0 1 1
102 11 11 0 0 0
102 12 9 0 0 0
102 15 14 0 0 0
102 16 8 0 0 0
102 19 53 0 0 0
102 95 0 1 1
103 12 4 0 0 0
103 15 52 0 0 0
103 16 120 0 0 0
103 19 49 0 0 0
103 225 0 1 1
104 11 204 0 0 0
104 15 275 0 0 0
104 16 261 0 0 0
104 19 718 0 0 0
104 1458 0 1 1
105 12 25 0 0 0
105 14 34 0 0 0
105 15 87 0 0 0
105 17 47 0 0 0
105 19 242 0 0 0
105 435 0 1 1
11 283 1 0 2
12 233 1 0 2
13 5 1 0 2
14 68 1 0 2
15 587 1 0 2
16 773 1 0 2
17 99 1 0 2
19 1522 1 0 2
3570 1 1 3
40 rows selected.
Wir sehen also recht deutlich, wie die Funktionen arbeiten. Mit diesem Wissen können wir nun auch die Ergebnismenge filtern um nur die Zwischenergebnisse zu erhalten.
SQL> select o.CUSTOMER_ID, p.CATEGORY_ID, sum(QUANTITY) QUANTITY#,
2 grouping(o.CUSTOMER_ID) cid,
3 grouping(p.CATEGORY_ID) pid,
4 grouping_id(o.CUSTOMER_ID, p.CATEGORY_ID) grp
5 from ORDERS o
6 join ORDER_ITEMS oi on (o.ORDER_ID = oi.ORDER_ID)
7 join PRODUCT_INFORMATION p on (p.PRODUCT_ID = oi.PRODUCT_ID)
8 where o.CUSTOMER_ID < 106
9 and p.CATEGORY_ID < 20
10 group by cube(o.CUSTOMER_ID, p.CATEGORY_ID)
11 having grouping(o.CUSTOMER_ID)=1 or grouping(p.CATEGORY_ID)=1
12 order by o.CUSTOMER_ID, p.CATEGORY_ID;
CUSTOMER_ID CATEGORY_ID QUANTITY# CID PID GRP
----------- ----------- ---------- ---------- ---------- ----------
101 1357 0 1 1
102 95 0 1 1
103 225 0 1 1
104 1458 0 1 1
105 435 0 1 1
11 283 1 0 2
12 233 1 0 2
13 5 1 0 2
14 68 1 0 2
15 587 1 0 2
16 773 1 0 2
17 99 1 0 2
19 1522 1 0 2
3570 1 1 3
14 rows selected.Fazit
Die Erweiterungen ROLLUP und CUBE für das GROUP BY zusammen mit den Funktionen bieten durchaus mächtige Möglichkeiten, Daten zu analysieren. Gerade im Data Warehouse Umfeld kann das durchaus interessant sein.
Kommentare
Keine Kommentare