Game of Hierarchies – wie Sie die GROUP BY-Klauseln meistern

Mit der passenden Antwort auf die weiter oben stehende hoch-valyrische Floskel setzen wir fort mit konkreten Anwendungsfällen zu den besprochenen Aggregatfunktionen. Der Einfachheit halber werden wir die Beispiele nur mit der COUNT-Funktion durchspielen – denn in den Beispielen können wir das Ergebnis dann auch im Kopf nachrechnen. Das Gezeigte funktioniert aber analog mit allen oben angeführten Aggregatfunktionen – und darf gerne als Hausübung ausprobiert werden.

Ein Blick auf die Demo-Daten ist zwischendurch auf jeden Fall lohnenswert. Ich habe mir die Mühe gemacht, für die "Known World" aus den Game of Thrones-Büchern pro Region und Kontinent Sehenswürdigkeiten (= points-of-interest, kurz: POI) zu erfassen. Zugegebenermaßen hatte ich mit Städten gestartet, musste aber rasch einsehen, dass in einem mittelalterlich angehauchten Epos nicht allzuviele Städte existieren und habe dann situationselastisch auf Sehenswürdigkeiten umgeschwenkt. Ein kurzes SELECT zeigt uns, was ich als "sehenswert" eingeschätzt habe:

SELECT
    POIName,
    RegionName,
    ContinentName,
    WorldName
FROM
    dbo.GoT_POI
ORDER BY
    WorldName,
    ContinentName,
    RegionName,
    POIName;

Das – aus Platzgründen – zusammengeschnittene Ergebnis sehen Sie in Abb.1.

Um jetzt alle sehenswürdigen Plätze zu zählen, reicht ein simples

SELECT
    count(*) as CountOfAll
FROM
    dbo.GoT_POI;

Wenig überraschend sehen wir in Abb.2 einen "CountOfAll" von 148. Das ist genau die Anzahl an Sätzen, die uns auch Abb.1 bereits gezeigt hat.

Am leichtesten fällt es mir, konzentriert an einer Thematik dran zu bleiben, wenn ich ein konkretes Ziel vor Augen habe. Daher starten wir gleich mal mit einem (hoffentlich für Sie) herausfordernden Bericht, an dessen Fertigstellung wir uns Schritt-für-Schritt annähern werden.

Abb.3 zeigt uns genau diesen Bericht. Wir verzichten in diesem Bericht auf die Auflistung der einzelnen POIs und beschränken uns auf die Ebenen Regionen, Kontinente und Welt. Die Spalte Name listet uns die Namen der Regionen und Kontinente auf. Im Falle der Kontinente wollen wir nicht nur den Namen des Kontinents, sondern zur deutlicheren Hervorhebung auch den Text "* SUBTOTAL" mit anzeigen. Analog dazu verfahren wir bei der Welt mit "** TOTAL". In der Spalte POICounter zeigen wir die Anzahl der POIs in ebendiesen Regionen und Kontinenten und auch eine Gesamtsumme.

Der naive Ansatz, eine Summe pro Ebene zu bekommen, wäre das folgende Statement:

SELECT
    POIName,
    RegionName,
    ContinentName,
    WorldName,
    count(*) as CountOfSomething
FROM
    dbo.GoT_POI;

Dieses wird aber mit der folgenden Fehlermeldung quittiert:

Msg 8120, Level 16, State 1, Line 42
Column 'dbo.GoT_POI.POIName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Die Fehlermeldung kritisiert an POIName, dass es weder in einer Aggregat-Funktion noch in einer GROUP BY-Klausel vorkommt. In eine Aggregat-Funktion wollen wir den Namen definitiv nicht stecken (obwohl es manchmal hilft, ein MIN oder MAX zu verwenden – die Erläuterung würde aber den Rahmen dieses Artikel sprengen). Also bleibt nur die GROUP BY-Klausel. Und in diese müssen wir auch alle anderen Spalten stecken:

SELECT
    POIName,
    RegionName,
    ContinentName,
    WorldName,
    count(*) as CountOfSomething
FROM
    dbo.GoT_POI
GROUP BY
    POIName,
    RegionName,
    ContinentName,
    WorldName
ORDER BY
    WorldName,
    ContinentName,
    RegionName,
    POIName;

In den meisten Fällen, in denen ich nach Spalten gruppiere, macht es auch Sinn, nach ebendiesen Spalten eine Sortierung anzuwenden. Das ist nicht syntaktisch von SQL erforderlich, aber eine Liste, in der die Regionen wild durcheinander dargestellt werden und nicht nach Kontinenten sortiert wären, ist eher schwierig zu lesen, vor allem wenn die Kontinente Zwischensummen zeigen.

Und ganz ehrlich: In der Liste der häufigsten Fehler, die ich im täglichen Arbeiten mit SQL mache, kommt das Vergessen des GROUP BY (gleich nach dem vergessenen Auswählen der richtigen Datenbank) ganz weit oben. Aus meiner Sicht ist es einfach lästig, die GROUP BY-Klausel angeben zu müssen. Denn wenn ich auf eine Spalte keine Aggregations-Funktion anwende, dann will ich eben nach dieser Spalte gruppieren. Schade, dass es für das ANSI-Institut keine connect-items gibt, wo ich das als gewünschtes Feature deponieren könnte.

Auf die Freude, dass nun keine Fehlermeldung kommt, folgt leider schnell die Ernüchterung, dass das in Abb.4 gezeigte Ergebnis noch weit entfernt von unserem Ziel ist. Der Wert in der Spalte "CountOfSomething" ist in jeder Zeile 1! Wenn wir kurz nachenken, wird auch schnell klar warum: Wir gruppieren ja – auch – nach den POIs. Und pro POI gibt es halt genau einen POI. Dadurch ist das Ergebnis der Aggregation in allen Zeilen 1. Und das liegt nicht daran, dass wir zuerst nach POI gruppieren und dann nach den anderen Spalten. Bei einem GROUP BY wie oben dargestellt, spielt nämlich die Reihenfolge, in der die Spalten angegeben werden, keine Rolle.

Wir müssen also die Spalte POIName los werden, um dem von uns gewünschten Bericht näher zu kommen. Das ist leicht gemacht:

SELECT
    ContinentName,
    count(*) as CountByContinent
FROM
    dbo.GoT_POI
GROUP BY
    ContinentName
ORDER BY
    ContinentName;

Abb.5 zeigt uns einen schlanken, dreizeiligen Bericht mit der Anzahl der POIs pro Kontinent. Jetzt fehlen uns aber die Regionen und die Gesamtsumme. Darum wird es Zeit, dass wir uns die GROUP BY-Klauseln näher ansehen.

ROLLUP

Mit den GROUP BY-Klauseln ist es möglich, die Aggregats-Funktion für verschiedene Kombinationen der angegebenen Spalten berechnen zu lassen. ROLLUP bietet sich an, um Werte hierarchisch aggregieren zu lassen. Aber ein Bild sagt mehr als tausend Worte, daher sehen wir uns das anhand eines Beispiels an:

SELECT
    RegionName,
    ContinentName,
    WorldName,
    count(*) as CountByGroup
FROM
    dbo.GoT_POI
GROUP BY ROLLUP (
    RegionName,
    ContinentName,
    WorldName
    )
ORDER BY
    RegionName,
    ContinentName,
    WorldName;

Nach dem Schlüsselwort ROLLUP müssen wir die angeführten Spalten zwingend in Klammern setzen.

Abb.6 zeigt uns das Ergebnis. Hier fallen schnell die vielen NULL-Werte in den Namens-Spalten auf. Der Grund für diese NULL-Werte ist nicht etwa, dass hier "unbekanntes" (die eigentliche Bedeutung eines NULL-Wertes) dargestellt wird, sondern meint in diesem Fall: der aggregierte Wert gilt für "alle" Regionen, Kontinente oder Welten. Die erste Zeile mit CountByGroup=148 zeigt eine Summe über alle Sätze – daher sind alle drei Namens-Spalten auf "alle" (NULL) gesetzt.

ROLLUP ist allerdings etwas "überfleißig" und zeigt uns die Region Asshai gleich dreimal mit dem – an sich richtigen – Wert von 4 POIs an. Wir wissen, dass Asshai Bestandteil des Kontinents Essos und der Known World ist und es daher keinen Unterschied macht, ob die Summe für Asshai für "alle" (NULL) Kontinente oder nur Essos berechnet wird. ROLLUP weiß das leider nicht und zeigt daher "Zwischensummen" pro Kontinent und Welt als Fleißaufgabe.

Im Gegensatz zu einem "normalen" GROUP BY spielt bei Einsatz von ROLLUP die Reihenfolge der Spalten schon eine Rolle. Ein Vertauschen der Spalten bringt uns hier auch tatsächlich eine deutliche Verbesserung (die Reihenfolge der Spalten in ORDER BY können wir unverändert lassen):

SELECT
    RegionName,
    ContinentName,
    WorldName,
    count(*) as CountByGroup
FROM
    dbo.GoT_POI
GROUP BY ROLLUP (
    WorldName,
    ContinentName,
    RegionName
    )
ORDER BY
    RegionName,
    ContinentName,
    WorldName;

In Abb.7 können Sie erkennen, dass wir nun die Mehrfach-Sätze pro Region vermeiden konnten. Wir bekommen auch genau eine Zeile pro Kontinent und Welt. Und zusätzlich noch einen Satz als Gesamtsumme – der identisch mit der Summe für Known World ist, weil wir nur eine bekannte Welt kennen (klar: über uns unbekannte Welten können wir auch nichts in eine Datenbank aufnehmen).

CUBE

Die GROUP BY-Klausel CUBE hat einen sehr verlockenden Namen. Warum mit SQL Server Analysis Services eine multidimensionale oder tabulare Datenbank (weitläufig manchmal als Cube bezeichnet) aufbauen, wenn wir doch so einfach unsere relationalen Daten in einen Cube verwandeln können? Und das sieht so aus:

SELECT
    RegionName,
    ContinentName,
    WorldName,
    count(*) as CountByGroup
FROM
    dbo.GoT_POI
GROUP BY CUBE (
    RegionName,
    ContinentName,
    WorldName
    )
ORDER BY
    RegionName,
    ContinentName,
    WorldName;

Auch bei der CUBE-Klausel gilt: Die Spaltennamen müssen in runden Klammern gesetzt werden. Das Ergebnis (s.Abb.8) wirft uns aber mehr als einen Schritt zurück. Nun haben wir glatt vier Zeilen pro Region (z. B. Asshai). Und das ergibt sich daraus, dass die CUBE-Klausel eine Gruppenzeile für jede erdenkliche Kombination der drei angegebenen Spalten erstellt. Im Falle einer Region also: 1. Region für sich, 2. Region in Kombination mit Welt, 3. Region in Kombination mit Kontinent und 4. Region in Kombination mit Kontinent und Welt. Im Falle aller drei angegebenen Spalten ergeben sich acht Gruppenzeilen:

• Region, Continent, World
• Region, Continent
• Region, World
• Continent, World
• Region
• Continent
• World
• ()

Die letzte Kombination "()" ist eine Gesamtsumme.

Die Reihenfolge in der Sie die Spalten in der CUBE-Klausel anführen, spielt keine Rolle. Eine Liste aller Kombinationsmöglichkeiten bleibt eine Liste aller Kombinationsmöglichkeiten – gleichgültig, bei welcher Spalte Sie mit der ersten Kombination beginnen.

Zum leichteren Verständnis habe ich die Zeilen tatsächlich nur auskommentiert, aber nicht gelöscht. Wie uns Abb.9 zeigt, haben wir nun endlich alle Zeilen beisammen, die wir für unseren Bericht benötigen: Eine Zeile pro Region, eine Zeile pro Kontinent und eine Zeile für die Welt als Gesamtsumme. Alle Zeilen zeigen die richtige Anzahl an POIs an.

NULL: UNKNOWN oder ALL?

In unserem Beispiel ist keine der Namens-Spalten (POI, Region, Kontinent oder Welt) NULL, daher kann NULL beim Query Output hier nur "alle" heißen (s. oben). Für einen anderen Anwendungsfall können wir das aber möglicherweise nicht ausschließen und dann könnte es beim Lesen der Ausgabe zu Mißverständnissen kommen, ob nun der eine Satz gemeint ist, der NULL als Namen hat, oder ob es sich um eine aggregierte Zeile handelt. Daher habe ich das folgenden Query so erweitert, dass wir nicht NULL, sondern stattdessen <UNKNOWN> oder <ALL> anzeigen.

Das heißt, wenn die Abfrage für RegionName NULL liefert und wir uns gerade in einer Zeile befinden, die als Gruppierungszeile für RegionName entstanden ist, dann zeigen wir <ALL> an. Ansonsten befinden wir uns in einer "normalen" Detailzeile und zeigen daher <UNKNOWN> an.

SELECT
    ISNULL(RegionName,    CASE WHEN GROUPING(RegionName)    = 1 THEN '<ALL>' ELSE '<UNKNOWN>' END) as RegionName,
    ISNULL(ContinentName, CASE WHEN GROUPING(ContinentName) = 1 THEN '<ALL>' ELSE '<UNKNOWN>' END) as ContinentName,
    ISNULL(WorldName,     CASE WHEN GROUPING(WorldName)     = 1 THEN '<ALL>' ELSE '<UNKNOWN>' END) as WorldName,
    count(*) as CountByGroup
FROM
    dbo.GoT_POI
GROUP BY GROUPING SETS (
    (WorldName),
    (WorldName, ContinentName),
    (WorldName, ContinentName, RegionName)
    )
ORDER BY
    RegionName,
    ContinentName,
    WorldName;

Abb.10 zeigt nun in den Summenzeilen ein <ALL> anstatt dem NULL-Wert. Die Antwort, ob die aktuelle Zeile nach einer bestimmten Zeile gruppiert ist oder nicht, liefert uns dabei die Funktion GROUPING(), die ich kurz am Anfang dieses Artikel erwähnt habe. Diese Funktion eignet sich, um für eine einzelne Spalte abzufragen, ob für diese aggregierte Werte angezeigt werden. Wenn Sie Kombinationen von Spalten abfragen möchten, können Sie auch GROUPING_ID() verwenden. Diese Funktion liefert einen Integer zurück, der den Status für alle als Parameter mitgegebenen Spalten anzeigt. Dabei werden die GROUPING()-Stati für jede einzelne Spalte quasi als Zeichenkette zusammengestückelt und diese Zeichenkette (aus 1er und 0er) dann als binäre Zahl interpretiert.

Im aktuellen Ergebnis stört vielleicht weniger, dass die Summenzeilen am Anfang kommen, als dass die Regionen alphabetisch sortiert sind und daher die Kontinente wild durcheinandergewürfelt sind. Das folgende Query zeigt, wie wir die Sortierung so ändern können, dass a) die Regionen innerhalb eines Kontinentes alphabetisch sortiert und b) die Summenzeilen pro Kontinent und der Welt jeweils am Ende eines Blockes angezeigt wird. Ersteres (Regionen nicht unabhängig, sondern innerhalb der Blöcke sortieren) ist einfach erreicht, indem wir die Reihenfolge der Spalten in der ORDER BY-Klausel umdrehen (s. Abb.11):

SELECT
    ISNULL(RegionName,    CASE WHEN GROUPING(RegionName)    = 1 THEN '<ALL>' ELSE '<UNKNOWN>' END) as RegionName,
    ISNULL(ContinentName, CASE WHEN GROUPING(ContinentName) = 1 THEN '<ALL>' ELSE '<UNKNOWN>' END) as ContinentName,
    ISNULL(WorldName,     CASE WHEN GROUPING(WorldName)     = 1 THEN '<ALL>' ELSE '<UNKNOWN>' END) as WorldName,
    count(*) as CountByGroup
FROM
    dbo.GoT_POI
GROUP BY GROUPING SETS (
    (WorldName),
    (WorldName, ContinentName),
    (WorldName, ContinentName, RegionName)
    )
ORDER BY
    WorldName,
    ContinentName,
    RegionName;

Für zweiteres passen wir die ORDER BY-Klausel noch weiter an, was auf den ersten Blick etwas komplizierter aussieht, als es im Endeffekt ist:

SELECT
    ISNULL(RegionName,    CASE WHEN GROUPING(RegionName)    = 1 THEN '<ALL>' ELSE '<UNKNOWN>' END) as RegionName,
    ISNULL(ContinentName, CASE WHEN GROUPING(ContinentName) = 1 THEN '<ALL>' ELSE '<UNKNOWN>' END) as ContinentName,
    ISNULL(WorldName,     CASE WHEN GROUPING(WorldName)     = 1 THEN '<ALL>' ELSE '<UNKNOWN>' END) as WorldName,
    count(*) as CountByGroup
FROM
    dbo.GoT_POI
GROUP BY GROUPING SETS (
    (WorldName),
    (WorldName, ContinentName),
    (WorldName, ContinentName, RegionName)
    )
ORDER BY
    ISNULL(WorldName,     CASE WHEN GROUPING(WorldName)     = 0 THEN NULL ELSE MAX(WorldName)    +'a' END),
    ISNULL(ContinentName, CASE WHEN GROUPING(ContinentName) = 0 THEN NULL ELSE MAX(ContinentName)+'a' END),
    ISNULL(RegionName,    CASE WHEN GROUPING(RegionName)    = 0 THEN NULL ELSE MAX(RegionName)   +'a' END);

Wie Sie in Abb.12 sehen, sind nun die Zwischensummen immer am Ende jedes Blockes. Und das funktioniert so: Mit ISNULL prüfe ich, ob z. B. RegionName den Wert NULL hat. Wenn nicht, dann wird der Name der Region für die Sortierung herangezogen. Wenn doch, prüfe ich mit CASE WHEN GROUPING(), ob der Grund dafür ist, dass es sich um einen "echten" NULL-Wert (im Sinne von UNKNOWN) handelt. Nach diesem NULL-Wert darf ganz normal sortiert werden. In dem Falle, dass es sich um eine Zwischensumme handelt (GROUPING liefert den Rückgabewert 1), sortiere ich nicht nach dem Region-Namen, sondern nach dem "größten" RegionNamen in dieser Gruppe (mit Hilfe von MAX) und stelle auch noch ein weiteres Zeichen dahinter, damit die Summenzeile sicher nach der letzten Region angezeigt wird. Zu Verständniszwecken können Sie sich die Konstrukte aus der ORDER BY-Klausel gerne in der Projektion der SELECT-Klausel anzeigen lassen.

Voilá

Der allerletzte Schritt ist dann sehr einfach: Je nachdem, in welcher Zwischensumme wir uns befinden, zeige ich in der Spalte mit Alias "Name" die Region, den Kontinent oder die Welt an. Bei den letzteren beiden kommen noch die Markierungen * SUBTOTAL oder ** TOTAL dazu. Der ELSE-Zweig mit UNKNOWN kann im aktuell verwendeten Datenmodell und GROUP BY nie zum Zug kommen und ihn habe ich nur sicherheitshalber dazugegeben, falls sich eines der zwei Dinge irgendwann mal ändern sollte.

SELECT
    CASE
        WHEN GROUPING(RegionName)        = 0 THEN RegionName
        WHEN GROUPING(ContinentName)    = 0 THEN '* SUBTOTAL (' + ContinentName + ')'
        WHEN GROUPING(WorldName)        = 0 THEN '** TOTAL (' + WorldName + ')'
        ELSE                                     '<UNKNOWN>'
        END as Name,
    count(*) as POICounter
FROM
    dbo.GoT_POI
GROUP BY GROUPING SETS (
    (WorldName),
    (WorldName, ContinentName),
    (WorldName, ContinentName, RegionName)
    )
ORDER BY
    ISNULL(WorldName,     CASE WHEN GROUPING(WorldName)     = 0 THEN NULL ELSE MAX(WorldName)    +'a' END),
    ISNULL(ContinentName, CASE WHEN GROUPING(ContinentName) = 0 THEN NULL ELSE MAX(ContinentName)+'a' END),
    ISNULL(RegionName,    CASE WHEN GROUPING(RegionName)    = 0 THEN NULL ELSE MAX(RegionName)   +'a' END);

Wenn wir alles richtig gemacht haben, liefert diese Abfrage das bereits weiter oben in Abb.3 beschriebene Ergebnis.