Kategorien:

Abfragesyntax

GROUP BY GROUPING SETS

GROUP BY 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. Der Gruppensatz ist ein Satz von Dimensionsspalten.

GROUP BY GROUPING SETS entspricht Verwendung von UNION mit zwei oder mehreren 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 [ , ... ] ] )
[ ... ]
Copy

Wobei:

groupSet ::= { <column_alias> | <position> | <expr> }
Copy
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.

  • 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 zum Beispiel UNION auf einer nach Abteilungen gruppierten Menge von Mitarbeitern und einer nach Dienstalter gruppierten Menge von Mitarbeitern ausgeführt wird, 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.

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 Lizenz).

  • Amateurfunk: Amateurfunklizenzen umfassen „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')
    ;
Copy

Diese Abfrage nutzt GROUP BY GROUPING SETS:

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

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 („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 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);
Copy

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 NULL in der RADIO_LICENSE-Spalte dieser Zeile auftritt, weil drei Krankenpfleger keine Funklizenz haben („SELECT DISTINCT RADIO_LICENSE FROM Krankenpflegern“ gibt nun vier diskrete Werte zurück: „Technician“, „General“, „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:

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

Ausgabe:

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