Bis hierher haben wir die Daten als originäre Datensätze betrachtet, wie sie in den abgefragten Tabellen stehen. In der Praxis haben wir jedoch oft das Bedürfnis, Datensätze nach verschiedenen Kriterien zu gruppieren, z.B. um die Umsätze eines Jahres zu aggregieren. Das Jahr bildet dann eine Gruppe mit einer Umsatzsumme.
GROUP BY
Zum Gruppieren bietet SQL die Klausel GROUP BY an. Alle dahinter notierten Spalten werden hinsichtlich gleicher Attributwerte zusammengefasst.
Wir hatten uns bereits die DISTINCT-Klausel angesehen, mit der wir Dubletten zu einem Datensatz haben zusammenfassen können. Als Beispiel haben wir dort die verschiedenen Alterseinstufungen der Filme ermittelt, die im Verleih sind. Selbiges können wir auch mithilfe von GROUP BY erreichen.
|
1 2 3 4 5 |
SELECT rating FROM film GROUP BY rating; |
An dem einfachen Beispiel können Sie gleich die Syntax erkennen. Das Ergebnis ist wie folgt:
+------+ |rating| +------+ |NC-17 | |G | |PG-13 | |R | |PG | +------+
Das Resultat ist identisch zu unsere ehedem mit DISTINCT formulierten Abfrage. Datensätze mit gleichen Attributausprägungen werden zusammengefasst. Insofern unterscheiden sich DISTINCT und GROUP BY, zumindest vordergründig, nicht. Dennoch ist GROUP BY ein vollkommen anderer Schnack. – In Bezug auf das obige Beispiel ist GROUP BY ein „armeseliger Ersatz“ für DISTINCT.
Die Krux besteht darin, GROUP BY in Verbindung mit Aggregationen zu verwenden. Bilden beispielsweise die Summe der von einem Kunden geleisteten Zahlen. Alle Kunden, die Zahlungen geleistet haben, haben dies in der Regel mehrfach getan. Wenn wir also nicht an den einzelnen Datensätzen interessiert sind, sondern an den jeweiligen Summen der Zahlungen, so bildet die jeweilige Summe peo Kunde eine Aggregation. Der Name eines Kunden bildet eine Gruppe, hinter der sich die einzelnen Zahlungen „verbergen“, die wir in einem zusammengefassten Wert, der jeweiligen Summe, ausgeben wollen.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT last_name , first_name , sum(amount) AS total FROM payment p JOIN customer c ON p.customer_id = c.customer_id GROUP BY last_name , first_name ORDER BY last_name , first_name; |
sum() ist eine der fünf Standard-Aggregationsfunktionen von SQL. Wenn wir eine solche Funktion zur Zusammenfassung von Datensätzen verwenden, müssen wir zwingend, die anderen Spalten, so sie nicht ebenfalls aggregiert werden, mittels GROUP BY gruppieren. Tun wir dies nicht, so meldet uns SQL einen Fehler.
+---------+----------+------+ |last_name|first_name|total | +---------+----------+------+ |Abney |Rafael |97.79 | |Adam |Nathaniel |133.72| |Adams |Kathleen |92.73 | |Alexander|Diana |105.73| |Allard |Gordon |160.68| |Allen |Shirley |126.69| ... (599 Datensätze insgesamt)
Variieren wir das Statement ein wenig. Konkatenieren wir den Nach- und Vornamen eines jeden Kunden zu jeweils einer Zeichenkette.
|
1 2 3 4 5 6 7 8 9 |
SELECT last_name || ', ' || first_name AS customer_name , sum(amount) AS total FROM payment p JOIN customer c ON p.customer_id = c.customer_id GROUP BY last_name || ', ' || first_name ORDER BY customer_name; |
Beachten Sie die Gruppierung. Hier habe ich den Zeichenkettenausdruck aus der SELECT-Klausel als Gruppierungskriterium aufgenommen. Sie können hier, jedenfalls bei den meisten Datenbankmanagementsystemen, nicht den Aliasbezeichner der Spalte verwenden. Das entspricht dem SQL-Standard. In PostgreSQL ist dies jedoch zulässig, sodass wir auch das Folgende schreiben dürfen.
|
1 2 3 4 5 6 7 8 9 |
SELECT last_name || ', ' || first_name AS customer_name , sum(amount) AS total FROM payment p JOIN customer c ON p.customer_id = c.customer_id GROUP BY customer_name ORDER BY customer_name; |
So der so, in der ORDER BY-Klausel können wir uns definitiv nicht mehr auf die Nach- und Vornamen als einzelne Datenwerte beziehen. Es gibt sie in der zugrundeliegenden Datenmenge nicht mehr.
Unwahrscheinlich, jedoch vielleicht dennoch gewollt, können wir dennoch separat und abweichend von der Konkatenierung erst nach Vor – und dann nach Nachnamen sortieren. In einem solchen Fall, müssen wir (wiederum zwingend) in der GROUP BY-Klausel, nach den einzelnen Spalten gruppieren.
|
1 2 3 4 5 6 7 8 9 |
SELECT last_name || ', ' || first_name AS customer_name , sum(amount) AS total FROM payment p JOIN customer c ON p.customer_id = c.customer_id GROUP BY last_name, first_name ORDER BY first_name, last_name; |
Das mag Sie jetzt im ersten Moment etwas verwirren. Sehen Sie es aber positiv. Sie haben hierdurch flexible Möglichkeiten, Gruppen und Sortierungen zu bestimmen. Wichtig ist lediglich, alle nicht aggregierten Spalten müssen gruppiert werden.
Aggregationsfunktionen
SQL bietet Ihnen die folgenden Aggregationsfunktionen:
- avg()
: Durchschnitt (Mittelwert) - count()
: Anzahl - min()
: Minimum - max()
: Maximum - sum()
: Summe
Probieren wir die Funktionen einfach dadurch aus, für jeden Kunden eine kleine Statistik zu erstellen.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT last_name || ', ' || first_name AS customer_name , count(amount) AS count , sum(amount) AS total , min(amount) AS minimum , max(amount) AS maximum , avg(amount) AS avg FROM payment p JOIN customer c ON p.customer_id = c.customer_id GROUP BY customer_name ORDER BY customer_name; |
+-----------------+-----+------+-------+-------+------------------+ |customer_name |count|total |minimum|maximum|avg | +-----------------+-----+------+-------+-------+------------------+ |Abney, Rafael |21 |97.79 |0.99 |9.99 |4.6566666666666667| |Adam, Nathaniel |28 |133.72|0.99 |9.99 |4.7757142857142857| |Adams, Kathleen |27 |92.73 |0.99 |9.99 |3.4344444444444444| |Alexander, Diana |27 |105.73|0.99 |7.99 |3.9159259259259259| |Allard, Gordon |32 |160.68|0.99 |10.99 |5.02125 | |Allen, Shirley |31 |126.69|0.99 |8.99 |4.0867741935483871| |Alvarez, Charlene|27 |114.73|0.99 |10.99 |4.2492592592592593| ... (599 Datensätze insgesamt)
Zur Übung erstellen wir noch eine weitere Statistik. Wir wollen uns ansehen, wie sich unsere monatlichen Umsätze über die Zeit entwickelt haben.
payment_date ist ein sogenannter TIMESTAMP, ein Datentyp der Datum und Uhrzeit (bis hin zu Sekundenbruchteilen) speichert. Wir benötigen die Informationen zu Jahr und Monat. Das Jahr dürfen wir nicht vergessen. Würden wir nur den Monat beachten, würden wir die Monate verschiedener Jahre zusammenfassen. Wir bemühen die PostgreSQL-Funktion to_char(). Bei anderen Datenbankmanagementsystemen heißt die Funktion in der Regel anders. Wir verwenden die Funktion und übergeben ihr das Zahldatum und einen Formatstring als Argumente. Der Formatstring drückt aus, was wir aus dem Zeitstempel extrahieren wollen.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT to_char(payment_date, 'YYYY-MM') AS month , count(amount) AS count , sum(amount) AS total , min(amount) AS minimum , max(amount) AS maximum , avg(amount) AS avg FROM payment GROUP BY month ORDER BY month; |
+-------+-----+--------+-------+-------+------------------+ |month |count|total |minimum|maximum|avg | +-------+-----+--------+-------+-------+------------------+ |2005-05|1157 |4824.43 |0.99 |11.99 |4.1697752808988764| |2005-06|2312 |9631.88 |0.99 |11.99 |4.166038062283737 | |2005-07|6711 |28373.89|0.99 |11.99 |4.2279675160184771| |2005-08|5687 |24072.13|0.99 |11.99 |4.2328345349041674| |2006-02|182 |514.18 |0 |9.98 |2.8251648351648352| +-------+-----+--------+-------+-------+------------------+
Aggregationen und NULL-Werte
Es gibt, zwischen den Aggregationsfunktionen kleine, jedoch feine, Unterschiede. So verlangt sum() sicherlich ein numerisches Argument, um rechnen zu können. Bei count() ist der Datentyp des Arguments unerheblich. Zählen kann man alles, sogar ganze Zeilen (*).
Allen Aggregationsfunktionen ist gemein, NULL-Werte zu ignorieren. Sie erinnern sich hoffentlich, dass wir bei den Adressen, in der Postleitzahlenspalte NULL-Werte gefunden haben. Wir wollen sie uns nochmals vornehmen, um das diesbezügliche Verhalten von Aggregationsfunktionen besser zu verstehen.
|
1 2 3 4 5 |
SELECT count(*) AS count_rows , count(address_id) AS count_ids , count(postal_code) AS count_postal_codes FROM address; |
Zuerst zählen wir die Zeilen. Dazu verwenden wir den Asterisk. Danach zählen wir die IDs und zuletzt die Postleitzahlen. Irgendwo wird sich ein Eintrag in einer Spalte eines Datensatzes befinden. Daher ist der Asterisk der beste Kandidat, um die Anzahl von Datensätzen zu ermitteln. Weil address_id der Primärschlüssel der Tabelle ist, muss dort ebenso zwingend ein Eintrag vorhanden sein – auch ein guter Kandidat. Wenn jedoch irgendwo, in einer explizit angegebenen Spalte, hier postal_code ein NULL auftritt, wird er nicht gezählt.
+----------+---------+------------------+ |count_rows|count_ids|count_postal_codes| +----------+---------+------------------+ |603 |603 |599 | +----------+---------+------------------+
Dieses Verhalten, NULL zu ignorieren, ist allen Aggregationsfunktionen gemeinsam!
Gruppenauswahl mit HAVING
Datensätze haben wir mittels einer WHERE-Bedingung ausgewählt. In Verbindung mit Gruppierung bestimmt diese auch weiterhin, welche Datensätze einer Tabelle in einer Abfrage Berücksichtigung finden. Um nun aber, nachdem wir Gruppen gebildet haben, nur die gruppierten Datensätze auszuwählen, die uns interessieren, verwenden wir HAVING.
Betrachten wir hierzu nochmals unsere oben erstellte Kundenstatistik. Wir interessieren uns für die Kunden, die weniger als 100 Dollar umgesetzt haben. Für sie wollen wir eine Werbeaktion starten. Wir müssen also in Erfahrung bringen, wer diese Kunden sind.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT last_name || ', ' || first_name AS customer_name , count(amount) AS count , sum(amount) AS total , min(amount) AS minimum , max(amount) AS maximum , avg(amount) AS avg FROM payment p JOIN customer c ON p.customer_id = c.customer_id GROUP BY customer_name HAVING sum(amount) < 100.00 ORDER BY customer_name; |
+------------------+-----+-----+-------+-------+------------------+ |customer_name |count|total|minimum|maximum|avg | +------------------+-----+-----+-------+-------+------------------+ |Abney, Rafael |21 |97.79|0.99 |9.99 |4.6566666666666667| |Adams, Kathleen |27 |92.73|0.99 |9.99 |3.4344444444444444| |Andrew, Jose |25 |96.75|0.99 |7.99 |3.87 | |Andrews, Ida |23 |76.77|0.99 |5.99 |3.3378260869565217| |Aquino, Oscar |20 |99.8 |0.99 |8.99 |4.99 | |Armstrong, Melanie|25 |92.75|0.99 |5.99 |3.71 | ... (insgesamt 204 Datensätze)
Spätestens in der HAVING-Klausel dürfen wir nicht mehr mit Aliasbezeichnern arbeiten. – Es gibt jedoch Datenbankmanagementsysteme, die dies, entgegen dem SQL-Standard, erlauben. PostgreSQL verhält sich jedoch ganz getreu dem Standard.
Beachten Sie: Die Einschränkung in der HAVING-Klausel schränkt nicht die Anzahl der originär zu verarbeitenden Datensätze ein. Das würde eine WHERE-Klausel besorgen. HAVING bestimmt eine Gruppenauswahlbedingung. Das bedeutet auch, dass die folgende Formulierung ganz, ganz schlecht ist.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT last_name || ', ' || first_name AS customer_name , count(amount) AS count , sum(amount) AS total , min(amount) AS minimum , max(amount) AS maximum , avg(amount) AS avg FROM payment p JOIN customer c ON p.customer_id = c.customer_id WHERE to_char(payment_date, 'YYYY-MM') = '2007-05' GROUP BY last_name || ', ' || first_name HAVING last_name || ', ' || first_name = 'Olson, Annette'; |
Augenscheinlich interessieren wir uns ausschließlich für die Kundin „Annette Olson“. Das Ergebnis passt auch. Jedoch verarbeitet unser Datenbankserver hier auch alle anderen Kunden, erstellt die Statistiken und erst am Ende wird unsere Kundin herausgefiltert. Besser wäre es gewesen, erst die Kundin zu filtern und (in diesem Fall) auf einen Gruppenfilter zu verzichten.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT last_name || ', ' || first_name AS customer_name , count(amount) AS count , sum(amount) AS total , min(amount) AS minimum , max(amount) AS maximum , avg(amount) AS avg FROM payment p JOIN customer c ON p.customer_id = c.customer_id WHERE to_char(payment_date, 'YYYY-MM') = '2007-05' AND first_name = 'Annette' AND last_name = 'Olson' GROUP BY last_name || ', ' || first_name; |
Lange Rede, kurzer Sinn: Verwenden Sie WHERE für die Datensatzauswahl und HAVING für die Gruppenauswahl. Achten Sie darauf an der korrekten Stelle zu filtern. Eine Gruppenauswahl ist bereits syntaktisch auf HAVING eingeschränkt. Wie oben gezeigt, besteht aber die Möglichkeit, eine eigentliche Datensatzauswahl in HAVING zu formulieren, was zu ineffizientem Code führt! – Einige gute Datenbank-Entwicklungssysteme, wie z.B. DataGrip, erkennen das und warnen davor.