GROUP BY aufgerollt

Einführung

Der heutige Blogeintrag wird sich mit den Möglichkeiten der Aggregierung in der Oracle Datenbank beschäftigen. Die Beispiele verwenden das Order Entry (OE) Beispielschema von Oracle. Die Bespielschemas stellt Oracle auf GitHub bereit. Wir verwenden hier die Version 19.2 der Schemas.

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. 

Verwandte Blogbeiträge:

Kommentare

Keine Kommentare

Kommentar schreiben

* Diese Felder sind erforderlich