Kategorien:: Abfragesyntax

GROUP BY GROUPING SETS¶

GROUPING SETS ist eine leistungsfähige Erweiterung der GROUP BY-Klausel, mit der mehrere GROUP BY-Klauseln in einer einzigen Anweisung verarbeitet werden können. Ein Gruppierungssatz ist ein Satz von Dimensionsspalten.

GROUPING SETS-Ausdrücke können mit anderen GROUP BY-Ausdrücken kombiniert werden, wodurch dieses Konstrukt zu einem integrierten Teil der GROUP BY-Klausel anstelle eines separaten Konstrukts wird. Sie können zum Beispiel GROUP BY x, GROUPING SETS(y, z) schreiben, um nach Spalte x in Kombination mit separaten Gruppierungen für y und z zu gruppieren.

Ein GROUPING SETS-Ausdruck entspricht einer Verknüpfung von zwei oder mehr GROUP BY-Operationen im gleichen Resultset. Beispiel:

GROUP BY GROUPING SETS((a)) ist äquivalent zur einzelnen Gruppierungssatzoperation GROUP BY a.
GROUP BY GROUPING SETS((a), (b)) ist äquivalent zu GROUP BY a UNION ALL GROUP BY b.

Beachten Sie, dass GROUPING SETS(a, b) ohne zusätzliche Klammern logisch äquivalent zu GROUPING SETS((a), (b)) ist, da beide zwei separate Gruppierungssätze erstellen, einen für die Spalte a und einen für Spalte b. Dieser Ausdruck unterscheidet sich deutlich von GROUPING SETS((a, b)), bei dem ein einzelner Gruppierungssatz erstellt wird, der nach beiden Spalten gruppiert.

Syntax¶

SELECT ...
FROM ...
[ ... ]
GROUP BY [ groupItem [ , groupItem [ , ... ] ] , ] GROUPING SETS ( groupSet [ , groupSet [ , ... ] ] )
[ ... ]

Copy

Wobei:

groupItem ::= { <column_alias> | <position> | <expr> }

groupSet ::= groupItem | ( groupItem [ , groupItem [ , ... ] ] )

Copy

Parameter¶

column_alias: Spaltenalias, der in der Liste SELECT des Abfrageblocks angezeigt wird.
position: Position eines Ausdrucks in der Liste SELECT.
expr: Jeder Ausdruck in Tabellen im aktuellen Bereich.

Nutzungshinweise¶

Snowflake erlaubt bis zu 128 Gruppierungssätze im gleichen Abfrageblock.
Syntaxvarianten mit Klammern:
- GROUPING SETS(a, b) ist die Kurzform für GROUPING SETS((a), (b)). Beide erstellen zwei separate Gruppierungssätze: einen, der nach Spalte a gruppiert, und einen weiteren, der nach Spalte b gruppiert.
- GROUPING SETS((a, b)) erstellt einen einzelnen Gruppierungssatz, der nach Spalte a und nach Spalte b gruppiert (ähnlich wie GROUP BY a, b).
Sie können reguläre GROUP BY-Spalten mit GROUPING SETS kombinieren: GROUP BY x, GROUPING SETS(y, z) gruppiert nach Spalte x in Kombination mit separaten Gruppierungen für y und z.
Die Ausgabe enthält typischerweise einige NULL-Werte. Da GROUP BY GROUPING SETS die Ergebnisse von zwei oder mehr Resultsets zusammenführt, von denen jedes nach verschiedenen Kriterien gruppiert wurde, können einige Spalten, die in einem Resultset einen Einzelwert aufweisen, viele entsprechende Werte im anderen Resultset haben. Wenn Sie zum Beispiel eine Vereinigung auf eine nach Abteilungen gruppierte Menge von Mitarbeitenden und eine nach Dienstalter gruppierten Menge von Mitarbeitenden anwenden, gehören die Mitglieder der Menge mit dem höchsten Dienstalter nicht unbedingt alle derselben Abteilung an, sodass der Wert von department_name auf NULL gesetzt wird. Aus diesem Grund enthalten die folgenden Beispiele NULL-Werte.

Siehe auch:¶

GROUPING (Dienstprogrammfunktion, um zu ermitteln, welche Gruppierungsebene jede Zeile erzeugt hat)
GROUP BY ROLLUP
GROUP BY CUBE

Beispiele¶

In diesen Beispielen wird eine Tabelle mit Informationen über Krankenpfleger verwendet, die für die Katastrophenhilfe ausgebildet sind. Alle diese Pflegekräfte haben eine Lizenz als Pflegekräfte (z. B. ein RN hat eine Lizenz als „staatlich geprüfte Pflegekraft“) und eine zusätzliche Lizenz in einem katastrophenbezogenen Fachgebiet (wie z. B. Such- und Rettungsdienst, Funkverkehr und so weiter). In diesem Beispiel werden der Einfachheit halber nur zwei Kategorien von Lizenzen verwendet:

Krankenpflege: RN (staatlich geprüfter Krankenpfleger) und LVN (ausgebildeter Krankenpfleger mit Lizenz).
Amateurfunk: Amateurfunklizenzen umfassen „Techniker“, „Allgemein“ und „Amateur Extra“.

Die folgenden Befehle erstellen und laden die Tabelle:

CREATE or replace TABLE nurses (
  ID INTEGER,
  full_name VARCHAR,
  medical_license VARCHAR,   -- LVN, RN, etc.
  radio_license VARCHAR      -- Technician, General, Amateur Extra
  )
  ;

INSERT INTO nurses
    (ID, full_name, medical_license, radio_license)
  VALUES
    (201, 'Thomas Leonard Vicente', 'LVN', 'Technician'),
    (202, 'Tamara Lolita VanZant', 'LVN', 'Technician'),
    (341, 'Georgeann Linda Vente', 'LVN', 'General'),
    (471, 'Andrea Renee Nouveau', 'RN', 'Amateur Extra')
    ;

Copy

In dieser Abfrage wird GROUP BY GROUPING SETS verwendet:

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license)
  ORDER BY 3 DESC NULLS FIRST;

Copy

In den ersten beiden Zeilen wird die Anzahl von RNs und LVNs (zwei Arten von Pflegelizenzen) angezeigt. Die NULL-Werte in der Spalte radio_license für diese beiden Zeilen sind beabsichtigt; mit der Abfrage wurden alle LVNs zusammen (und alle RNs zusammen) gruppiert – unabhängig von ihrer Funklizenz –, sodass die Ergebnisse in der Spalte radio_license keinen Wert für jede Zeile anzeigen können, die notwendigerweise für alle LVNs oder RNs gelten, die in dieser Zeile gruppiert sind.

In den nächsten drei Zeilen wird die Anzahl der Krankenpfleger mit den einzelnen Arten von Amateurfunklizenz („Technician“, „General“ und „Amateur Extra“) angezeigt. Der NULL-Wert für medical_license in jeder dieser drei Zeilen ist beabsichtigt, da keine medizinische Zulassung notwendigerweise für alle Mitglieder der einzelnen Zeilen gilt.

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        3 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | NULL            | Technician    |
|        1 | NULL            | General       |
|        1 | NULL            | Amateur Extra |
+----------+-----------------+---------------+

Das folgende Beispiel veranschaulicht den Unterschied zwischen der separaten Gruppierung nach Spalten und der gemeinsamen Gruppierung nach Spalten . Die Abfrage gruppiert anhand der Kombination aus medical_license und aus radio_license:

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS ((medical_license, radio_license))
  ORDER BY 3 DESC NULLS FIRST;

Copy

Diese Abfrage erzeugt Zeilen, in denen jede Kombination von medical_license und radio_license mit der entsprechenden Anzahl angezeigt wird. Im Gegensatz zum vorherigen Beispiel gibt es keine NULL-Werte in der Ausgabe, da die Abfrage nach beiden Spalten zusammen gruppiert, anstatt für jede Spalte separate Gruppierungen zu erstellen.

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

Das nächste Beispiel mach deutlich, was geschieht, wenn einige Spalten NULL-Werte enthalten. Beginnen Sie durch das Hinzufügen von drei neuen Krankenpflegern, die noch keine Amateurfunklizenz haben.

INSERT INTO nurses
    (ID, full_name, medical_license, radio_license)
  VALUES
    (101, 'Lily Vine', 'LVN', NULL),
    (102, 'Larry Vancouver', 'LVN', NULL),
    (172, 'Rhonda Nova', 'RN', NULL)
    ;

Copy

Führen Sie dann die gleiche Abfrage wie zuvor aus:

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license)
  ORDER BY 3 DESC NULLS FIRST;

Copy

Warum gibt es jetzt eine Zeile, die den Wert NULL in beiden Spalten hat? Und wenn alle Werte NULL sind, warum ist das Ergebnis für COUNT(*) gleich 3?

Die Antwort lautet, dass die NULL in der radio_license-Spalte dieser Zeile angezeigt wird, weil drei Pflegekräfte keine Funklizenz haben. (Die Abfrage SELECT DISTINCT radio_license FROM nurses gibt jetzt vier verschiedene Werte zurück: „Technician“, „General“, „Amateur Extra“ und „NULL“).

Der NULL-Wert in der Spalte medical_licenses tritt aus dem gleichen Grund auf, aus dem auch NULL-Werte in den früheren Abfrageergebnissen vorkommen: Die in dieser Zeile gezählten Pflegekräfte haben unterschiedliche medizinische Zulassungen, sodass kein einziger Wert (RN oder LVN) unbedingt für alle in dieser Zeile gezählten Pflegekräfte Anwendung findet.

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | RN              | NULL          |
|        5 | LVN             | NULL          |
|        3 | NULL            | NULL          |
|        2 | NULL            | Technician    |
|        1 | NULL            | General       |
|        1 | NULL            | Amateur Extra |
+----------+-----------------+---------------+

Das folgende Beispiel veranschaulicht die Kombination von regulären GROUP BY-Spalten mit GROUPING SETS. Diese Abfrage gruppiert nach medical_license und erstellt innerhalb jeder Gruppe medizinischer Zulassungen separate Aggregationen für jeden einzelnen radio_license-Wert und für alle Funklizenzen zusammen:

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY medical_license, GROUPING SETS (radio_license, ())
  ORDER BY 3 DESC NULLS FIRST;

Copy

Für jede medizinische Zulassung (LVN und RN) zeigt die Ausgabe Folgendes an:

Zeilen gruppiert nach den einzelnen radio_license-Werten (Technician, General, Amateur Extra oder NULL für Personen ohne eine Funklizenz)
Eine Zusammenfassungszeile mit NULL in der radio_license-Spalte, in der alle Pflegekräfte mit dieser medizinischen Zulassung aufgeführt sind, unabhängig von ihrer Funklizenz

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | RN              | NULL          |
|        5 | LVN             | NULL          |
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

Sie können diese Ausgabe mit der Ausgabe einer GROUP BY-Abfrage ohne die GROUPING SETS-Klausel vergleichen:

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY medical_license, radio_license
  ORDER BY 3 DESC NULLS FIRST;

Copy

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

Verwenden der Funktion GROUPING¶

Die Dienstprogrammfunktion GROUPING hilft zu ermitteln, welche Aggregationsebene jede Zeile erzeugt hat. Dies ist besonders nützlich, um zwischen NULL-Werten, die sich aus der Gruppierungsoperation ergeben, und tatsächlichen NULL-Werten in den Daten zu unterscheiden.

Die GROUPING-Funktion gibt Folgendes zurück:

0 für eine Zeile, die für die angegebene Spalte gruppiert ist
1 für eine Zeile, die nicht für die angegebene Spalte gruppiert ist (wobei NULL aufgrund der Aggregation angezeigt wird)

Dieses Beispiel fügt GROUPING-Funktionen zur Abfrage hinzu, um die Ausgabe zu verdeutlichen:

SELECT
    COUNT(*),
    medical_license,
    radio_license,
    GROUPING(medical_license) AS grp_medical,
    GROUPING(radio_license) AS grp_radio
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license);

Copy

Die Spalten grp_medical und grp_radio zeigen an, welche Spalten für die Gruppierung verwendet wurden:

Zeilen 1–2: Gruppiert nach medical_license (grp_medical=0), nicht nach radio_license (grp_radio=1)
Zeilen 3–6: Gruppiert nach radio_license (grp_radio=0), nicht nach medical_license (grp_medical=1)
Zeile 6: Der NULL-Wert in radio_license steht für tatsächliche Daten (grp_radio=0), während der NULL-Wert in medical_license aus der Aggregation stammt (grp_medical=1)

+----------+-----------------+---------------+-------------+-----------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE | GRP_MEDICAL | GRP_RADIO |
|----------+-----------------+---------------+-------------+-----------|
|        2 | RN              | NULL          |           0 |         1 |
|        5 | LVN             | NULL          |           0 |         1 |
|        2 | NULL            | Technician    |           1 |         0 |
|        1 | NULL            | General       |           1 |         0 |
|        3 | NULL            | NULL          |           1 |         0 |
|        1 | NULL            | Amateur Extra |           1 |         0 |
+----------+-----------------+---------------+-------------+-----------+