klauzule GROUP BY

Článek
12/27/2024

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

Klauzule GROUP BY se používá k seskupení řádků na základě set zadaných výrazů seskupení a k výpočtu agregací na této skupině řádků pomocí 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 stejný vstupní záznam set prostřednictvím klauzulí GROUPING SETS, CUBE, ROLLUP. 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 ) ]

Parameters

ALL

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

Zkrácený zápis pro přidání všech SELECT–list výrazů neobsahujících 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 poskytne set skupinových výrazů, 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ýsledku values výrazů seskupení. Výraz seskupení může být název column, například GROUP BY a, column pozici, například GROUP BY 0, nebo výraz jako GROUP BY a + b. Pokud group_expression obsahuje agregační funkci Azure Databricks, vyvolá GROUP_BY_AGGREGATE chybu.
grouping_set

Skupina set je určena jedním nebo více výrazy oddělenými čárkami v závorkách. Pokud set seskupení obsahuje pouze jeden prvek, je možné vynechat závorky. Například trasa GROUPING SETS ((a), (b)) je stejná jako GROUPING SETS (a, b).
SADY SESKUPENÍ

Seskupí řádky pro každé seskupení set uvedené po 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 ALLwhere, kde každá část operátoru UNION ALL provádí agregaci každého set seskupení zadaného v klauzuli GROUPING SETS.

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 ve SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b)je výstup column c vždy null.

KUMULATIVNÍ AKTUALIZACE

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

KRYCHLE

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

GROUP BY warehouse, product WITH CUBE

nebo

GROUP BY CUBE(warehouse, product)

odpovídá:

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

Zatímco

GROUP BY CUBE(warehouse, product, location)

odpovídá následujícímu:

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

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.).
ZŘETELNÝ

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í

Klauzule GROUP BY 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 CUBE překladu a překladu ROLLUPGROUPING 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

Sdílet prostřednictvím

klauzule GROUP BY

Syntaxe

Parameters

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

Příklady

Váš názor

Další materiály

Sdílet prostřednictvím

klauzule GROUP BY

Syntaxe

Parameters

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

Příklady

Související články

Váš názor

Další materiály