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.

  • The output typically contains some NULL values. Because GROUP BY ROLLUP merges the results of two or more result sets, each of which was grouped by different criteria, some columns that have a single value in one result set might have many corresponding values in the other result set. For example, if you do a UNION of a set of employees grouped by department with a set grouped by seniority, the members of the set with the greatest seniority are not necessarily all in the same department, so the value of department_name is set to NULL. The examples below include examples that contain NULLs for this reason.

Beispiele

These examples use a table of information about nurses who are trained to assist in disasters. All of these nurses have a license as nurses (e.g. an RN has a license as a „Registered Nurse“), and an additional license in a disaster-related specialty, such as search and rescue, radio communications, etc. This example simplifies and uses just two categories of licenses:

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

The next three rows show the number of nurses with each type of ham radio license („Technician“, „General“, and „Amateur Extra“). The NULL value for MEDICAL_LICENSE in each of those three rows is deliberate because no single medical license necessarily applies to all members of each of those rows.

+----------+-----------------+---------------+
| 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          |
+----------+-----------------+---------------+
Zurück zum Anfang