Alles, was Sie über die SQL GROUP BY-Anweisung wissen müssen

Eskere Guru

Ein Großteil der Leistung relationaler Datenbanken beruht auf dem Filtern von Daten und dem Zusammenfügen von Tabellen. Deshalb vertreten wir diese Beziehungen in erster Linie. Moderne Datenbanksysteme bieten jedoch eine weitere wertvolle Technik: die Gruppierung.

Durch Gruppieren können Sie zusammenfassende Informationen aus einer Datenbank extrahieren. Sie können die Ergebnisse kombinieren, um nützliche statistische Daten zu erstellen. Durch die Gruppierung können Sie keinen Code für häufige Fälle wie die Mittelung von Zahlenlisten schreiben. Und es kann zu effizienteren Systemen führen.

Was macht die GROUP BY-Klausel?

GROUP BY gruppiert, wie der Name schon sagt, die Ergebnisse zu einer kleineren Menge. Die Ergebnisse bestehen aus einer Zeile für jeden einzelnen Wert der gruppierten Spalte. Wir können seine Verwendung zeigen, indem wir einige Beispieldaten mit Zeilen betrachten, die einige gemeinsame Werte haben.

Das Folgende ist eine sehr einfache Datenbank mit zwei Tabellen, die Plattenalben darstellen. Sie können eine solche Datenbank einrichten, indem Sie ein Grundschema für das von Ihnen ausgewählte Datenbanksystem schreiben . Die Albumtabelle enthält neun Zeilen mit einer Primärschlüssel- ID- Spalte und Spalten für Name, Künstler, Erscheinungsjahr und Verkauf:

 +----+---------------------------+-----------+--------------+-------+
 | id | name | artist_id | release_year | sales |
 +----+---------------------------+-----------+--------------+-------+
 | 1 | Abbey Road | 1 | 1969 | 14 |
 | 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
 | 3 | Rumours | 3 | 1977 | 28 |
 | 4 | Nevermind | 4 | 1991 | 17 |
 | 5 | Animals | 2 | 1977 | 6 |
 | 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
 | 7 | 21 | 6 | 2011 | 25 |
 | 8 | 25 | 6 | 2015 | 22 |
 | 9 | Bat Out of Hell | 7 | 1977 | 28 |
 +----+---------------------------+-----------+--------------+-------+

Der Künstlertisch ist noch einfacher. Es hat sieben Zeilen mit ID- und Namensspalten:

 +----+---------------+
 | id | name |
 +----+---------------+
 | 1 | The Beatles |
 | 2 | Pink Floyd |
 | 3 | Fleetwood Mac |
 | 4 | Nirvana |
 | 5 | Elton John |
 | 6 | Adele |
 | 7 | Meat Loaf |
 +----+---------------+

Mit nur einem einfachen Datensatz wie diesem können Sie verschiedene Aspekte von GROUP BY verstehen. Natürlich würde ein realer Datensatz viele, viele weitere Zeilen enthalten, aber die Prinzipien bleiben dieselben.

Gruppierung nach einer einzelnen Spalte

Nehmen wir an, wir möchten herausfinden, wie viele Alben wir für jeden Künstler haben. Beginnen Sie mit einer typischen SELECT- Abfrage, um die Spalte artist_id abzurufen:

 SELECT artist_id FROM albums

Dies gibt erwartungsgemäß alle neun Zeilen zurück:

 +-----------+
 | artist_id |
 +-----------+
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 2 |
 | 5 |
 | 6 |
 | 6 |
 | 7 |
 +-----------+

Um diese Ergebnisse nach Künstler zu gruppieren, fügen Sie den Ausdruck GROUP BY artist_id hinzu :

 SELECT artist_id FROM albums GROUP BY artist_id

Welches ergibt die folgenden Ergebnisse:

 +-----------+
 | artist_id |
 +-----------+
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 5 |
 | 6 |
 | 7 |
 +-----------+

Es gibt sieben Zeilen in der Ergebnismenge von der Gesamt reduziert neun in der Alben – Tabelle. Jede eindeutige artist_id hat eine einzelne Zeile. Um die tatsächlichen Zählungen zu erhalten, fügen Sie COUNT (*) zu den ausgewählten Spalten hinzu:

 SELECT artist_id, COUNT(*)
 FROM albums
 GROUP BY artist_id
 +-----------+----------+
 | artist_id | COUNT(*) |
 +-----------+----------+
 | 1 | 1 |
 | 2 | 2 |
 | 3 | 1 |
 | 4 | 1 |
 | 5 | 1 |
 | 6 | 2 |
 | 7 | 1 |
 +-----------+----------+

Die Ergebnisse gruppieren zwei Zeilenpaare für die Künstler mit den IDs 2 und 6 . Jedes hat zwei Alben in unserer Datenbank.

Verwandte: Das Essential SQL Commands Cheat Sheet für Anfänger

Zugriff auf gruppierte Daten mit einer Aggregatfunktion

Möglicherweise haben Sie die COUNT- Funktion bereits verwendet, insbesondere in der oben gezeigten COUNT (*) -Form. Es ruft die Anzahl der Ergebnisse in einem Satz ab. Sie können es verwenden, um die Gesamtzahl der Datensätze in einer Tabelle abzurufen:

 SELECT COUNT(*) FROM albums
 +----------+
 | COUNT(*) |
 +----------+
 | 9 |
 +----------+

COUNT ist eine Aggregatfunktion. Dieser Begriff bezieht sich auf Funktionen, die Werte aus mehreren Zeilen in einen einzelnen Wert übersetzen. Sie werden häufig in Verbindung mit der Anweisung GROUP BY verwendet.

Anstatt nur die Anzahl der Zeilen zu zählen, können wir eine Aggregatfunktion auf gruppierte Werte anwenden:

 SELECT artist_id, SUM(sales)
 FROM albums
 GROUP BY artist_id
 +-----------+------------+
 | artist_id | SUM(sales) |
 +-----------+------------+
 | 1 | 14 |
 | 2 | 30 |
 | 3 | 28 |
 | 4 | 17 |
 | 5 | 8 |
 | 6 | 47 |
 | 7 | 28 |
 +-----------+------------+

Die oben angegebenen Gesamtverkäufe für die Künstler 2 und 6 sind die Verkäufe ihrer mehreren Alben zusammen:

 SELECT artist_id, sales
 FROM albums
 WHERE artist_id IN (2, 6)
 +-----------+-------+
 | artist_id | sales |
 +-----------+-------+
 | 2 | 24 |
 | 2 | 6 |
 | 6 | 25 |
 | 6 | 22 |
 +-----------+-------+

Gruppierung nach mehreren Spalten

Sie können nach mehr als einer Spalte gruppieren. Fügen Sie einfach mehrere Spalten oder Ausdrücke ein, die durch Kommas getrennt sind. Die Ergebnisse werden nach der Kombination dieser Spalten gruppiert.

 SELECT release_year, sales, count(*)
 FROM albums
 GROUP BY release_year, sales

Dies führt normalerweise zu mehr Ergebnissen als die Gruppierung nach einer einzelnen Spalte:

 +--------------+-------+----------+
 | release_year | sales | count(*) |
 +--------------+-------+----------+
 | 1969 | 14 | 1 |
 | 1973 | 24 | 1 |
 | 1977 | 28 | 2 |
 | 1991 | 17 | 1 |
 | 1977 | 6 | 1 |
 | 1973 | 8 | 1 |
 | 2011 | 25 | 1 |
 | 2015 | 22 | 1 |
 +--------------+-------+----------+

Beachten Sie, dass in unserem kleinen Beispiel nur zwei Alben das gleiche Erscheinungsjahr und die gleiche Verkaufszahl haben (28 im Jahr 1977).

Nützliche Aggregatfunktionen

Abgesehen von COUNT funktionieren mehrere Funktionen gut mit GROUP. Jede Funktion gibt einen Wert zurück, der auf den Datensätzen basiert, die zu jeder Ergebnisgruppe gehören.

  • COUNT () gibt die Gesamtzahl der übereinstimmenden Datensätze zurück.
  • SUM () gibt die Summe aller Werte in der angegebenen Spalte addiert zurück.
  • MIN () gibt den kleinsten Wert in einer bestimmten Spalte zurück.
  • MAX () gibt den größten Wert in einer bestimmten Spalte zurück.
  • AVG () gibt den mittleren Durchschnitt zurück. Es ist das Äquivalent von SUM () / COUNT ().

Sie können diese Funktionen auch ohne GROUP-Klausel verwenden:

 SELECT AVG(sales) FROM albums
 +------------+
 | AVG(sales) |
 +------------+
 | 19.1111 |
 +------------+

Verwenden von GROUP BY mit einer WHERE-Klausel

Genau wie bei einem normalen SELECT können Sie WHERE weiterhin zum Filtern der Ergebnismenge verwenden:

 SELECT artist_id, COUNT(*)
 FROM albums
 WHERE release_year > 1990
 GROUP BY artist_id
 +-----------+----------+
 | artist_id | COUNT(*) |
 +-----------+----------+
 | 4 | 1 |
 | 6 | 2 |
 +-----------+----------+

Jetzt haben Sie nur die Alben, die nach 1990 veröffentlicht wurden und nach Künstlern gruppiert sind. Sie können auch einen Join mit der WHERE-Klausel verwenden, unabhängig von GROUP BY:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND release_year > 1990
 GROUP BY artist_id
 +---------+--------+
 | name | albums |
 +---------+--------+
 | Nirvana | 1 |
 | Adele | 2 |
 +---------+--------+

Beachten Sie jedoch Folgendes, wenn Sie versuchen, basierend auf einer aggregierten Spalte zu filtern:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND albums > 2
 GROUP BY artist_id;

Sie erhalten eine Fehlermeldung:

 ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'

Spalten, die auf aggregierten Daten basieren, stehen der WHERE-Klausel nicht zur Verfügung.

Verwenden der HAVING-Klausel

Wie filtern Sie die Ergebnismenge, nachdem eine Gruppierung stattgefunden hat? Die HAVING- Klausel befasst sich mit diesem Bedarf:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 GROUP BY artist_id
 HAVING albums > 1;

Beachten Sie, dass die HAVING-Klausel nach GROUP BY steht. Ansonsten ist es im Wesentlichen ein einfacher Ersatz des WHERE durch HAVING. Die Ergebnisse sind:

 +------------+--------+
 | name | albums |
 +------------+--------+
 | Pink Floyd | 2 |
 | Adele | 2 |
 +------------+--------+

Sie können weiterhin eine WHERE-Bedingung verwenden, um die Ergebnisse vor der Gruppierung zu filtern. Es funktioniert zusammen mit einer HAVING-Klausel zum Filtern nach der Gruppierung:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND release_year > 1990
 GROUP BY artist_id
 HAVING albums > 1;

Nur ein Künstler in unserer Datenbank hat nach 1990 mehr als ein Album veröffentlicht:

 +-------+--------+
 | name | albums |
 +-------+--------+
 | Adele | 2 |
 +-------+--------+

Ergebnisse mit GROUP BY kombinieren

Die GROUP BY-Anweisung ist ein unglaublich nützlicher Teil der SQL-Sprache. Es kann zusammenfassende Informationen zu Daten bereitstellen, beispielsweise für eine Inhaltsseite. Es ist eine hervorragende Alternative zum Abrufen großer Datenmengen. Die Datenbank bewältigt diese zusätzliche Arbeitslast gut, da sie aufgrund ihres Designs für den Job optimal ist.

Sobald Sie die Gruppierung und das Verknüpfen mehrerer Tabellen verstanden haben, können Sie den größten Teil der Leistung einer relationalen Datenbank nutzen.