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_name
GROUP 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_3
FROM table_name
GROUP BY
    column_2,
    column_3

UNION ALL

SELECT SUM(column_1), column_2, NULL
FROM table_name
GROUP BY column_2

UNION ALL

SELECT SUM(column_1), NULL, column_3
FROM table_name
GROUP BY column_3

UNION ALL

SELECT SUM(column_1), NULL, NULL
FROM 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 payment
VALUES
(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:

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 payment
GROUP BY YEAR(payment_date), store_id
ORDER BY YEAR(payment_date), store_id;

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_new
GROUP BY GROUPING SETS (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id;

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_new
GROUP BY GROUPING SETS
(
(YEAR(payment_date), store_id),
(YEAR(payment_date)),
(store_id)
)
ORDER BY YEAR(payment_date), store_id;

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 payment
GROUP BY ROLLUP (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id

Die Gesamtsumme wird am Anfang des Ergebnisses angezeigt:

Der Rest des Ergebnisses ist wie folgt aufgebaut. Zuerst wird die Jahressumme angezeigt:

Danach folgen die Gesamtsummen pro Geschäft und Jahr:

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 payment
GROUP BY CUBE (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id

Der Hauptunterschied zwischen dieser Ausgabe und dem Beispiel ROLLUP besteht darin, dass hier auch die Gesamtsumme für jede Filiale angezeigt wird.

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.