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:

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 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

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

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

| 5 | DIRECT LOAD INTO | SYS_TEMP_0FD9D6670_6E757 | | | | |

| 6 | DIRECT LOAD INTO | SYS_TEMP_0FD9D6671_6E757 | | | | |

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

| 13 | DIRECT LOAD INTO | SYS_TEMP_0FD9D6672_6E757 | | | | |

| 14 | DIRECT LOAD INTO | SYS_TEMP_0FD9D6671_6E757 | | | | |

| 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:

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 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', 'a', 100);

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

insert into test values (200801, 'US', 'b', 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', 'b', 100);

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

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

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

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

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

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


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

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

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

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

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

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

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

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

insert into test values (200801, 'AS', 'c', 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', 'b', 100);

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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


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

insert into test values (200802, 'EU', 'a', 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, 'EU', 'c', 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', 'b', 100);

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

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

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

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


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

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

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

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

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

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

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

commit;

Kommentare

Kommentar veröffentlichen

Beliebte Posts aus diesem Blog

PGA unter Oracle 11g

-
aus der Doku: Quelle: http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/memory.htm#PFGRF94351 7.5 PGA Memory Management For complex queries (for example, decision support queries), a big portion of the run-time area is dedicated to work areas allocated by memory intensive operators, such as the following: * Sort-based operators, such as ORDER BY, GROUP BY, ROLLUP, and window functions * Hash-join * Bitmap merge * Bitmap create * Write buffers used by bulk load operations Sizing: A) Ideally, the size of a work area is big enough that it can accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. This is known as the optimal size of a work area. B) When the size of the work area is smaller than optimal, the response time increases, because an extra pass is performed over part of the input data. This is known as the one-pass size of the work area. C) Under the one-pass threshold, when the size of a work area is far too small compar
Mehr anzeigen

trunc(sysdate) - nette Spiele mit dem Datum

-
Über die trunc-date Funktion läßt sich ausgehend von einem Datumswert sehr einfach ein abgeleiteter Datumswert ermittelt: bspw. der Jahres-, Quartals-, Monats- oder Wochenanfang. Die 11g Doku erläutert dies an zwei Stellen: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions209.htm#SQLRF06151 bzw. http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions242.htm#sthref2718 So liefert select trunc(to_date('06.02.2011'),'q') from dual; das DATUM des Quartalsersten (im Bsp. den 01.01.2011). Wichtig ist, dass trunc immer ein Datum zurückliefert, keinen String. Einige Beispiele anhand des 20.Feb 2011 (ein Sonntag in der 07 KW in 2011): select trunc(to_date('20.02.2011'),'d') from dual; select trunc(to_date('20.02.2011'),'dd') from dual; select trunc(to_date('20.02.2011'),'ddd') from dual; select trunc(to_date('20.02.2011'),'mm') from dual; select trunc(to_date('20.02.2011&
Mehr anzeigen

Datapump - Verzeichnis erstellen

-
siehe auch Metalink-Doc: 266875.1 HOW TO SPECIFY A DIRECTORY IN ORACLE10G EXPORT/IMPORT DATAPUMP -------------------------------------------------------------- 1. SERVER-BASED OR CLIENT-BASED ---------------------------- The parameter DIRECTORY specifies the location to which Export DataPump or Import DataPump can write the dump file set, the log file, and the SQL file (Import DataPump only). As export DataPump and import DataPump are server-based, rather than client-based, the output files are accessed relative to server-based directory paths. Data Pump requires you to specify directory paths as directory objects. A directory object maps a name to a directory path on the file system. 2. HOW TO CREATE A DIRECTORY ------------------------- To create a directory, you must have the DBA role or you must have been granted the CREATE ANY DIRECTORY privilege. Example (a DBA creates directories on the Windows platform and grants access to user scott): SQL> CONNECT
Mehr anzeigen