Was ist die SQL-Klausel GROUPING SETS, und wie wird sie verwendet?
GROUPING SETS sind Gruppen oder Sets von Spalten, nach denen Zeilen gruppiert werden können. Anstatt mehrere Abfragen zu schreiben und die Ergebnisse mit einer UNION zu kombinieren, können Sie einfach GROUPING SETS verwenden.
GROUPING SETS kann in SQL als eine Erweiterung der GROUP BY -Klausel betrachtet werden. Sie ermöglicht es Ihnen, mehrere Gruppierungssätze in derselben Abfrage zu definieren.
Sehen wir uns ihre Syntax an und wie sie einer GROUP BY mit mehreren UNION ALL Klauseln entsprechen kann.
SQL GROUPING SETS Syntax
Die allgemeine Syntax von GROUPING SETS lautet wie folgt:
SELECT aggregate_function(column_1) column_2, column_3,FROM table_nameGROUP BY GROUPING SETS ( (column_2, column_3), (column_2), (column_3), ()); |
Sie können sehen, wie wir nach den verschiedenen Sets gruppieren.
Diese Syntax ist äquivalent zu der folgenden längeren Abfrage, die GROUP BY mit UNION ALL verwendet, um die Ergebnisse zu kombinieren:
SELECT SUM(column_1), column_2, column_3FROM table_nameGROUP BY column_2, column_3UNION ALLSELECT SUM(column_1), column_2, NULLFROM table_nameGROUP BY column_2UNION ALLSELECT SUM(column_1), NULL, column_3FROM table_nameGROUP BY column_3UNION ALLSELECT SUM(column_1), NULL, NULLFROM table_name |
Wenn Sie GROUP BY auf diese Weise verwenden, benötigen Sie mehrere UNION ALL Klauseln, um die Daten aus verschiedenen Quellen zu kombinieren. UNION ALL verlangt außerdem, dass alle Ergebnissätze die gleiche Anzahl von Spalten mit kompatiblen Datentypen haben, so dass Sie die Abfragen anpassen müssen, indem Sie bei Bedarf einen NULL Wert hinzufügen.
Auch wenn die Abfrage so funktioniert, wie Sie es erwarten, hat sie zwei Hauptprobleme:
- Sie ist langwierig und nicht sehr handlich.
- Sie kann zu einem Leistungsproblem führen, da SQL die Verkaufstabelle jedes Mal durchsuchen muss.
Die GROUPING SETS Klausel behebt diese Probleme. Aber wie wirkt sie sich im Vergleich zu einer herkömmlichen GROUP BY -Klausel auf die Ausgabe aus? Schauen wir uns ein Beispiel an!
SQL GROUPING SETS Beispiel
Wir brauchen einige Beispieldaten. Erstellen wir eine Tabelle namens payments die alle Zahlungen enthält, die unser Unternehmen im Januar, Februar und März der letzten vier Jahre (2018 bis 2021) erhalten hat. Das genaue Geschäft, in dem die Zahlung erfolgte, wird in der Spalte store_id angegeben.
Um diese Tabelle zu erstellen, führen Sie die folgende Abfrage aus:
CREATE TABLE payment (payment_amount decimal(8,2), payment_date date, store_id int);INSERT INTO paymentVALUES(1200.99, '2018-01-18', 1),(189.23, '2018-02-15', 1),(33.43, '2018-03-03', 3),(7382.10, '2019-01-11', 2),(382.92, '2019-02-18', 1),(322.34, '2019-03-29', 2),(2929.14, '2020-01-03', 2),(499.02, '2020-02-19', 3),(994.11, '2020-03-14', 1),(394.93, '2021-01-22', 2),(3332.23, '2021-02-23', 3),(9499.49, '2021-03-10', 3),(3002.43, '2018-02-25', 2),(100.99, '2019-03-07', 1),(211.65, '2020-02-02', 1),(500.73, '2021-01-06', 3); |
Sie können die Daten mit dieser einfachen SELECT Klausel anzeigen:
SELECT * FROM payment ORDER BY payment_date; |
Die Ausführung dieser Abfrage ergibt das folgende Ergebnis:
| payment_amount | payment_date | store_id |
|---|---|---|
| 1200.99 | 2018-01-18 | 1 |
| 189.23 | 2018-02-15 | 1 |
| 3002.43 | 2018-02-25 | 2 |
| 33.43 | 2018-03-03 | 3 |
| 7382.10 | 2019-01-11 | 2 |
| 382.92 | 2019-02-18 | 1 |
| 100.99 | 2019-03-07 | 1 |
| 322.34 | 2019-03-29 | 2 |
| 2929.14 | 2020-01-03 | 2 |
| 211.65 | 2020-02-02 | 1 |
| 499.02 | 2020-02-19 | 3 |
| 994.11 | 2020-03-14 | 1 |
| 500.73 | 2021-01-06 | 3 |
| 394.93 | 2021-01-22 | 2 |
| 3332.23 | 2021-02-23 | 3 |
| 9499.49 | 2021-03-10 | 3 |
Sie können sehen, dass es für einige Geschäfte mehrere Einträge gibt. Stellen Sie sich vor, wir bereiten einen Bericht vor und möchten eine Gesamtsumme für jede Filiale sehen. Die Aggregatfunktion SUM() kann uns dabei helfen. Wir werden auch die GROUP BY Klausel verwenden, um unsere Ergebnisse nach Jahr und Filiale zu gruppieren.
SELECT SUM(payment_amount), YEAR(payment_date) AS 'Payment Year', store_id AS 'Store'FROM paymentGROUP BY YEAR(payment_date), store_idORDER BY YEAR(payment_date), store_id; |
| SUM(payment_amount) | Payment Year | Store |
|---|---|---|
| 1390.22 | 2018 | 1 |
| 3002.43 | 2018 | 2 |
| 33.43 | 2018 | 3 |
| 483.91 | 2019 | 1 |
| 7704.44 | 2019 | 2 |
| 1205.76 | 2020 | 1 |
| 2929.14 | 2020 | 2 |
| 499.02 | 2020 | 3 |
| 394.93 | 2021 | 2 |
| 13332.45 | 2021 | 3 |
Die Ergebnisse werden nach jeder eindeutigen Kombination von Jahr und Filiale aggregiert.
Wir können jedoch nicht die Gesamtzahlungen nach Jahr sehen: die Gesamtzahlungen für 2018, 2019, 2020 oder 2021. Wir können auch nicht die Gesamtzahlungen nach Filiale sehen, was eine nützliche Kennzahl wäre. Mit GROUPING SETS können wir diese Gesamtzahlen anzeigen.
SELECT SUM(payment_amount), YEAR(payment_date) AS 'Payment Year', store_id AS 'Store'FROM payment_newGROUP BY GROUPING SETS (YEAR(payment_date), store_id)ORDER BY YEAR(payment_date), store_id; |
| SUM(payment_amount) | Payment Year | Store |
|---|---|---|
| 3079.89 | NULL | 1 |
| 14030.94 | NULL | 2 |
| 13864.90 | NULL | 3 |
| 4426.08 | 2018 | NULL |
| 8188.35 | 2019 | NULL |
| 4633.92 | 2020 | NULL |
| 13727.38 | 2021 | NULL |
Wow, unsere Ergebnisse haben sich drastisch verändert! Jetzt sehen wir nur noch die Gesamtsummen für jede Filiale sowie die Gesamtsummen für jedes Jahr.
Für die Spalten, nach denen die Zeilen nicht gruppiert sind, sehen Sie die Werte von NULL.
Denken Sie daran, dass Sie mehrere GROUP BY -Klauseln in Ihre GROUPING SETS einfügen können. Wenn Sie dies auf unsere Abfrage anwenden, erhalten Sie folgendes Ergebnis:
SELECT SUM(payment_amount), YEAR(payment_date) AS 'Payment Year', store_id AS 'Store'FROM payment_newGROUP BY GROUPING SETS((YEAR(payment_date), store_id),(YEAR(payment_date)),(store_id))ORDER BY YEAR(payment_date), store_id; |
| SUM(payment_amount) | Payment Year | Store |
|---|---|---|
| 3079.89 | NULL | 1 |
| 14030.94 | NULL | 2 |
| 13864.90 | NULL | 3 |
| 4426.08 | 2018 | NULL |
| 1390.22 | 2018 | 1 |
| 3002.43 | 2018 | 2 |
| 33.43 | 2018 | 3 |
| 8188.35 | 2019 | NULL |
| 483.91 | 2019 | 1 |
| 7704.44 | 2019 | 2 |
| 4633.92 | 2020 | NULL |
| 1205.76 | 2020 | 1 |
| 2929.14 | 2020 | 2 |
| 499.02 | 2020 | 3 |
| 13727.38 | 2021 | NULL |
| 394.93 | 2021 | 2 |
| 13332.45 | 2021 | 3 |
Bevor wir dieses Tutorial beenden, sollten wir noch zwei weitere SQL GROUP BY Erweiterungen erwähnen, die sich für Ihr spezielles Projekt oder Szenario als nützlich erweisen könnten: ROLLUP und CUBE. Diese Themen werden sehr ausführlich in diesem Fortgeschrittenes SQL Lernpfad von LearnSQL.de behandelt, der Fensterfunktionen, GROUP BY Erweiterungen und rekursive Abfragen enthält.
SQL ROLLUP Beispiel
Ähnlich wie bei GROUPING SETS können Sie die Option ROLLUP in einer einzigen Abfrage verwenden, um mehrere Gruppierungssätze zu erzeugen.
ROLLUP setzt eine Hierarchie zwischen den Eingabespalten voraus. Wenn die Eingabespalten zum Beispiel sind:
GROUP BY ROLLUP(column_1,column_2) |
ist die Hierarchie für diese Abfrage column_1 > column_2, und ROLLUP generiert die folgenden Gruppierungssets:
(column_1, column_2)(column_1)() |
ROLLUP erzeugt alle Gruppierungssets, die in dieser Hierarchie sinnvoll sind. Jedes Mal, wenn sich der Wert von column_1 ändert, wird eine Zwischensummenzeile erzeugt; dies ist die von uns vorgegebene Hierarchie. Aus diesem Grund verwenden wir ROLLUP häufig zur Erstellung von Zwischensummen und Gesamtsummen in Berichten. Die Reihenfolge Ihrer Spalten in ROLLUP ist sehr wichtig.
Schauen wir uns eine Abfrage an, die ROLLUP verwendet:
SELECT SUM(payment_amount), YEAR(payment_date) AS 'Payment Year', store_id AS 'Store'FROM paymentGROUP BY ROLLUP (YEAR(payment_date), store_id)ORDER BY YEAR(payment_date), store_id |
| SUM(payment_amount) | Payment Year | Store |
|---|---|---|
| 30975.73 | NULL | NULL |
| 4426.08 | 2018 | NULL |
| 1390.22 | 2018 | 1 |
| 3002.43 | 2018 | 2 |
| 33.43 | 2018 | 3 |
| 8188.35 | 2019 | NULL |
| 483.91 | 2019 | 1 |
| 7704.44 | 2019 | 2 |
| 4633.92 | 2020 | NULL |
| 1205.76 | 2020 | 1 |
| 2929.14 | 2020 | 2 |
| 499.02 | 2020 | 3 |
| 13727.38 | 2021 | NULL |
| 394.93 | 2021 | 2 |
| 13332.45 | 2021 | 3 |
Die Gesamtsumme wird am Anfang des Ergebnisses angezeigt:
| 30975.73 | NULL | NULL |
|---|
Der Rest des Ergebnisses ist wie folgt aufgebaut. Zuerst wird die Jahressumme angezeigt:
| 4426.08 | 2018 | NULL |
|---|
Danach folgen die Gesamtsummen pro Geschäft und Jahr:
| 1390.22 | 2018 | 1 |
|---|---|---|
| 3002.43 | 2018 | 2 |
| 33.43 | 2018 | 3 |
Wie Sie sehen, erzeugt ROLLUP jedes Mal eine Zwischensummenzeile, wenn sich der Wert von Payment Year ändert, da dies die von uns angegebene Hierarchie ist. Dieses Beispiel zeigt, wie nützlich ROLLUP für Berichtszwecke sein kann.
SQL CUBE Beispiel
Ähnlich wie ROLLUP ist CUBE eine Erweiterung der Klausel GROUP BY. Sie ermöglicht es Ihnen, Zwischensummen für alle Kombinationen der in der GROUP BY -Klausel angegebenen Gruppierungsspalten zu erstellen.
CUBE ist eine Kombination aus GROUPING SETS und ROLLUP. Sie zeigt die detaillierte Ausgabe beider Klauseln.
SELECT SUM(payment_amount), YEAR(payment_date) AS 'Payment Year', store_id AS 'Store'FROM paymentGROUP BY CUBE (YEAR(payment_date), store_id)ORDER BY YEAR(payment_date), store_id |
| SUM(payment_amount) | Payment Year | Store |
|---|---|---|
| 30975.73 | NULL | NULL |
| 3079.89 | NULL | 1 |
| 14030.94 | NULL | 2 |
| 13864.90 | NULL | 3 |
| 4426.08 | 2018 | NULL |
| 1390.22 | 2018 | 1 |
| 3002.43 | 2018 | 2 |
| 33.43 | 2018 | 3 |
| 8188.35 | 2019 | NULL |
| 483.91 | 2019 | 1 |
| 7704.44 | 2019 | 2 |
| 4633.92 | 2020 | NULL |
| 1205.76 | 2020 | 1 |
| 2929.14 | 2020 | 2 |
| 499.02 | 2020 | 3 |
| 13727.38 | 2021 | NULL |
| 394.93 | 2021 | 2 |
| 13332.45 | 2021 | 3 |
Der Hauptunterschied zwischen dieser Ausgabe und dem Beispiel ROLLUP besteht darin, dass hier auch die Gesamtsumme für jede Filiale angezeigt wird.
| 3079.89 | NULL | 1 |
|---|---|---|
| 14030.94 | NULL | 2 |
| 13864.90 | NULL | 3 |
Abgesehen von diesen Zeilen sind alle Zeilen in diesem Ergebnis identisch mit dem Ergebnis von ROLLUP.
Damit ist unser Vergleich von GROUPING SETS, ROLLUP und CUBE abgeschlossen. In diesem Artikel finden Sie weitere Beispiele für das Gruppieren, Rollen und Würfeln von Daten.
Gruppieren Sie Ihre Daten effektiv mit den SQL GROUP BY-Erweiterungen
Um die SQL-Erweiterungen GROUP BY zu beherrschen, bedarf es einiger Übung. Optionen wie GROUPING SETS, ROLLUP und CUBE ermöglichen es Ihnen, die Ergebnisse Ihrer Abfragen auf verschiedene Weise zu manipulieren. Wenn Sie wissen, wie Sie diese Erweiterungen effektiv nutzen können, müssen Sie Ihre Daten nicht mehr manuell formatieren, bevor Sie sie an die entsprechenden Akteure weitergeben.
Wenn Sie Ihr Wissen in diesem Bereich weiter ausbauen möchten, sollten Sie diesen Kurs über GROUP BY-Erweiterungen von LearnSQL.de besuchen, der GROUPING SETS, ROLLUP und CUBE behandelt.
