group by und grouping sets

Übersicht

In SQL können Gruppierungen über verschiedene Wege erreicht werden:

group by – "klassische" Gruppierung nach Spalten, liefert keine Zwischen-/Endsummen
group by grouping sets (<mehrere Spaltenlisten>) – flexible Gruppierung über beliebige Kombinationen von Spalten, liefert entsprechende Zwischen-/Gesamtsummen
group by rollup (<Spaltenliste>) – Gruppierung über ALLE Spalten der Liste, in der Reihenfolge, wie in der Liste angegeben. Lliefert entsprechende Zwischen-/Gesamtsummen.

Per grouping()- oder nvl-Funktion können Summenzeilen entsprechend benannt werden (siehe unten).

Gruppierung mit "normalem" group by

Abfrage der Test-Tabelle per group by

select nvl(to_char(monat),'- all -') monat,

decode(grouping(monat),1,'- all -',monat) monat2,

nvl(region,'- all -') r,

nvl(kundengruppe,'- all -') K,

grouping(monat) A1,

grouping(region) A2,

grouping(kundengruppe) A3,

sum(umsatz) sum, count(umsatz) ANZ

from test

group by monat, region, kundengruppe

order by 2,3,4;

Liefert die Aggregation der Wertekombinationen, jedoch keine Zwischen-/Endsummen. Der explain plan ist sehr einfach:

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 65 | 2145 | 17 (12)| 00:00:01 |

| 1 | SORT ORDER BY | | 65 | 2145 | 17 (12)| 00:00:01 |

| 2 | HASH GROUP BY | | 65 | 2145 | 17 (12)| 00:00:01 |

| 3 | TABLE ACCESS FULL| TEST | 65 | 2145 | 15 (0)| 00:00:01 |

----------------------------------------------------------------------------

Gruppierung per grouping sets

Abfrage der test-Tabelle mit grouping sets

set linesize 130

column Monat format a7

column Monat2 format a7

select nvl(to_char(monat),'- all -') monat,

decode(grouping(monat),1,'- all -',monat) monat2,

nvl(region,'- all -') r,

nvl(kundengruppe,'- all -') K,

grouping(monat) A1,

grouping(region) A2,

grouping(kundengruppe) A3,

sum(umsatz) sum, count(umsatz) ANZ

from test

group by grouping sets (

(monat, region, kundengruppe),

(monat, region),

(region, kundengruppe),

(monat, kundengruppe),

(monat),

(region),

(kundengruppe),

(1)

)

order by 1,2,3;

Per grouping sets können beliebige Kombinationen in einem Rutsch abgefragt werden. Zwischensummen werden entsprechend der Spaltenkombinationen gebildet. Nachteil: Die Ausführung ist deutlich aufwändiger – siehe explain plan

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 65 | 6370 | 37 (17)| 00:00:01 |

| 1 | TEMP TABLE TRANSFORMATION | | | | | |

| 2 | LOAD AS SELECT | | | | | |

| 3 | TABLE ACCESS FULL | TEST | 65 | 2145 | 15 (0)| 00:00:01 |

| 4 | MULTI-TABLE INSERT | | | | | |

| 7 | SORT GROUP BY ROLLUP | | 1 | 33 | 3 (34)| 00:00:01 |

| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D666F_6E757 | 1 | 33 | 2 (0)| 00:00:01 |

| 9 | LOAD AS SELECT | | | | | |

| 10 | SORT GROUP BY ROLLUP | | 1 | 33 | 3 (34)| 00:00:01 |

| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6670_6E757 | 1 | 33 | 2 (0)| 00:00:01 |

| 12 | MULTI-TABLE INSERT | | | | | |

| 15 | SORT GROUP BY ROLLUP | | 1 | 42 | 3 (34)| 00:00:01 |

| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6670_6E757 | 1 | 42 | 2 (0)| 00:00:01 |

| 17 | LOAD AS SELECT | | | | | |

| 18 | HASH GROUP BY | | 1 | 29 | 3 (34)| 00:00:01 |

| 19 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6672_6E757 | 1 | 29 | 2 (0)| 00:00:01 |

| 20 | LOAD AS SELECT | | | | | |

| 21 | HASH GROUP BY | | 1 | 26 | 3 (34)| 00:00:01 |

| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D666F_6E757 | 1 | 26 | 2 (0)| 00:00:01 |

| 23 | SORT ORDER BY | | 65 | 6370 | 7 (15)| 00:00:01 |

| 24 | VIEW | | 3 | 294 | 6 (0)| 00:00:01 |

| 25 | VIEW | | 3 | 216 | 6 (0)| 00:00:01 |

| 26 | UNION-ALL | | | | | |

| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6670_6E757 | 1 | 72 | 2 (0)| 00:00:01 |

| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6671_6E757 | 1 | 72 | 2 (0)| 00:00:01 |

| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6672_6E757 | 1 | 72 | 2 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------

Gruppierung per group by rollup

Abfrage der test-Tabelle mit rollup:

select nvl(to_char(monat),'- all -') monat,

decode(grouping(monat),1,'- all -',monat) monat2,

nvl(region,'- all -') r,

nvl(kundengruppe,'- all -') K,

grouping(monat) A1,

grouping(region) A2,

grouping(kundengruppe) A3,

sum(umsatz) sum, count(umsatz) ANZ

from test

group by ROLLUP

(monat, region, kundengruppe)

order by 2,3,4;

Liefert eine feste Kombination von Spaltensummen: Monat – eine Summe, Region 3 Summen, Kundengruppe 11 Summenzeilen. Die Abfrage ist wieder deutlich einfacher:

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 65 | 2145 | 17 (12)| 00:00:01 |

| 1 | SORT ORDER BY | | 65 | 2145 | 17 (12)| 00:00:01 |

| 2 | SORT GROUP BY ROLLUP| | 65 | 2145 | 17 (12)| 00:00:01 |

| 3 | TABLE ACCESS FULL | TEST | 65 | 2145 | 15 (0)| 00:00:01 |

------------------------------------------------------------------------------

Summenzeilen mit decode, grouping() und nvl benennen

Über die Funktion grouping(Spalte) kann abgefragt werden, ob die entsprechende Zeile gerade eine Summe liefert. Eigentlich nichts anderes als nvl(<Spalte>). Grundsätzlich sinnvoll um per decode sprechende Reportingeinträge zur generieren. Allerding funktioniert grouping() im Gegensatz zu nvl bei grouping sets leider nicht korrekt:

select nvl(to_char(monat),'- all -') monat,

decode(grouping(monat),1,'- all -',monat) monat2,

nvl(region,'- all -') r,

nvl(kundengruppe,'- all -') K,

grouping(monat) A1,

grouping(region) A2,

grouping(kundengruppe) A3,

sum(umsatz) sum, count(umsatz) ANZ

from test

group by grouping sets (

(monat, region, kundengruppe),

(monat, region),

(region, kundengruppe),

(monat, kundengruppe),

(monat),

(region),

(kundengruppe),

(1)

)

order by 1,2,3;

sollte in Spalte 1 und 2 entweder den Monat oder "- all -" zurück geben. Macht es aber nicht, stattdessen bleibt die Spalte MONAT2 leer:

MONAT MONAT2 R K A1 A2 A3 SUM ANZ

------- ------- ------- ------- ---------- ---------- ---------- ---------- ----------

- all - - all - - all - - all - 1 1 1 6500 65

- all - - all - a 1 1 0 1900 19

- all - - all - b 1 1 0 2500 25

- all - - all - c 1 1 0 2100 21

- all - AF a 1 0 0 300 3

- all - AF c 1 0 0 500 5

- all - AF - all - 1 0 1 1300 13

- all - AF b 1 0 0 500 5

- all - AS b 1 0 0 900 9

- all - AS - all - 1 0 1 1700 17

- all - AS a 1 0 0 400 4

- all - AS c 1 0 0 400 4

- all - EU - all - 1 0 1 1600 16

- all - EU b 1 0 0 400 4

- all - EU a 1 0 0 400 4

- all - EU c 1 0 0 800 8

- all - US - all - 1 0 1 1900 19

- all - US c 1 0 0 400 4

- all - US b 1 0 0 700 7

- all - US a 1 0 0 800 8

200801 200801 - all - a 0 1 0 800 8

200801 200801 - all - b 0 1 0 1300 13

Das Beispiel erstellen

drop table test;

create table test(

monat number(6),

region char(2 char),

kundengruppe char(1 char),

umsatz number(12,2)

);

-- Reg: US, EU, AS, AF

-- KD: A, B, C

-- Monat: 200801, 200802

insert into test values (200801, 'US', 'a', 100);

insert into test values (200801, 'US', 'b', 100);

insert into test values (200801, 'US', 'c', 100);

insert into test values (200801, 'EU', 'a', 100);

insert into test values (200801, 'EU', 'b', 100);

insert into test values (200801, 'EU', 'c', 100);

insert into test values (200801, 'AS', 'a', 100);

insert into test values (200801, 'AS', 'b', 100);

insert into test values (200801, 'AS', 'c', 100);

insert into test values (200801, 'AF', 'a', 100);

insert into test values (200801, 'AF', 'b', 100);

insert into test values (200801, 'AF', 'c', 100);

insert into test values (200802, 'US', 'a', 100);

insert into test values (200802, 'US', 'b', 100);

insert into test values (200802, 'US', 'c', 100);

insert into test values (200802, 'EU', 'a', 100);

insert into test values (200802, 'EU', 'b', 100);

insert into test values (200802, 'EU', 'c', 100);

insert into test values (200802, 'AS', 'a', 100);

insert into test values (200802, 'AS', 'b', 100);

insert into test values (200802, 'AS', 'c', 100);

insert into test values (200802, 'AF', 'a', 100);

insert into test values (200802, 'AF', 'b', 100);

insert into test values (200802, 'AF', 'c', 100);

commit;

Dieses Blog durchsuchen

Frank's Oracle Blog