Wie man in SQL nach mehreren Spalten gruppiert

Bei der Analyse großer Datensätze erstellen Sie oft Gruppierungen und wenden Aggregatfunktionen an, um Summen oder Durchschnittswerte zu ermitteln. In diesen Fällen entfaltet die Verwendung der GROUP BY-Klausel mit mehreren Spalten ihr volles Potenzial.

GROUP BY ist eine Klausel des Befehls SELECT. Sie ermöglicht es Ihnen, verschiedene Statistiken für eine Gruppe von Zeilen zu berechnen. Sie können zum Beispiel GROUP BY mit einer Angestelltentabelle verwenden, um zu erfahren, wie viele Angestellte von jedem Geschlecht sind. Oder Sie können nach mehreren Spalten gruppieren, um das Durchschnittsalter der Fahrzeuge für jede Marke und jedes Modell in einer Fuhrpark-Tabelle zu ermitteln. In diesem Artikel werden wir im Detail untersuchen, wie die Gruppierung nach mehreren Spalten funktioniert.

In diesem Artikel wird davon ausgegangen, dass Sie bereits wissen, wie Sie GROUP BY in einer SQL-Abfrage verwenden können. Sie sind mit GROUP BY nicht vertraut? Der beste Weg, diese und andere grundlegende SQL-Konstruktionen zu lernen, ist unser interaktiver Kurs SQL für Anfänger. Er enthält 129 praktische Übungen. In jeder Übung erhalten Sie eine kurze Erklärung und eine Aufgabe, die Sie lösen müssen. Mit jeder abgeschlossenen Übung gewinnen Sie Vertrauen in Ihre SQL-Kenntnisse. Dieser Kurs eignet sich auch hervorragend, um die grundlegenden SQL-Funktionen zu wiederholen, wenn Ihre Kenntnisse etwas eingerostet sind.

Beginnen wir also mit einer Auffrischung eines einfachen Anwendungsfalls für GROUP BY.

GROUP BY 1 Spalte

Jede Kombination der Werte der in der GROUP BY Klausel angegebenen Spalte(n) stellt eine Gruppe dar; der Befehl SELECT mit einer GROUP BY Klausel zeigt eine einzelne Zeile für jede Gruppe an. Es ist auch gut zu wissen, dass GROUP BY Ihnen erlaubt, Aggregatfunktionen auf Spalten anzuwenden, die nicht in der ausstehenden Untergruppe enthalten sind.

Lassen Sie uns ein Beispiel sehen. Ich habe eine Tabelle mit dem Namen WorldWideFriends erstellt, die Daten über meine Freunde in verschiedenen Teilen der Welt speichert:

Ich möchte die Informationen in dieser Tabelle verwenden, um Nachforschungen anzustellen - z. B. um eine Liste der Länder zu erhalten, in denen meine Freunde leben, einschließlich der Anzahl der Freunde, die in jedem Land leben.

Wenn ich wissen möchte, wie viele Freunde ich in jedem Land habe, würde ich GROUP BY zusammen mit der Funktion COUNT() aggregate verwenden:

SELECT 
  Country, 
  COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country;

Mit dieser Abfrage erhalte ich eine Ergebnismenge, die die Zeilen mit demselben Land zu einer einzigen Zeile verdichtet, während COUNT(*) mir sagt, wie viele wiederholte Zeilen es für jedes Land gibt:

Die obige Abfrage gibt mir die Informationen, die ich bräuchte, wenn ich zum Beispiel wählen müsste, in welches Land ich reisen will, um möglichst viele meiner Freunde zu treffen. Wenn Sie mehr über die grundlegende Verwendung von GROUP BY lesen möchten, empfehle ich Ihnen unsere Artikel Was ist GROUP BY in SQL und Wie verwendet man GROUP BY.

Aber selbst wenn ich in ein Land reise, in dem viele meiner Freunde leben, kann es sein, dass sich diese Freunde in verschiedenen Staaten befinden. Ich habe vielleicht nicht die Zeit, von einem Staat zum anderen zu reisen, um sie alle zu besuchen. Ich muss also meine Suche etwas verfeinern, um den geografischen Ort zu finden, an dem eine höhere Konzentration meiner Freunde vorhanden ist.

GROUP BY 2 Spalten

Jetzt muss ich wissen, wie meine Freunde nach Bundesland und Land verteilt sind. Das kann ich herausfinden, indem ich die Spalte State zu meiner vorherigen GROUP BY Land (mit Kommas getrennt) und in der SELECT Klausel hinzufüge. Die Abfrage sieht wie folgt aus:

SELECT 
  Country, 
  State, 
  COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country, State;

Wenn man sich die Ergebnisse dieser Abfrage ansieht, sieht man, dass einige der Länder, die vorher nur in einer Zeile standen, jetzt in mehreren Zeilen erscheinen. Der Grund dafür ist, dass die Abfrage, wenn wir das Feld State hinzufügen, die Gruppen mit den Zeilen zusammenstellen muss, die denselben Wert in Country und State haben.

In der vorherigen Abfrage hatte die Zeile, die "Kolumbien" entspricht, eine 3 im Feld HowMany. In diesem Fall erscheint "Kolumbien" in zwei Zeilen mit unterschiedlichen Werten für den Staat: eine für "Antioquia" und die andere für "Cundinamarca". Im Feld HowMany steht in der Zeile für "Antioquia" der Wert 1, in der Zeile für "Cundinamarca" der Wert 2. Das bedeutet, dass es in der disaggregierten Liste zwei Zeilen mit Country = "Kolumbien" und State = "Cundinamarca" gibt und nur eine mit Country = "Kolumbien" und State = "Antioquia".

Die Summe der HowMany Werte dieser beiden Zeilen entspricht logischerweise dem vorherigen HowMany Wert für die Zeile, die "Kolumbien" entspricht. Das Gleiche gilt für alle anderen Länder, die in mehrere Zeilen mit verschiedenen Staaten unterteilt sind.

GROUP BY Mehrere Spalten

Wenn ich meine Reise so kurz wie möglich machen und trotzdem so viele Freunde wie möglich besuchen möchte, muss ich nur die Spalte City zu meiner Abfrage hinzufügen - sowohl in SELECT als auch in GROUP BY - um zu sehen, welche Städte die meisten Freunde haben:

SELECT 
  Country, 
  State, 
  City, 
  COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country, State, City;

Wenn wir Spalten zu GROUP BY hinzufügen, erhöht sich die Anzahl der Zeilen im Ergebnis. Das liegt daran, dass die Anzahl der möglichen Wertekombinationen zunimmt. Wenn ich die Spalte City zur SQL GROUP BY hinzufüge, nimmt die Größe des Ergebnisses erheblich zu:

In diesem Fall halte ich es für besser, nur die Städte anzuzeigen, in denen es mehr als einen meiner Freunde gibt. Um die Ergebnisse zusammenzufassen, werde ich also die Klausel HAVING verwenden. Mit dieser Klausel kann ich bei Verwendung von GROUP BY eine Bedingung für die Ergebnisse der Aggregatfunktionen festlegen. In diesem Fall ist die Bedingung, dass die Anzahl der Freunde größer als 1 ist (COUNT(*) > 1). Nach dem Einfügen der HAVING Klausel sieht die Abfrage wie folgt aus:

SELECT Country, State, City, COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country, State, City
HAVING COUNT(*) > 1;

Auf diese Weise wird das Ergebnis der Abfrage auf eine einzige Zeile reduziert, die mir die einzige Stadt anzeigt, in der es mehr als einen meiner Freunde gibt:

Andere Möglichkeiten der Verwendung von GROUP BY mit mehreren Spalten

Es ist üblich, GROUP BY mit mehreren Spalten zu verwenden, wenn zwei oder mehr der Spalten in einem Abfrageergebnis eine Hierarchie von Klassifizierungen mit mehreren Ebenen bilden. Solche Hierarchien sind in vielen Bereichen zu finden, wie zum Beispiel:

• Detaillierte Verkaufsdaten mit dem Verkaufsdatum unterteilt in Jahr, Quartal und Monat.
• Der Produktkatalog eines Herstellers, gegliedert nach Familie, Marke, Produktlinie und Modell.
• Die Gehaltsliste der Mitarbeiter eines Unternehmens, gegliedert nach Management, Branche, Abteilung.

In all diesen Fällen können verschiedene Untergruppen von Spalten in GROUP BY verwendet werden, um vom Allgemeinen zum Besonderen zu gelangen.

Verwendung von GROUP BY für mehrere Spalten: Gruppieren einer Hierarchie

Schauen wir uns eine Beispiel-Ergebnismenge von Verkaufsdaten an. Nehmen wir an, Sie haben eine Ansicht namens ViewSales, die die folgenden Informationen zurückgibt:

Es ist leicht zu erkennen, dass die ersten Felder dieser Tabelle eine Hierarchie bilden, mit dem Jahr als höchster Ebene und dem Datum als niedrigster Ebene. Mit Hilfe von GROUP BY und der Funktion SUM() können wir die Gesamtverkaufsmengen nach Year, nach Quarter, nach Month oder nach Date ermitteln. Wenn Sie die Gesamtzahl der verkauften Einheiten und den durchschnittlichen Stückpreis pro Year und Quarter erhalten möchten, müssen Sie diese beiden Spalten in SELECT und in GROUP BY angeben:

SELECT 
  Year, 
  Quarter, 
  SUM(Quantity) AS TotalQty, 
  AVG(Unit_Price) as AvgUnit_Prc
FROM ViewSales
GROUP BY Year, Quarter;

Das Ergebnis wird sein:

Bitte beachten Sie, dass die Daten in den verschiedenen Gruppierungsspalten unabhängig voneinander sind, obwohl es eine hierarchische Reihenfolge gibt. Das bedeutet, dass, wenn Sie nur nach Quarter anstatt nach Year plus Quarter gruppieren, die aggregierten Berechnungen die Informationen aus demselben Quartal für alle Jahre kombinieren (d. h. alle Q2 haben eine Zeile):

SELECT 
  Quarter, 
  SUM(Quantity) AS TotalQty,
  AVG(Unit_Price) as AvgUnit_Prc
FROM ViewSales
GROUP BY Quarter;

Dies ist kein Fehler; Sie müssen nur verstehen, dass die Ergebnisse unterschiedliche Erkenntnisse vermitteln. Mit der letzteren Abfrage können Sie die Umsatzleistung zwischen verschiedenen Quartalen unabhängig vom Jahr vergleichen (z. B. um saisonale Faktoren zu erkennen, die sich auf den Umsatz zur gleichen Zeit des Jahres auswirken), während die erste Abfrage den Umsatz für jedes einzelne Jahr und Quartal vergleicht.

Verwendung von GROUP BY für mehrere Spalten: Nicht-hierarchische Gruppierung

Im vorigen Beispiel haben wir gesehen, dass die Gruppierung nach mehreren Spalten es ermöglicht, vom Allgemeinen zum Besonderen überzugehen, wenn Datensätze mit Spalten vorliegen, die eine Datenhierarchie bilden. Aber in Situationen, in denen eine Ergebnismenge aus Spalten besteht, die keine Hierarchie bilden, ermöglicht die Verwendung von GROUP BY mit mehreren Spalten die Entdeckung verborgener Wahrheiten in großen Datensätzen; sie kombiniert Attribute, die auf den ersten Blick nichts miteinander zu tun haben.

Nehmen wir zum Beispiel an, wir haben eine Tabelle mit dem Namen Downloads , die Informationen über Personen speichert, die in den letzten zwei Jahren Filme von einem Streaming-Dienst heruntergeladen haben. Diese Tabelle hat eine Zeile für jeden Download, und jede Zeile enthält die folgenden Informationen über jede Person, die einen Film heruntergeladen hat:

• Age
• Gender
• Nationality

In jeder Zeile werden auch diese Attribute zu jedem heruntergeladenen Film erfasst:

• Genre
• Year
• Country

Mithilfe von GROUP BY mit mehreren dieser Spalten und der Funktion COUNT(*) können wir Korrelationen zwischen den Spalten erkennen. Um z. B. die Vorlieben für Filmgenres nach Alter herauszufinden, geben wir Folgendes ein:

SELECT 
  Age, 
  Genre, 
  COUNT(*) AS Downloads
FROM Downloads
GROUP BY Age, Genre

Als Ergebnis erhalten wir etwas wie dieses:

Wir könnten auch GROUP BY 3 Spalten verwenden, um (zum Beispiel) die Genrepräferenzen nach Geschlecht und Nationalität zu ermitteln:

SELECT 
  Gender, 
  Nationality, 
  Genre, 
  COUNT(*) AS Downloads
FROM Downloads
GROUP BY Gender, Nationality, Genre

Das Ergebnis wäre dann etwa so:

GROUP BY mit mehreren Spalten liefert facettierte Informationen

GROUP BY ist ein leistungsfähiges Werkzeug, um Erkenntnisse aus großen Datensätzen zu gewinnen, die auf andere Weise nur schwer zu bearbeiten sind. Durch die Verwendung von GROUP BY mit mehreren Spalten können Sie das volle Potenzial dieses Tools nutzen, um die Wahrheiten eines Datensatzes zu enthüllen und verschiedene Facetten davon zu sehen. Um dies erfolgreich zu tun, ist es wichtig, dass Sie verstehen - und zu erklären wissen - was eine nach mehreren Spalten gruppierte SQL-Ergebnismenge darstellt.

Wenn Sie vorhaben, ernsthafte Datenanalysen durchzuführen, sollten Sie unseren interaktiven Kurs SQL für Anfänger besuchen, um alle Werkzeuge kennen zu lernen, die SQL zu bieten hat. Folgen Sie auch diesen Links, wenn Sie weitere Erklärungen zu GROUP BY benötigen oder weitere Beispiele für GROUP BY in SQL sehen möchten.