Sdílet prostřednictvím

klauzule GROUP BY

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

Klauzule GROUP BY slouží k seskupení řádků na základě sady zadaných výrazů seskupení a 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 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 ) ]

Parametry

  • ALL

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

    Zkrácený zápis pro přidání všech výrazů SELECT-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 vznikne 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 a, umístění sloupce, například GROUP BY 0, nebo výraz, jako je 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 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 ALL, kde každá část operátoru UNION ALL provádí agregaci každé skupiny setů specifikované 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 sloupce 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, location))

    je to 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 sloupců seskupení zadaný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 to 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
  • Last updated on