GROUP BY-Klausel

Artikel
04/18/2024

Gilt für: durch Häkchen mit „Ja“ markiert Databricks SQL Databricks Runtime

Die GROUP BY-Klausel wird verwendet, um die Zeilen anhand einer Kombination von angegebenen Gruppierungsausdrücken und Computeaggregationen für die Zeilengruppe basierend auf mindestens einer angegebenen Aggregatfunktion zu gruppieren. Databricks SQL unterstützt auch erweiterte Aggregationen, um mehrere Aggregationen für denselben Eingabedatensatz über die Klauseln GROUPING SETS, CUBE, ROLLUP vorzunehmen. Die Gruppierungsausdrücke und erweiterten Aggregationen können in der GROUP BY-Klausel gemischt und in einer GROUPING SETS-Klausel geschachtelt werden.

Weitere Informationen finden Sie im Abschnitt „Analyse gemischter/geschachtelter Gruppierungen“.

Wenn eine FILTER-Klausel an eine Aggregatfunktion angefügt ist, werden nur die übereinstimmenden Zeilen an diese Funktion übergeben.

Syntax

GROUP BY ALL

GROUP BY group_expression [, ...] [ WITH ROLLUP | WITH CUBE ]

GROUP BY { group_expression | { ROLLUP | CUBE | GROUPING SETS } ( grouping_set [, ...] ) } [, ...]

grouping_set
   { expression |
     ( [ expression [, ...] ] ) }

Dabei werden Aggregatfunktionen definiert als

aggregate_name ( [ DISTINCT ] expression [, ...] ) [ FILTER ( WHERE boolean_expression ) ]

Parameter

ALL

Gilt für: Databricks SQL Databricks Runtime 12.2 LTS und höher

Eine kurze Notation, um alle SELECT-list-Ausdrücke, die keine Aggregatfunktionen enthalten, als group_expression hinzuzufügen. Ist kein derartiger Ausdruck vorhanden, entspricht GROUP BY ALL dem Weglassen der GROUP BY-Klausel und führt zu einer globalen Aggregation.

Es ist nicht garantiert, dass GROUP BY ALL einen Satz von auflösbaren Gruppenausdrücken generiert. Azure Databricks löst UNRESOLVED_ALL_IN_GROUP_BY oder MISSING_AGGREGATION aus, wenn die generierte Klausel nicht wohlgeformt ist.
group_expression

Gibt die Kriterien zum Gruppieren von Zeilen an. Die Gruppierung von Zeilen erfolgt anhand der Ergebniswerte der Gruppierungsausdrücke. Ein Gruppierungsausdruck kann ein Spaltenname wie GROUP BY a, eine Spaltenposition wie GROUP BY 0 oder ein Ausdruck wie GROUP BY a + b sein. Wenn group_expression eine Aggregatfunktion enthält, löst Azure Databricks einen Fehler vom Typ GROUP_BY_AGGREGATE aus.
grouping_set

Ein Gruppierungssatz wird durch einen Null-Wert oder mehrere kommagetrennte Ausdrücke in Klammern angegeben. Wenn der Gruppierungssatz über nur ein Element verfügt, können die Klammern entfallen. Beispielsweise ist GROUPING SETS ((a), (b)) identisch mit GROUPING SETS (a, b).
GROUPING SETS

Gruppiert die Zeilen für jeden nach GROUPING SETSangegebenen Gruppierungssatz. Beispiele:

GROUP BY GROUPING SETS ((warehouse), (product)) entspricht semantisch einer Vereinigung der Ergebnisse von GROUP BY warehouse und GROUP BY product.

Diese Klausel ist eine Kurzform für UNION ALL, bei der jedes Element des UNION ALL-Operators die Aggregation jedes in der GROUPING SETS-Klausel angegebenen Gruppierungssatzes ausführt.

Auf ähnliche Weise entspricht GROUP BY GROUPING SETS ((warehouse, product), (product), ()) semantisch der Vereinigung der Ergebnisse von GROUP BY warehouse, product, GROUP BY product und einem globalen Aggregat.

Hinweis

Für die Hive-Kompatibilität wird GROUP BY ... GROUPING SETS (...) von Databricks SQL erlaubt. Die GROUP BY-Ausdrücke werden in der Regel ignoriert. Wenn sie aber über die GROUPING SETS-Ausdrücke hinaus weitere Ausdrücke enthalten, werden die zusätzlichen Ausdrücke in die Gruppierungsausdrücke eingeschlossen, und der Wert ist immer NULL. Bei SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b) beispielsweise ist die Ausgabe von Spalte „c“ immer NULL.

ROLLUP

Gibt mehrere Aggregationsebenen in einer einzelnen Anweisung an. Diese Klausel wird verwendet, um Aggregationen anhand mehrerer Gruppierungssätze zu berechnen. ROLLUP ist eine Kurzform für GROUPING SETS. Beispiele:

GROUP BY warehouse, product WITH ROLLUP oder GROUP BY ROLLUP(warehouse, product) entspricht

GROUP BY GROUPING SETS((warehouse, product), (warehouse), ()).

Während GROUP BY ROLLUP(warehouse, product, (warehouse, location))

entspricht GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse), ()).

Die N-Elemente einer ROLLUP-Spezifikation ergeben N+1 GROUPING SETS.
CUBE

Die CUBE-Klausel wird verwendet, um Aggregationen anhand einer Kombination von in der GROUP BY-Klausel angegebenen Gruppierungsspalten auszuführen. CUBE ist eine Kurzform für GROUPING SETS. Beispiele:

GROUP BY warehouse, product WITH CUBE oder GROUP BY CUBE(warehouse, product) entspricht

GROUP BY GROUPING SETS((warehouse, product), (warehouse), (product), ()).

GROUP BY CUBE(warehouse, product, (warehouse, location)) ist äquivalent zu:
```
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, warehouse, location), (warehouse), (product), (warehouse, product), ())
```
Die N-Elemente einer CUBE-Spezifikation ergeben 2^N GROUPING SETS.
aggregate_name

Name einer Aggregatfunktion (MIN, MAX, COUNT, SUM, AVG usw.).
DISTINCT

Entfernt Duplikate in Eingabezeilen, bevor sie an Aggregatfunktionen übergeben werden.
FILTER

Filtert die Eingabezeilen, bei denen boolean_expression in der WHERE-Klausel als TRUE ausgewertet wird und die an die Aggregatfunktion übergeben werden. Andere Zeilen werden verworfen.

Analyse gemischter/geschachtelter Gruppierung

Eine GROUP BY-Klausel kann mehrere „group_expressions“-Vorkommen und mehrere CUBE-, ROLLUP- und GROUPING SETS-Klauseln enthalten.

GROUPING SETS kann auch geschachtelte CUBE-, ROLLUP- oder GROUPING SETS-Klauseln enthalten. Beispiele:

GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location)), GROUPING SETS(warehouse, GROUPING SETS(location, GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location))))

CUBE und ROLLUP sind lediglich andere Syntaxformen für GROUPING SETS. Informationen für die Umsetzung von CUBE und ROLLUP in GROUPING SETS finden Sie in den obigen Abschnitten. group_expression kann in diesem Kontext als einzelne Gruppe von GROUPING SETS behandelt werden.

Bei mehreren Vorkommen von GROUPING SETS in der GROUP BY-Klausel generiert Databricks SQL eine einzelne GROUPING SETS-Instanz, indem ein Kreuzprodukt des ursprünglichen GROUPING SETS erstellt wird.

Um GROUPING SETS in der GROUPING SETS-Klausel zu schachteln, nimmt Databricks SQL einfach die Gruppierungssätze und zerlegt sie. Zum Beispiel die folgenden Abfragen:

GROUP BY warehouse, GROUPING SETS((product), ()), GROUPING SETS((location, size), (location), (size), ());

GROUP BY warehouse, ROLLUP(product), CUBE(location, size);

sind äquivalent zu:

GROUP BY GROUPING SETS( (warehouse, product, location, size), (warehouse, product, location), (warehouse, product, size), (warehouse, product), (warehouse, location, size), (warehouse, location), (warehouse, size), (warehouse))

Während GROUP BY GROUPING SETS(GROUPING SETS(warehouse), GROUPING SETS((warehouse, product)))

entspricht GROUP BY GROUPING SETS((warehouse), (warehouse, product)).

Beispiele

CREATE TEMP VIEW dealer (id, city, car_model, quantity) AS
VALUES (100, 'Fremont', 'Honda Civic', 10),
       (100, 'Fremont', 'Honda Accord', 15),
       (100, 'Fremont', 'Honda CRV', 7),
       (200, 'Dublin', 'Honda Civic', 20),
       (200, 'Dublin', 'Honda Accord', 10),
       (200, 'Dublin', 'Honda CRV', 3),
       (300, 'San Jose', 'Honda Civic', 5),
       (300, 'San Jose', 'Honda Accord', 8);

-- Sum of quantity per dealership. Group by `id`.
> SELECT id, sum(quantity) FROM dealer GROUP BY id ORDER BY id;
  id sum(quantity)
 --- -------------
 100            32
 200            33
 300            13

-- Use column position in GROUP by clause.
> SELECT id, sum(quantity) FROM dealer GROUP BY 1 ORDER BY 1;
  id sum(quantity)
 --- -------------
 100            32
 200            33
 300            13

-- Multiple aggregations.
-- 1. Sum of quantity per dealership.
-- 2. Max quantity per dealership.
> SELECT id, sum(quantity) AS sum, max(quantity) AS max
    FROM dealer GROUP BY id ORDER BY id;
  id sum max
 --- --- ---
 100  32  15
 200  33  20
 300  13   8

-- Count the number of distinct dealers in cities per car_model.
> SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY car_model;
    car_model count
 ------------ -----
  Honda Civic     3
    Honda CRV     2
 Honda Accord     3

-- Count the number of distinct dealers in cities per car_model, using GROUP BY ALL
> SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY ALL;
    car_model count
 ------------ -----
  Honda Civic     3
    Honda CRV     2
 Honda Accord     3

-- Sum of only 'Honda Civic' and 'Honda CRV' quantities per dealership.
> SELECT id,
         sum(quantity) FILTER (WHERE car_model IN ('Honda Civic', 'Honda CRV')) AS `sum(quantity)`
    FROM dealer
    GROUP BY id ORDER BY id;
  id sum(quantity)
 --- -------------
 100            17
 200            23
 300             5

-- Aggregations using multiple sets of grouping columns in a single statement.
-- Following performs aggregations based on four sets of grouping columns.
-- 1. city, car_model
-- 2. city
-- 3. car_model
-- 4. Empty grouping set. Returns quantities for all city and car models.
> SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
    ORDER BY city;
      city    car_model sum
 --------- ------------ ---
      null         null  78
      null  HondaAccord  33
      null     HondaCRV  10
      null   HondaCivic  35
    Dublin         null  33
    Dublin  HondaAccord  10
    Dublin     HondaCRV   3
    Dublin   HondaCivic  20
   Fremont         null  32
   Fremont  HondaAccord  15
   Fremont     HondaCRV   7
   Fremont   HondaCivic  10
  San Jose         null  13
  San Jose  HondaAccord   8
  San Jose   HondaCivic   5

-- Group by processing with `ROLLUP` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), ())
> SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY city, car_model WITH ROLLUP
    ORDER BY city, car_model;
      city    car_model sum
 --------- ------------ ---
      null         null  78
    Dublin         null  33
    Dublin  HondaAccord  10
    Dublin     HondaCRV   3
    Dublin   HondaCivic  20
   Fremont         null  32
   Fremont  HondaAccord  15
   Fremont     HondaCRV   7
   Fremont   HondaCivic  10
  San Jose         null  13
  San Jose  HondaAccord   8
  San Jose   HondaCivic   5

-- Group by processing with `CUBE` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
> SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY city, car_model WITH CUBE
    ORDER BY city, car_model;
      city    car_model sum
 --------- ------------ ---
      null         null  78
      null  HondaAccord  33
      null     HondaCRV  10
      null   HondaCivic  35
    Dublin         null  33
    Dublin  HondaAccord  10
    Dublin     HondaCRV   3
    Dublin   HondaCivic  20
   Fremont         null  32
   Fremont  HondaAccord  15
   Fremont     HondaCRV   7
   Fremont   HondaCivic  10
  San Jose         null  13
  San Jose  HondaAccord   8
  San Jose   HondaCivic   5

--Prepare data for ignore nulls example
> CREATE TEMP VIEW person (id, name, age) AS
   VALUES (100, 'Mary', NULL),
          (200, 'John', 30),
          (300, 'Mike', 80),
          (400, 'Dan' , 50);

--Select the first row in column age
> SELECT FIRST(age) FROM person;
  first(age, false)
 --------------------
  NULL

--Get the first row in column `age` ignore nulls,last row in column `id` and sum of column `id`.
> SELECT FIRST(age IGNORE NULLS), LAST(id), SUM(id) FROM person;
  first(age, true)    last(id, false)    sum(id)
 ------------------- ------------------ ----------
  30                  400                1000

Freigeben über

GROUP BY-Klausel

Syntax

Parameter

Analyse gemischter/geschachtelter Gruppierung

Beispiele

Feedback

Zusätzliche Ressourcen

Freigeben über

GROUP BY-Klausel

Syntax

Parameter

Analyse gemischter/geschachtelter Gruppierung

Beispiele

Verwandte Artikel

Feedback

Zusätzliche Ressourcen