Klauzule GROUP BY

Článek
04/18/2024

Platí pro: zaškrtnutí označeného ano Databricks SQL Databricks Runtime

Klauzule GROUP BY slouží k seskupení řádků na základě sady zadaných výrazů seskupení a výpočetních agregací na skupině řádků na základě jedné nebo více zadaných agregačních funkcí. Databricks SQL také podporuje pokročilé agregace, které umožňují provádět více agregací pro stejnou vstupní sadu záznamů prostřednictvím GROUPING SETSklauzulí , CUBEROLLUP . Výrazy seskupení a rozšířené agregace se dají v klauzuli kombinovat GROUP BY a vnořit do GROUPING SETS klauzule.

Další podrobnosti najdete v části Analýzy smíšených nebo vnořených seskupení.

FILTER Pokud je klauzule připojená k agregační funkci, předají se této funkci pouze odpovídající řádky.

Syntaxe

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 [, ...] ] ) }

Zatímco agregační funkce jsou definovány jako

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

Parametry

ALL

Platí pro: Databricks SQL Databricks Runtime 12.2 LTS a vyšší

Zkratka pro přidání všech SELECTvýrazů -list, které neobsahují agregační funkce jako group_expressions. Pokud takový výraz neexistuje GROUP BY ALL , je ekvivalentem vynechání GROUP BY klauzule, která má za následek globální agregaci.

GROUP BY ALL není zaručeno, že se vytvoří sada výrazů skupiny, které je možné vyřešit. Azure Databricks vyvolá UNRESOLVED_ALL_IN_GROUP_BY nebo MISSING_AGGREGATION , pokud není vytvořená klauzule správně vytvořená.
group_expression

Určuje kritéria pro seskupení řádků. Seskupení řádků se provádí na základě výsledných hodnot výrazů seskupení. Výraz seskupení může být název sloupce, například GROUP BY aumístění GROUP BY 0sloupce nebo výraz jako GROUP BY a + b. Pokud group_expression obsahuje agregační funkci Azure Databricks, vyvolá GROUP_BY_AGGREGATE chybu.
grouping_set

Sada seskupení je určena nulou nebo více výrazů oddělených čárkami v závorkách. Pokud sada seskupení obsahuje pouze jeden prvek, závorky lze vynechat. Například trasa GROUPING SETS ((a), (b)) je stejná jako GROUPING SETS (a, b).
SADY SESKUPENÍ

Seskupí řádky pro každou sadu seskupení zadanou za GROUPING SETS. Příklad:

GROUP BY GROUPING SETS ((warehouse), (product)) je sémanticky ekvivalentní sjednocení výsledků a GROUP BY warehouseGROUP BY product.

Tato klauzule je zkratka pro UNION ALL místo, kde každá noha UNION ALL operátor provádí agregaci každé sady seskupení specifikované v GROUPING SETS klauzuli.

GROUP BY GROUPING SETS ((warehouse, product), (product), ()) Podobně je séanticky ekvivalentní sjednocení výsledků GROUP BY warehouse, producta GROUP BY product globální agregace.

Poznámka:

Pro kompatibilitu Hive Databricks SQL umožňuje GROUP BY ... GROUPING SETS (...). Výrazy GROUP BY se obvykle ignorují, ale pokud kromě výrazů obsahují další výrazy GROUPING SETS , budou do výrazů se seskupování zahrnuty další výrazy a hodnota je vždy null. Například SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b)výstup sloupce c je vždy null.

KUMULATIVNÍ

Určuje více úrovní agregací v jednom příkazu. Tato klauzule se používá k výpočtu agregací založených na několika sadách seskupení. ROLLUP je zkratka pro GROUPING SETS. Příklad:

GROUP BY warehouse, product WITH ROLLUP nebo GROUP BY ROLLUP(warehouse, product) je ekvivalentní

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

Zatímco GROUP BY ROLLUP(warehouse, product, (warehouse, location))

je ekvivalentní GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse), ()).

N prvků ROLLUP specifikace má za následek N+1 GROUPING SETS.
Datové krychle

Klauzule CUBE slouží k provádění agregací na základě kombinace seskupování sloupců zadaných v GROUP BY klauzuli. CUBE je zkratka pro GROUPING SETS. Příklad:

GROUP BY warehouse, product WITH CUBE nebo GROUP BY CUBE(warehouse, product) je ekvivalentní

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

GROUP BY CUBE(warehouse, product, (warehouse, location)) odpovídá následujícímu:
```
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, warehouse, location), (warehouse), (product), (warehouse, product), ())
```
Výsledkem N prvků CUBE specifikace je hodnota 2^N GROUPING SETS.
aggregate_name

Název agregační funkce (MIN, MAX, COUNT, SUM, AVG atd.).
ODLIŠNÉ

Před předáním agregačním funkcím odebere duplicity ve vstupních řádcích.
FILTR

Filtruje vstupní řádky, pro které boolean_expressionWHERE se klauzule vyhodnotí jako true, jsou předány agregační funkci; ostatní řádky se zahodí.

Analýza smíšeného nebo vnořeného seskupení

GROUP BY Klauzule může obsahovat více group_expressions a více CUBE, ROLLUPa GROUPING SETSs.

GROUPING SETS může mít také vnořené CUBEklauzule , ROLLUPnebo GROUPING SETS klauzule. Příklad:

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

CUBE a ROLLUP je jen syntaxe cukru pro GROUPING SETS. Informace o překladu a ROLLUP překladu a překladu CUBEGROUPING SETSnajdete v oddílech výše. group_expression lze v tomto kontextu považovat za jednu skupinu GROUPING SETS .

Pro více GROUPING SETS v klauzuli GROUP BY Databricks SQL vygeneruje jeden GROUPING SETS provedením křížového produktu původního GROUPING SETS.

Pro vnořené GROUPING SETS v GROUPING SETS klauzuli přebírá Databricks SQL sady seskupení a odstraní je. Například následující dotazy:

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

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

jsou ekvivalentní následujícímu:

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

Zatímco GROUP BY GROUPING SETS(GROUPING SETS(warehouse), GROUPING SETS((warehouse, product)))

je ekvivalentní GROUP BY GROUPING SETS((warehouse), (warehouse, product)).

Příklady

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

Klauzule GROUP BY

Syntaxe

Parametry

Analýza smíšeného nebo vnořeného seskupení

Příklady

Související články

Další materiály