Kategorien:

Abfragesyntax

GROUP BY GROUPING SETS

GROUP BY GROUPING SETS ist eine leistungsstarke Erweiterung der GROUP BY-Klausel, mit der sich mehrere group-by-Klauseln in einer einzigen Anweisung berechnen lassen. Der Gruppensatz ist ein Satz von Dimensionsspalten.

GROUP BY GROUPING SETS entspricht einer UNION von zwei oder mehr GROUP BY-Operationen im gleichen Resultset:

  • 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.

Syntax

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

Wobei:

groupSet ::= { <column_alias> | <position> | <expr> }
Spaltenalias

Spaltenalias, der in der Liste SELECT des Abfrageblocks angezeigt wird.

Position

Position eines Ausdrucks in der Liste SELECT.

Ausdruck

Jeder Ausdruck in Tabellen im aktuellen Bereich.

Nutzungshinweise

  • Snowflake erlaubt bis zu 128 Gruppierungssätze im gleichen Abfrageblock.

  • Die Ausgabe enthält typischerweise einige NULL-Werte. Da GROUP BY ROLLUP 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 wir zum Beispiel eine UNION einer Menge von Mitarbeitern, die nach Abteilungen gruppiert ist, mit einer Menge, die nach Dienstalter gruppiert ist, durchführen, gehören die Mitglieder der Menge mit dem höchsten Dienstalter nicht unbedingt alle derselben Abteilung an, sodass der Wert department_name auf NULL gesetzt wird. Folgende Beispiele enthalten Beispiele, die aus diesem Grund NULLs enthalten.

Beispiele

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

  • Krankenpflege: RN (staatlich geprüfter Krankenpfleger) und LVN (ausgebildeter Krankenpfleger mit Linzenz).

  • Amateurfunk: Amateurfunklizenzen beinhalten „Techniker“, „Allgemein“ und „Amateur Extra“.

Hier sind die Befehle zum Erstellen und Laden der 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')
    ;

Diese Abfrage nutzt GROUP BY GROUPING SETS:

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

Ausgabe:

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 („Techniker“, „Allgemein“ 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 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)
    ;

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);

Ausgabe:

Die ersten 5 Zeilen sind die gleichen wie in der vorherigen Abfrage.

Die letzte Zeile mag zunächst verwirren: Warum gibt es eine Zeile, in der beide Spalten NULL-Werte aufweisen? Und wenn alle Werte NULL sind, warum ist die COUNT(*) gleich 3?

Die Antwort lautet, dass die NULL in der RADIO_LICENSE-Spalte dieser Zeile auftritt, weil drei Krankenpfleger keine Funklizenz haben. („SELECT DISTINCT RADIO_LICENSE FROM Krankenpflegern“ gibt nun vier verschiedene Werte zurück: „Techniker“, „Allgemein“, „Amateur Extra“ und „NULL“.)

Die NULL 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 Krankenpfleger haben unterschiedliche MEDICAL_LICENSES, sodass kein einziger Wert („RN“ oder „LVN“) unbedingt für alle in dieser Zeile gezählten Krankenpfleger Anwendung findet.

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
+==========+=================+================+
|        5 |            LVN  | NULL          |
|        2 |            RN   | NULL          |
|        2 |            NULL | TECHNICIAN    |
|        1 |            NULL | GENERAL       |
|        1 |            NULL | AMATEUR EXTRA |
|        3 |            NULL | NULL          |
+----------+-----------------+---------------+

Wenn Sie möchten, können Sie diese Ausgabe mit der Ausgabe einer GROUP BY-Anweisung ohne die Klausel GROUPING SETS vergleichen:

... GROUP BY medical_license, radio_license;

Ausgabe:

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
+==========+=================+===============+
|        2 |            LVN  | NULL          |
|        2 |            LVN  | TECHNICIAN    |
|        1 |            LVN  | GENERAL       |
|        1 |            RN   | AMATEUR EXTRA |
|        1 |            RN   | NULL          |
+----------+-----------------+---------------+