Wer viel mit Auswertungen zu tun hat und GROUP BY sowie ROLLUP und CUBE bis hin zur Verzweiflung benutzen muss, der hat mit der bei SQL Server 2008 eingeführten Klausel GROUPING SETS sicherlich seine Freude.

Wer beispielsweise solche Auswertungen schreiben muss

select
     Anstellungsjahr
    ,Geschlecht
    ,Region
    ,sum(Verkauf) Summe
from dbo.vVerk
group by
     Anstellungsjahr
    ,Geschlecht
    ,Region
union all
select
     NULL
    ,Geschlecht
    ,Region
    ,sum(Verkauf)
from dbo.vVerk
group by
     Geschlecht
    ,Region
union all
select
     Anstellungsjahr
    ,NULL
    ,Region
    ,sum(Verkauf)
from dbo.vVerk
group by
     Anstellungsjahr
    ,Region
union all
select
     NULL
    ,NULL
    ,NULL
    ,sum(Verkauf)
from dbo.vVerk;

wird sich freuen, sein Augenmerk auf die Logik, statt auf mühselige Tipparbeit und fehleranfälliges Copy&Paste legen zu können. Mittels GROUPING SETS wird es deutlich übersichtlicher.

select
     Anstellungsjahr
    ,Geschlecht
    ,Region
    ,sum(Verkauf) Summe
from dbo.vVerk
group by
    grouping sets
    (
         (Anstellungsjahr, Geschlecht, Region)
        ,(Anstellungsjahr, Region)
        ,(Geschlecht, Region)
        ,()
    );

Auffällig an dieser Stelle ist das Paar leere Klammern. Dieses fasst innerhalb eines GROUPING SETS alle Gruppen zu einer Gruppe zusammen („Grand Total“).

In obigem Beispiel entspricht

()

folgendem Statement aus dem ersten Beispiel

select
     NULL
    ,NULL
    ,NULL
    ,sum(Verkauf)
from dbo.vVerk;

Also eine feine Sache. Mittels ROLLUP und CUBE kann man sich noch mehr Tipparbeit sparen.

ROLLUP

select
     Anstellungsjahr
    ,Geschlecht
    ,Region
    ,sum(Verkauf) Summe
from dbo.vVerk
group by
    rollup(Anstellungsjahr, Geschlecht, Region);

entspricht

select
     Anstellungsjahr
    ,Geschlecht
    ,Region
    ,sum(Verkauf) Summe
from dbo.vVerk
group by
    grouping sets
    (
         (Anstellungsjahr, Geschlecht, Region)
        ,(Anstellungsjahr, Geschlecht)
        ,(Anstellungsjahr)
        ,()
    );

CUBE

select
     Anstellungsjahr
    ,Geschlecht
    ,Region
    ,sum(Verkauf) Summe
from dbo.vVerk
group by
    cube(Anstellungsjahr, Geschlecht, Region);

entspricht

select
     Anstellungsjahr
    ,Geschlecht
    ,Region
    ,sum(Verkauf) Summe
from dbo.vVerk
group by
    grouping sets
    (
         (Anstellungsjahr, Geschlecht, Region)
        ,(Geschlecht, Region)
        ,(Region)
        ,(Anstellungsjahr, Region)
        ,(Anstellungsjahr)
        ,(Anstellungsjahr, Geschlecht)
        ,(Geschlecht)
        ,()
    ) ;

Natürlich gilt bei GROUPING SETS auch, dass jede Spalte in der SELECT Liste Argument einer Aggregatfunktion oder in der GROUP BY Klausel enthalten sein muss. Die Anordnung der Spalten innerhalb eines GROUPING SETS oder die Anordnung von mehreren GROUPING SETS (dazu später mehr) ist unerheblich.

Bitte dringend beachten, das die Verwendung von CUBE, ROLLUP und ALL gemäß früherer SQL Server Versionen abgekündigt ist.

Vorteil der GROUPING SETS ist nicht nur die vereinfachte Schreibeweise von komplexen Gruppierungen, sondern auch ein Geschwindigkeitsaspekt. Bei der früheren Schreibweise mit UNION ALL wurde jeweils auf die Tabelle(n) zugegriffen, bei GROUPING SETS kann der Optimizer hierfür auch Table Spools erzeugen, wenn dies kostengünstiger ist.

Besonders interessant ist, dass GROUPING SETS miteinander kombiniert werden können.
Da ROLLUP und CUBE nichts anderes als Abkürzungen für GROUPING SETS sind, kann man diese somit auch kombinieren.
Aber dem Ganzen sind natürlich Grenzen gesetzt: Maximale Anzahl an Gruppierungen ist 4096. Das entspricht beispielsweise GROUP BY CUBE mit 12 Ausdrücken. Maximal 32 eindeutige Ausdrücke in GROUPING SETS sind erlaubt.

Um das Ganze durchschaubarer und übersichtlicher darzustellen, erzeugen wir uns einfach virtuelle Daten (die folgenden Beispiele sind ohne Weiteres in jeder Datenbank lauffähig):

with tstrows as
(
    select 
		[1], [2], [3], [4], [5], [6], [7], [8]
    from
    (
        values
        ('1', '2', '3', '4', '5', '6', '7', '8')
    ) E ([1], [2], [3], [4], [5], [6], [7], [8])
)
select
    [1], [2], [3], [4], [5], [6], [7], [8]
from tstrows;

Als Ergebnis erhalten wir eine Tabelle mit 8 Spalten und einen Satz

Nehmen wir nun 2 GROUPING SETS hinzu

with tstrows as
(
    select 
		[1], [2], [3], [4], [5], [6], [7], [8]
    from
    (
        values
        ('1', '2', '3', '4', '5', '6', '7', '8')
    ) E ([1], [2], [3], [4], [5], [6], [7], [8])
)
select
    [1], [2], [3], [4], [5], [6], [7], [8]
from tstrows
group by
    grouping sets
    (
         ([1], [2])
        ,([3], [4])
    ),
    grouping sets
    (
         ([5], [6])
        ,([7], [8])
    );

Wir sehen hier, dass aus den beiden GROUPING SETS ein Kartesisches Produkt gebildet wurde:

(3,4) und (7,8),
(1,2) und (7,8),
(3,4) und (5,6),
(1,2) und (5,6)

Mit diesem Beispiel kann man auch sehr gut sehen, was () in einem GROUPING SET bewirkt

with tstrows as
(
    select 
		[1], [2], [3], [4], [5], [6], [7], [8]
    from
    (
        values
        ('1', '2', '3', '4', '5', '6', '7', '8')
    ) E ([1], [2], [3], [4], [5], [6], [7], [8])
)
select
    [1], [2], [3], [4], [5], [6], [7], [8]
from tstrows
group by
    grouping sets
    (
         ([1], [2])
        ,([3], [4])
        ,()
    ),
    grouping sets
    (
         ([5], [6])
        ,([7], [8])
    );

folgendes Ergebnis

Die fett markierten Zellen sind das Ergebnis des leeren Klammerpaares.

Also

(3,4) und (7,8),
() und (7,8),
(1,2) und (7,8),
(3,4) und (5,6),
() und (5,6),
(1,2) und (5,6)

Da es sich bei ROLLUP und CUBE wie bereits erwähnt um Abkürzungen von GROUPING SETS handelt, lassen sich diese natürlich auch beliebig mit anderen GROUPING SETS kombinieren:

with tstrows as
(
    select 
		[1], [2], [3], [4], [5], [6], [7], [8]
    from
    (
        values
        ('1', '2', '3', '4', '5', '6', '7', '8')
    ) E ([1], [2], [3], [4], [5], [6], [7], [8])
)
select
    [1], [2], [3], [4], [5], [6], [7], [8]
from tstrows
group by
     rollup([1], [2], [3], [4], [5])
    ,cube([6], [7], [8]);

Das Ergebnis ist etwas größer

Solche Ergebnismengen bieten sich an, in einer Tabelle gespeichert zu werden. Um schnelle Abfragen auf bestimmte Gruppierungen zu ermöglichen, ist ein clustered Index auf einer Spalte, welche eine Gruppierung identifiziert, ideal. Um dieses Merkmal zu erstellen, kann die Funktion GROUPING_ID() verwendet werden.

GROUPING_ID() gibt eine Bitmap zurück, bei der für jedes übergebene Argument per nicht gesetztem Bit (0) angezeigt wird, ob das Argument Teil der Gruppierung ist.

Achtung, GROUPING_ID() ist insofern fehleranfällig, dass nicht alle gruppierten Spalten als Argument übergeben werden müssen und dass diese zudem mehrfach vorkommen dürfen. Copy&Paste kann hier also schnell zu einem spät zu erkennenden Fehler führen.

Am besten übergibt man die Argumente an die GROUPING_ID() Funktion in umgekehrter Reihenfolge der Spaltenordnung, um die zu den Spalten korrespondierenden Bits der Bitmap einfach identifizieren zu können (erste Spalte, niedrigstes Bit).

Hierfür ein Beispiel, bei dem ich zur verständlicheren Darstellung die 2er Potenz des entsprechenden Bits als Spaltenkopf gewählt habe (zum testen TOP Klausel entfernen, damit alle 256 Zeilen dargestellt werden):

with tstrows as
(
    select [1], [2], [4], [8], [16], [32], [64], [128]
    from
    (
        values
        ('1', '2', '3', '4', '5', '6', '7', '8')
    ) E ([1], [2], [4], [8], [16], [32], [64], [128])
)
select top(16)
     grouping_id([128], [64], [32], [16], [8], [4], [2], [1]) grpid
    ,[1], [2], [4], [8], [16], [32], [64], [128]
from tstrows
group by
    cube([1], [2], [4], [8], [16], [32], [64], [128])
order by
    grpid;

Da SQL Server 2008 mehrfach vorkommende Gruppierungen zulässt, kann man dieses Verfahren auch dazu verwenden, mehrfach vorkommende Gruppierungen bei komplexen GROUPING SETS zu identifizieren. Bei 4096 möglichen Gruppierungen kann man sich ja mal vertun…

with tstrows as
(
    select 
		[1], [2], [4], [8], [16], [32], [64], [128], 
		[256], [512], [1024], [2048], [4096]
    from
    (
        values
        ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13')
    ) E ([1], [2], [4], [8], [16], [32], [64], [128], [256], [512], [1024], [2048], [4096])
)
, grps as
(
    select
         grouping_id([4096], [2048], [1024], [512], [256], [128], [64], [32], [16], [8], [4], [2], [1]) grpid
        ,[1], [2], [4], [8], [16], [32], [64], [128], [256], [512], [1024], [2048], [4096]
    from tstrows
    group by
        grouping sets
        (
            ([256], [512], [1024], [2048]),
            ([256], [512], [2048]),
            ([256], [2048], [4096]),
            ()
        ),
        rollup([1], [2], [4], [8], [16], [32]),
        cube([32], [64], [128], [2048])
)
select
    count(*) NbrOfSameGroups
   ,grpid
   ,cast(sign(grpid & 1)    as char(1)) +
    cast(sign(grpid & 2)    as char(1)) +
    cast(sign(grpid & 4)    as char(1)) +
    cast(sign(grpid & 8)    as char(1)) +
    cast(sign(grpid & 16)   as char(1)) +
    cast(sign(grpid & 32)   as char(1)) +
    cast(sign(grpid & 64)   as char(1)) +
    cast(sign(grpid & 128)  as char(1)) +
    cast(sign(grpid & 256)  as char(1)) +
    cast(sign(grpid & 512)  as char(1)) +
    cast(sign(grpid & 1024) as char(1)) +
    cast(sign(grpid & 2048) as char(1)) +
    cast(sign(grpid & 4096) as char(1)) bitvektor
from grps
group by
    grpid
having
    count(*) > 1

Die Möglichkeit, eine HAVING Klausel zu verwenden um bestimmte Gruppierungen mittels GROUPING_ID() herauszufiltern, ist kontraproduktiv: Entweder bildet man die Gruppierungen direkt so, wie sie benötigt werden, oder falls diese in einer Tabelle persistiert wurden, wird über den clustered Index auf dem durch die GROUPING_ID() Funktion erzeugten Wert auf die Gruppierungen zugegriffen.

Ein Weg mit dem man eine solche Tabelle aktuell halten und mit neuen Gruppierungen befüllen kann, ist Beispiel des letzten Artikels.