GROUP BY-Klausel: Wie gut kennen Sie sie?

Die Datenbank kann verschiedene Arten von Berechnungen durchführen: Sie kann addieren und subtrahieren, multiplizieren und dividieren, Datumsberechnungen durchführen, Zeilen zählen und Zeilenwerte summieren usw. Sie kann auch recht anspruchsvolle Statistiken durchführen. Die GROUP BY-Klausel ist die grundlegendste Methode zur Berechnung von Statistiken in SQL. Für Anfänger kann sie ziemlich schwierig sein, aber sie ist sehr leistungsfähig. Schauen wir uns die Details der GROUP BY-Klausel an und beginnen mit den Grundlagen.

Die Grundlagen: Wie GROUP BY funktioniert

Hier haben wir eine Tabelle Medaillen mit den Medaillengewinnern im Skisprung-Weltcup der letzten vier Saisons.

 country |         person        | season  | place
---------+-----------------------+---------+-------
Norway   | Anders Bardal         | 2011-12 |   1
Austria  | Gregor Schlierenzauer | 2011-12 |   2
Austria  | Andreas Kofler        | 2011-12 |   3
Austria  | Gregor Schlierenzauer | 2012-13 |   1
Norway   | Anders Bardal         | 2012-13 |   2
Poland   | Kamil Stoch           | 2012-13 |   3
Poland   | Kamil Stoch           | 2013-14 |   1
Slovenia | Peter Prevc           | 2013-14 |   2
Germany  | Severin Freund        | 2013-14 |   3
Germany  | Severin Freund        | 2014-15 |   1
Slovenia | Peter Prevc           | 2014-15 |   2
Austria  | Stefan Kraft          | 2014-15 |   3

Ich möchte herausfinden, wie viele Medaillen Polen gewonnen hat:

SELECT count(*)
FROM medals
WHERE country = 'Poland';

Wenn ich die Anzahl der Medaillen für Deutschland herausfinden wollte, müsste ich diese Abfrage stellen:

SELECT count(*)
FROM medals
WHERE country = 'Germany';

Wenn ich die Anzahl der Medaillen für jedes Land herausfinden möchte, könnte ich sechs ähnliche Abfragen stellen. Oder ich könnte eine GROUP BY verwenden.

SELECT country, count(*)
FROM medals
GROUP BY country;

Die GROUP BY-Klausel steht in einer SQL-Abfrage direkt nach der WHERE-Klausel. Hier fehlt die WHERE-Klausel, also steht sie direkt nach FROM.

Das Ergebnis:

 country | count
---------+-------
Poland   |   2
Germany  |   2
Austria  |   4
Norway   |   2
Slovenia |   2

Mit der GROUP BY-Abfrage teilt die Datenbank die Daten in Gruppen ein. Zeilen mit der gleichen GROUP BY-Spalte (in unserem Beispiel das Land) werden in einer Gruppe zusammengefasst. In unserem Beispiel werden also die Medaillengewinner aus Polen in eine Gruppe eingeordnet, die Medaillengewinner aus Deutschland in eine andere Gruppe und so weiter. Hier ist die Gruppierung, die wir für diese Abfrage erhalten:

 country |        person         | season  | place
–--------+-----------------------+---------+-------
Poland   | Kamil Stoch           | 2012-13 |   3
         | Kamil Stoch           | 2013-14 |   1
–--------+-----------------------+---------+-------
Germany  | Severin Freund        | 2013-14 |   3
         | Severin Freund        | 2014-15 |   1
–--------+-----------------------+---------+-------
Austria  | Gregor Schlierenzauer | 2012-13 |   1
         | Stefan Kraft          | 2014-15 |   3
         | Gregor Schlierenzauer | 2011-12 |   2
         | Andreas Kofler        | 2011-12 |   3
–--------+-----------------------+---------+-------
Norway   | Anders Bardal         | 2012-13 |   2
         | Anders Bardal         | 2011-12 |   1
–--------+-----------------------+---------+-------
Slovenia | Peter Prevc           | 2013-14 |   2
         | Peter Prevc           | 2014-15 |   2
–--------+-----------------------+---------+-------

Mit GROUP BY werden die Aggregate (count, sum, avg, min, max und andere) für jede Gruppe einzeln berechnet. In diesem Beispiel zählt die Datenbank die Anzahl der Zeilen in jeder Gruppe.

Gruppierung mit mehreren Spalten

Sie können Zeilen nach mehr als einer Spalte gruppieren.

Wenn Sie zum Beispiel herausfinden möchten, wie viele Medaillen jedes Land in jeder Saison erhalten hat, würde Ihre Abfrage wie folgt aussehen:

SELECT country, season, count(*)
FROM medals
GROUP BY country, season;

Zeilen mit dem gleichen Land und der gleichen Saison werden in einer Gruppe zusammengefasst. Die Gruppierung sieht so aus:

 country | season  |        person         | place
–--------+---------+-----------------------+-------
Poland   | 2012-13 | Kamil Stoch           |   3
–--------+---------+-----------------------+-------
Poland   | 2013-14 | Kamil Stoch           |   1
–--------+---------+-----------------------+-------
...
–--------+---------+-----------------------+-------
Austria  | 2011-12 | Gregor Schlierenzauer |   2
         | 2011-12 | Andreas Kofler        |   3
–--------+---------+-----------------------+------

Das Endergebnis:

 country | season  | count
---------+---------+-------
 Poland  | 2012-13 |   1
 Austria | 2011-12 |   2
 ...
 Poland  | 2013-14 |   1

NULL-Werte in GROUP BY

Zur Erinnerung: In der WHERE-Bedingung werden keine zwei NULL-Werte als gleichwertig betrachtet. So seltsam das auch erscheinen mag, die Abfrage

SELECT *
FROM medals
WHERE place = place;

wählt alle Zeilen aus , außer denen mit NULL-Platz. Für SQL bedeutet der NULL-Wert "Unbekannt", und wenn er unbekannt ist, kann SQL nicht davon ausgehen, dass es mit Sicherheit weiß, was das Ergebnis sein wird. (Insbesondere kann es nicht mit Sicherheit wissen, dass das Ergebnis WAHR ist.)

Bei GROUP BY ist das anders. Zeilen mit NULL-Werten gehen alle in eine Gruppe, und die Aggregate werden für diese Gruppe wie für jede andere berechnet. Es funktioniert auch für mehrspaltige GROUP BYs.

Für diese Tabelle:

    country    |   city  |    person    | earnings
---------------+---------+--------------+----------
 NULL          | Warsaw  | John Doe     |   1000
 United States | NULL    | Maria Jane   |   1000
 Germany       | Berlin  | Hans Schmitt |   2430
 United States | NULL    | Bill Noir    |   1000
 United States | Chicago | Rob Smith    |   3000
 NULL          | Warsaw  | Sophie Doe   |   2000
 Germany       | Berlin  | Jane Dahl    |   1500

Die Abfrage

SELECT country, city, sum(earnings)
FROM employees
GROUP BY country, city;

rendert diese Gruppen:

    country    |   city  |    person    | earnings
---------------+---------+--------------+----------
 NULL          | Warsaw  | John Doe     |   1000
               |         | Sophie Doe   |   2000
–--------------------------------------------------
 United States | NULL    | Maria Jane   |   1000
               |         | Bill Noir    |   1000
–--------------------------------------------------
 United States | Chicago | Rob Smith    |   3000
–--------------------------------------------------
 Germany       | Berlin  | Hans Schmitt |   2430
               |         | Jane Dahl    |   1500

und dieses Ergebnis

    country    |   city  |  sum
---------------+---------+-------
 NULL          | Warsaw  |  3000
 United States | NULL    |  2000
 United States | Chicago |  3000
 Germany       | Berlin  |  3930

Mythos: Ausgewählte Spalten müssen in der GROUP BY-Klausel oder unter einer Aggregatfunktion erscheinen?

Der Volksmund sagt, dass ausgewählte Spalten in einer GROUP BY-Abfrage entweder in der GROUP BY-Klausel oder unter einer Aggregatfunktion erscheinen müssen. Diese Abfrage ist also falsch:

SELECT user_account.id, email, count(*)
FROM user_account JOIN address
ON user_account.id = address.user_id
GROUP BY email;

Die Abfrage gruppiert die Ergebnisse nach E-Mail, aber sie wählt die id-Spalte aus, die nicht in der GROUP BY-Klausel enthalten ist.

Diese Weisheit war die Regel im SQL92-Standard. So implementieren viele Datenbanken heute das GROUP BY-Verhalten. Sie erhalten eine ähnliche Fehlermeldung wie diese:

ERROR:  column "user_account.id" must appear in the GROUP BY clause or be used 
in an aggregate function

Woher kommt diese Regel? Schauen wir uns ein Datenbeispiel an.

|       user_account      |   |         address         |
+----+--------------------+   +----+----------+---------+
| id |        Email       |   | id |   city   | user_id |
+----+--------------------+   +----+----------+---------+
| 1  | john@example.com   |   | 1  | London   | 1       |
| 2  | mary@example.co.uk |   | 2  | Brussels | 2       |
| 3  | john@example.com   |   | 3  | Cairo    | 3       |
|    |                    |   | 4  | Dublin   | 1       |

Wir gruppieren die Daten nach E-Mail

 
user_account.email |user_account.id |address.id|address.city|address.user_id|
-------------------+----------------+----------+------------+---------------+
john@example.com   | 1              | 1        | A          | 1             |
                   +----------------+----------+------------+---------------+
                   | 1              | 4        | D          | 1             |
                   +----------------+----------+------------+---------------+
                   | 3              | 3        | C          | 3             |
-------------------+----------------+----------+------------+---------------+
mary@example.com   | 2              | 2        | B          | 2             |

Die Datenbank erstellt für jede E-Mail eine Gruppe. Aber es gibt mehrere user_account IDs in jeder Gruppe. Die Datenbank weiß nicht, welche ID sie zurückgeben soll. Der SQL-Standard will, dass das SQL-Ergebnis deterministisch ist, und verbietet daher die Ausführung einer Abfrage wie dieser.

Der SQL99-Standard hat den Wortlaut der Regel geändert. Er besagt nun, dass jede Spalte, die unter SELECT erscheint, unter der Aggregatfunktion erscheinen oder funktional von Spalten in der GROUP BY-Klausel abhängig sein muss. Die Regel erwartet nicht mehr, dass alle nicht aggregierten Spalten in der GROUP BY-Klausel wiederholt werden.

Was bedeutet funktional abhängige Spalten in der BY by-Klausel? Es bedeutet: Wenn ich Werte für Spalten in der GROUP BY-Klausel festlege, darf es nur einen Wert für die andere Spalte geben. Zum Beispiel bestimmt die E-Mail-Adresse den Wert des Namens ihres Besitzers. Aber die Sache hat einen Haken: Die Datenbank muss von dieser Abhängigkeit wissen. Im Kontext von Datenbanken bedeutet die Abhängigkeit entweder Primärschlüssel oder eindeutige Schlüssel. Wenn ich nach einem Primärschlüssel gruppiere, dann weiß ich, dass andere Spalten in dieser Tabelle feste Werte haben.

Unser ursprüngliches Beispiel ist auch nach der neuen Regel nicht gültig. ABER: Wenn ich die eindeutige Einschränkung für die E-Mail-Spalte erzwinge, wird die Abfrage unter der neuen Regel gültig. Wenn die E-Mail-Spalte in der Tabelle user_account eindeutig ist, bestimmt die Festlegung des E-Mail-Werts alle anderen Spalten in der Tabelle user_account. Wenn ich die eindeutige Einschränkung hinzufüge, sind meine Beispieldaten natürlich ebenfalls ungültig. Ich kann nicht zwei verschiedene Zeilen mit der gleichen E-Mail haben.

Unterstützen Datenbanken die neue GROUP BY-Regel?

Manche schon, manche nicht. Die neue Regel ist im SQL99-Standard enthalten. MySQL ab Version 5.7.4 unterstützt das neue Verhalten. Das gilt auch für Postgres ab Version 9.1. Postgres behandelt die Spalte als funktional abhängig von den gruppierten Spalten, wenn eine Teilmenge der gruppierten Spalten ein Primärschlüssel der Tabelle ist, aus der die Spalte stammt.

Soweit ich weiß, halten Oracle und SQL Server noch an der alten Version fest.

Sollten Sie in Ihren Abfragen die neue oder die alte Version der Regel verwenden? In der Praxis ändert diese Änderung nicht wirklich etwas. Beide Regeln garantieren, dass bei der Auswahl einer nicht aggregierten Spalte in einer GROUP BY-Abfrage ihr Wert in jeder Gruppe eindeutig ist. Die alte Regel zwingt Sie dazu, diese Spalte in der GROUP BY-Klausel hinzuzufügen, aber diese GROUP BY ändert die Semantik der Abfrage nicht. Das Ergebnis ist dasselbe, Sie müssen mit der neuen Regel nur ein wenig mehr eingeben.

Im Allgemeinen sind Sie besser dran, wenn Sie sich an die alte Regel halten. Ihre Abfragen werden in den meisten Datenbanken funktionieren. Aber es ist gut zu wissen, dass Sie das nicht müssen.

Erweiterungen zu GROUP BY

Mit SQL-99 wurden ROLLUP, CUBE und GROUPING SETS als Optionen für SELECT-Anweisungen hinzugefügt.

GRUPPE NACH ROLLUP

Die Syntax für ROLLUP lautet

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY ROLLUP (<group-by columns>);

Die Verwendung von ROLLUP (a,b,c) erzeugt GROUP BY-Klauseln: (a, b, c), (a, b), (a) und eine Zeile für eine Aggregation aller ausgewählten Zeilen. Dies entspricht vier SELECT-Abfragen mit verschiedenen GROUP BY-Klauseln.

Für diese Tabelle

department | year | sales
–----------+--------------
 IT        | 2012 |  25000
 IT        | 2013 |  26000
 Retail    | 2012 |  35000
 Retail    | 2013 |  15000
 IT        | 2014 |  18000

und diese Abfrage

SELECT department, year, sum(sales)
FROM sales
GROUP BY ROLLUP (department, year);

erhalten wir das Ergebnis:

 department | year |   sum   
------------+------+--------
 IT         | 2012 |   25000
 IT         | 2014 |   18000
 IT         | 2013 |   26000
 IT         | NULL |   69000 <-  group by department
 Retail     | 2013 |   15000
 Retail     | 2012 |   35000
 Retail     | NULL |   50000 <-  group by department
 NULL       | NULL |  119000 <-  group by (), i.e. all rows selected

Die zusätzlichen Zeilen werden manchmal Superaggregate genannt.

ROLLUP wird von SQL Server, Oracle und DB2 unterstützt.

In MySQL können Sie die WITH ROLLUP-Syntax verwenden:

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY a,b,c WITH ROLLUP;

PostgreSQL unterstützt ROLLUP nicht.

GROUP BY CUBE

Die Syntax für CUBE lautet

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY CUBE (a, b, c);

Sie funktioniert ähnlich wie ROLLUP, erzeugt aber alle möglichen Kombinationen von Spalten: (a,b,c), (a,b), (a,c), (b,c), (a), (b), (c) und eine Zeile für alle ausgewählten Zeilen.

Die Abfrage

SELECT department, year, sum(sales)
FROM sales
GROUP BY CUBE (department, year);

wird dieses Ergebnis wiedergeben:

 department | year |   sum   
------------+------+--------
 IT         | 2012 |   25000
 IT         | 2014 |   18000
 IT         | 2013 |   26000
 IT         | NULL |   69000  <-  group by department
 Retail     | 2013 |   15000
 Retail     | 2012 |   35000
 Retail     | NULL |   50000  <-  group by department
 NULL       | NULL |  119000  <-  group by ()
 NULL       | 2014 |   18000 |
 NULL       | 2012 |   60000 | <= three new rows added by CUBE
 NULL       | 2013 |   41000 |    

CUBE wird von SQL Server, Oracle und DB2 unterstützt. MySQL und Postgres unterstützen es nicht.

GRUPPIEREN DURCH GRUPPIEREN VON SETS

GROUPING SETS funktioniert ähnlich, erlaubt aber die Angabe, welche Spaltenkombinationen im Ergebnis verwendet werden müssen. Gruppierungssätze müssen mit Kommas getrennt werden. Gibt es mehr als eine Spalte in einer Gruppierungsmenge, sollte diese Gruppierungsmenge in Klammern gesetzt werden. Leere Klammern bedeuten den allgemeinen Datensatz mit Aggregaten für den gesamten Satz.

Beispielabfrage:

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY GROUPING SETS ((a, b), c, ());

GROUPING SETS wird von SQL Server, Oracle und DB2 unterstützt. MySQL und Postgres unterstützen es nicht.