Wie funktioniert SQL GROUP BY?

Die Gruppierung von Ergebnissen ist eine leistungsstarke SQL-Funktion, die es Ihnen ermöglicht, Schlüsselstatistiken für eine Gruppe von Datensätzen zu berechnen.

GROUP BY ist eine der mächtigsten SQL-Klauseln. Sie ermöglicht es Ihnen, Daten auf eine neue Art und Weise zu betrachten und Schlüsselmetriken zu finden (wie z. B. die Durchschnitts-, Maximal- und Minimalwerte in einer Gruppe von Datensätzen).

Ohne GROUP BY sind alle Ergebnisse, die wir erhalten, auf Datensätze bezogen. Mit GROUP BY können wir Gruppen von Datensätzen erstellen und Metriken für jede Gruppe berechnen. In diesem Artikel erfahren Sie, wie GROUP BY Ihre SQL-Abfragen viel leistungsfähiger und vielfältiger macht.

GROUP BY, Teil 1: Daten gruppieren

Nehmen wir an, wir haben ein kleines Hotel in Patagonien. Wir haben auch eine Datenbank, die die Namen der Gäste, ihre Herkunftsorte, ihr Alter, das Datum des Eincheckens, das Datum des Auscheckens und vieles mehr enthält. Diese Daten befinden sich in zwei Tabellen namens room_guest und guest. Schauen Sie sich das an:

room_guest

guest

Wir möchten einige Statistiken berechnen, damit wir mehr Gäste buchen können. Mit der SQL GROUP BY Klausel können wir Datensätze basierend auf Daten in einer bestimmten Spalte (oder Spalten) gruppieren. Wir können Datensätze in der Tabelle room_guest basierend auf dem Wert der Spalte origin_city gruppieren. Dann gehören alle Datensätze von Gästen aus "Genua" zu einer Gruppe, alle Datensätze von Gästen aus "Dubai" zu einer anderen Gruppe und so weiter. Die folgende Tabelle zeigt jede Gruppe von Datensätzen in einer anderen Farbe.

Angenommen, der Hotelbesitzer möchte wissen, wie viele Gäste aus den einzelnen Städten kommen. Um das herauszufinden, müssen wir die Anzahl der Datensätze in jeder Gruppe zählen. Mit anderen Worten, wir benötigen die Aggregatfunktion COUNT(*), die die Anzahl der Datensätze in einer Gruppe zurückgibt. COUNT() ist eine sehr gebräuchliche Funktion; wir werden später in diesem Artikel darauf zurückkommen.

Wir brauchen also eine Abfrage, um Gruppen von Datensätzen mit demselben Wert in origin_city zu erstellen und dann die Anzahl der Datensätze in jeder Gruppe zu zählen. Die Abfrage würde wie folgt aussehen:

SELECT 	origin_city,
COUNT(*) AS quantity_of_guests
FROM   	room_guest 
GROUP BY	origin_city

Sie können die Anzahl der Gäste aus jeder Stadt in der unten stehenden Ergebnistabelle mit der zuvor gezeigten farbigen Tabelle vergleichen:

Beachten Sie, dass die Anzahl der Zeilen in den Abfrageergebnissen der Anzahl der Gruppen entspricht, die durch die Klausel GROUP BY erstellt wurden. Eine Gruppe für jede Stadt, eine Zeile für jede Stadt.

Zum Abschluss dieser Einführung in GROUP BY möchte ich Ihnen den Artikel Getting the Hang of the GROUP BY Clause empfehlen. Er enthält eine vollständige Beschreibung von GROUP BY und mehrere Beispiele für die häufigsten Fehler.

GROUP BY, Teil 2: Aggregationsfunktionen

Die Gruppierung nach einem Wert ist zwar praktisch, aber die wahre Stärke von GROUP BY liegt in der Verwendung mit Aggregatfunktionen. Ich würde sogar so weit gehen zu sagen, dass jede SQL Abfrage, die eine GROUP BY Klausel verwendet, mindestens eine Aggregatfunktion haben sollte. (Aber das ist nicht zwingend erforderlich.)

Im vorigen Abschnitt haben wir erwähnt, dass GROUP BY zur Erstellung von Gruppen und zur Berechnung von Metriken verwendet wird. Metriken werden mit Aggregationsfunktionen wie COUNT(), SUM(), AVG(), MIN() und MAX() berechnet. Die Werte, die von jeder dieser Funktionen berechnet werden, sind selbsterklärend. Sie haben jedoch alle etwas gemeinsam: Alle Aggregationsfunktionen geben einen Wert zurück, der auf allen Datensätzen der Gruppe basiert.

Lassen Sie uns ein Beispiel betrachten. Der Hotelbesitzer möchte wissen, welcher Wert für jedes Zimmer maximal berechnet wurde. Außerdem möchte er den minimalen und den durchschnittlichen Rechnungswert für jedes Zimmer erfahren. Hier ist die Abfrage, gefolgt von den Ergebnissen:

SELECT 	room_number,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest 
GROUP BY	room_number

Datensätze nach mehreren Spalten gruppieren

In manchen Fällen müssen wir nach zwei oder mehr Spalten gruppieren. Können wir das mit GROUP BY tun? Natürlich!

In der vorherigen Abfrage haben wir einen Bericht erstellt, der analysiert, wie viel Geld jeder Raum einbringt. Einige Zimmer können jedoch zu verschiedenen Jahreszeiten auf Premium- oder Standardniveau konfiguriert sein (siehe Zimmernummer 2002); um eine korrekte Analyse durchzuführen, müssen wir daher die Datensätze anhand von zwei Spalten gruppieren: room_number und room_level.

Bevor wir uns der Abfrage zuwenden, wollen wir anhand von Farben sehen, wie die Datensätze durch die Klausel GROUP BY room_number, room_level gruppiert sind. Denken Sie daran, dass die Datensätze in jeder Gruppe genau dieselben Werte in room_number und room_level haben müssen. Die erste Gruppe ist zum Beispiel für room_number = 1001 und room_level = ‘standard’.

Die Abfrage lautet:

SELECT 	room_number,
		room_level,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest 
GROUP BY	room_number, room_level

Die folgende Tabelle zeigt die Ergebnisse dieser Abfrage. Sie können diese Tabelle mit der vorherigen Tabelle vergleichen, um die Ergebnisse zu überprüfen.

NULL-Werte gruppieren

Wie jeder andere Wert haben auch die Werte von NULL ihre eigene Gruppe. Wenn wir in einer der Spalten von GROUP BY einen NULL haben, wird für diese Datensätze eine zusätzliche Gruppe erstellt. Um dies zu demonstrieren, müssen wir ein Paar Datensätze mit NULL Werten in der Spalte origin_city einfügen:

INSERT INTO into room_guest VALUES ('Kevin C.', NULL, 2001, '2019-07-25', '2019-08-07', NULL, 'standard', 10500);
INSERT INTO into room_guest VALUES  ('Karl J.', NULL, 1002, '2019-11-12', '2019-11-22', NULL, 'premium', 13900);

Dann wird diese Abfrage ...

SELECT 	origin_city,
COUNT(*) AS quantity_of_guests
FROM   	room_guest 
GROUP BY	origin_city

... das folgende Ergebnis anzeigen. Beachten Sie die neue Gruppe für NULL origin_city Werte in der ersten Zeile:

Verwendung von WHERE mit GROUP BY

Die WHERE Klausel wird häufig in SQL Abfragen verwendet, daher ist es wichtig zu verstehen, wie sie in Kombination mit GROUP BY funktioniert.

Die WHERE -Klausel wird vor der GROUP BY-Klausel angewandt. Das bedeutet, dass alle Datensätze zunächst nach WHERE gefiltert werden; anschließend werden die Datensätze, die der WHERE -Bedingung entsprechen, anhand der GROUP BY -Kriterien gruppiert.

Lassen Sie uns als Beispiel die vorherige Abfrage verwenden, aber dieses Mal filtern wir nach Gästen, die aus den Städten San Francisco und Los Angeles kommen. Die Abfrage lautet:

SELECT 	room_number,
		room_level,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest
WHERE		origin_city IN (‘San Francisco’,’Los Angeles’ )
GROUP BY	room_number, room_level

Wie erwartet ist diese Ergebnismenge kürzer als die vorherigen; die WHERE Klausel hat viele Gäste herausgefiltert, und nur die Datensätze für Zimmer in San Francisco und Los Angeles wurden von der GROUP BY Klausel verarbeitet.

Vermeiden von Problemen mit GROUP BY

Bei den ersten Schritten mit GROUP BY stößt man häufig auf die folgenden Probleme. Hier erfahren Sie, wie Sie diese vermeiden können.

Probleme beim Zählen

Schauen wir uns einen ähnlichen Fall an, bei dem wir mehr als eine zusätzliche Spalte in die GROUP BY Klausel einfügen müssen. In der ersten Abfrage haben wir nach origin_city gruppiert. Einige Städte haben jedoch denselben Namen (weil sie in verschiedenen Staaten oder Ländern liegen). In unserem Datensatz haben wir zwei verschiedene Städte mit dem Namen San Pedro, eine in Argentinien und die andere in Spanien. Wir wollen sie nicht zusammenzählen, da es sich um zwei verschiedene Orte handelt.

Um diese Städte getrennt zu zählen, müssen wir die Datensätze anhand der Spalten city_origin, state und country gruppieren. Dann wiederholen wir die erste Abfrage, fügen aber die Spalten state und country zur Klausel GROUP BY hinzu. Wenn wir jedoch Spalten zu GROUP BY hinzufügen, sollten wir sie auch zu SELECT hinzufügen.

Da die Spalten Staat und Land in der Tabelle guest befinden, müssen wir die Tabellen JOIN room_guest und guest. Hier ist die Abfrage, die wir haben:

SELECT 	origin_city, state, country
COUNT(*) AS number_of_guests
FROM   	room_guest 
JOIN		guest ON guest.guest_name = room_guest.guest_name
GROUP BY	origin_city, state, country

Die Ergebnisse zeigen zwei verschiedene "San Pedro"-Städte, da wir state und country als zusätzliche Spalten in der GROUP BY Klausel verwendet haben.

Es gibt noch ein Problem in dieser Abfrage zu beheben: Wenn dieselbe Person das Hotel zweimal besucht hat, wird diese Person zweimal gezählt. Das ist nicht unbedingt falsch, aber was ist, wenn wir die Anzahl der einzelnen Besucher des Hotels wissen wollen? Dann müssten wir COUNT(distinct guest_name) verwenden. Die Gruppierungsfunktion COUNT(distinct column) gibt die Anzahl der eindeutigen Werte für eine bestimmte Spalte in einer Gruppe von Datensätzen zurück.

In der folgenden Abfrage fügen wir die Funktion COUNT(distinct) hinzu. Wir behalten auch die ursprüngliche COUNT(*) bei, damit der Leser beide Ergebnisse vergleichen kann:

SELECT 	origin_city, state, country
COUNT(distinct guest_name) AS number_of_unique_guests,
COUNT(*) AS number_of_guests
FROM   	room_guest 
JOIN		guest ON guest.guest_name = room_guest.guest_name
GROUP BY	origin_city, state, country

Jetzt können wir sehen, dass das Hotel insgesamt drei Besuche eines Einwohners von Dubai erhalten hat, aber dass diese drei Besuche von zwei verschiedenen Personen (Peter S. und Olek V.) gemacht wurden.

Bevor ich diesen Abschnitt schließe, empfehle ich Ihnen, sich dieses 5-minütige Video auf GROUP BY für Anfänger anzusehen. Es ist eine sehr dynamische Art, SQL zu lernen.

Auslassen von nicht aggregierten Spalten in GROUP BY

Ein weiterer sehr häufiger GROUP BY Fehler ist das Hinzufügen einer nicht aggregierten Spalte (d. h. einer Spalte, die nicht in einer Aggregatfunktion verwendet wird) in SELECT, die Sie nicht in GROUP BY haben. Um diesen Fehler zu vermeiden, befolgen Sie eine sehr einfache Regel: Alle Spalten in SELECT sollten in der GROUP BY Klausel erscheinen oder in einer Aggregatfunktion verwendet werden.

Probieren wir eine ungültige Abfrage aus, um den Fehler zu sehen:

SELECT    room_number,
	    room_level,
	    origin_city, --This column is invalid, is not in the GROUP BY
    COUNT(*) AS quantity_of_visitors,
FROM      room_guest 
GROUP BY  room_number, room_level

Wenn wir diese Abfrage ausführen, erhalten wir die folgende Fehlermeldung:

ERROR:  The column «room_guest.origin_city» must be in the GROUP BY clause
LINE 3:   guest_age,

Wir können den Fehler beheben, indem wir die Spalte origin_city zur Klausel GROUP BY hinzufügen:

	SELECT 	room_number,
			room_level,
			Origin_city,
			COUNT(*) AS quantity_of_visitors
	FROM		room_gest
	GROUP BY	room_number, room_level, origin_city -- origin_city added

Wenn Sie versuchen, den Unterschied zwischen GROUP BY und ORDER BY herauszufinden, lesen Sie den Artikel Der Unterschied zwischen GROUP BY und ORDER BY in einfachen Worten. Er wird Ihnen helfen, den Unterschied zu verstehen.

Es gibt noch mehr zu tun mit GROUP BY

Wir haben also gelernt, wie man GROUP BY verwendet, um Datensätze nach gemeinsamen Werten zu gruppieren. Wir wissen, dass die Aggregatfunktionen MIN(), MAX(), AVG() und SUM() verschiedene Statistiken berechnen. Und die Funktion COUNT() macht eine Menge Dinge:

• COUNT(*) Sie zählt alle Zeilen.
• COUNT(guest_name) Zählt alle Nicht-NULL-Werte in der Spalte guest_name.
• COUNT(distinct guest_name) Zählt alle verschiedenen Nicht-NULL-Werte in der Spalte guest_name.

Bei der Gruppierung erhält NULL seine eigene Gruppe. Und alle nicht gruppierten Spalten in SELECT müssen in GROUP BY vorhanden sein.

Aufgrund der Länge des Artikels bin ich nicht auf die HAVING-Klausel eingegangen, die eine Art WHERE -Klausel ist und zum Filtern von Gruppen anstelle von Datensätzen verwendet wird. Für die Leser, die einen Schritt weiter gehen wollen, hinterlasse ich einen Link zu unserem SQL für Anfänger Kurs, der viele interessante Themen behandelt. Ein guter Weg, um Ihre SQL-Kenntnisse zu erweitern!