GROUP BY, klauzula

Artykuł
04/18/2024

Dotyczy: zaznacz pole wyboru oznaczone jako tak Databricks SQL Databricks Runtime

Klauzula GROUP BY służy do grupowania wierszy na podstawie zestawu określonych wyrażeń grupowania i agregacji obliczeniowych w grupie wierszy na podstawie co najmniej jednej określonej funkcji agregującej. Usługa Databricks SQL obsługuje również zaawansowane agregacje w celu wykonywania wielu agregacji dla tego samego zestawu rekordów wejściowych za pośrednictwem GROUPING SETSklauzul , . CUBEROLLUP Wyrażenia grupowania i zaawansowane agregacje można mieszać w klauzuli GROUP BY i zagnieżdżać w klauzuli GROUPING SETS .

Zobacz więcej szczegółów w sekcji Analiza grupowania mieszanego/zagnieżdżonego.

Gdy klauzula FILTER jest dołączona do funkcji agregującej, do tej funkcji są przekazywane tylko pasujące wiersze.

Składnia

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

Funkcje agregujące są definiowane jako

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

Parametry

ALL

Dotyczy: Databricks SQL Databricks Runtime 12.2 LTS i nowsze

Skrócona notacja do dodawania wszystkich SELECTwyrażeń -list, które nie zawierają funkcji agregujących jako group_expressions. Jeśli takie wyrażenie nie istnieje GROUP BY ALL , jest równoważne pomijaniu GROUP BY klauzuli, która powoduje agregację globalną.

GROUP BY ALL Nie ma gwarancji utworzenia zestawu wyrażeń grupy, które można rozpoznać. Usługa Azure Databricks zgłasza UNRESOLVED_ALL_IN_GROUP_BY lub MISSING_AGGREGATION , jeśli utworzona klauzula nie jest dobrze sformułowana.
group_expression

Określa kryteria grupowania wierszy razem. Grupowanie wierszy jest wykonywane na podstawie wartości wyników wyrażeń grupowania. Wyrażenie grupowania może być nazwą kolumny, taką jak GROUP BY a, pozycja kolumny, na przykład GROUP BY 0, lub wyrażenie takie jak GROUP BY a + b. Jeśli group_expression zawiera funkcję agregacji, usługa Azure Databricks zgłasza błąd GROUP_BY_AGGREGATE .
grouping_set

Zestaw grupowania jest określany przez zero lub więcej wyrażeń rozdzielanych przecinkami w nawiasach. Jeśli zestaw grupowania ma tylko jeden element, nawiasy można pominąć. Na przykład kod GROUPING SETS ((a), (b)) jest taki sam jak kod GROUPING SETS (a, b).
ZESTAWY GRUPOWANIA

Grupuje wiersze dla każdego zestawu grupowania określonego po GROUPING SETS. Na przykład:

GROUP BY GROUPING SETS ((warehouse), (product)) jest semantycznie równoważne unii wyników GROUP BY warehouse i GROUP BY product.

Ta klauzula jest skrótem, w UNION ALL którym każda część UNION ALL operatora wykonuje agregację każdego zestawu grupowania określonego w klauzuli GROUPING SETS .

GROUP BY GROUPING SETS ((warehouse, product), (product), ()) Podobnie, jest semantycznie równoważne unii wyników GROUP BY warehouse, product, GROUP BY product i globalnej agregacji.

Uwaga

W przypadku zgodności z usługą Hive usługa Databricks SQL zezwala na usługę GROUP BY ... GROUPING SETS (...). Wyrażenia GROUP BY są zwykle ignorowane, ale jeśli zawierają dodatkowe wyrażenia oprócz GROUPING SETS wyrażeń, dodatkowe wyrażenia zostaną uwzględnione w wyrażeniach grupowania, a wartość jest zawsze równa null. Na przykład SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b)dane wyjściowe kolumny c są zawsze zerowe.

ZESTAWIENIE

Określa wiele poziomów agregacji w jednej instrukcji. Ta klauzula służy do obliczania agregacji na podstawie wielu zestawów grupowania. ROLLUP to skrót dla elementu GROUPING SETS. Na przykład:

GROUP BY warehouse, product WITH ROLLUP lub GROUP BY ROLLUP(warehouse, product) jest równoważne

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

Chwila GROUP BY ROLLUP(warehouse, product, (warehouse, location))

jest odpowiednikiem GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse), ())elementu .

N elementów ROLLUP specyfikacji powoduje N+1 GROUPING SETS.
SZEŚCIAN

Klauzula CUBE służy do wykonywania agregacji na podstawie kombinacji kolumn grupowania określonych w klauzuli GROUP BY . CUBE to skrót dla elementu GROUPING SETS. Na przykład:

GROUP BY warehouse, product WITH CUBE lub GROUP BY CUBE(warehouse, product) jest równoważne

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

GROUP BY CUBE(warehouse, product, (warehouse, location)) jest równoważny z następującymi elementami:
```
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, warehouse, location), (warehouse), (product), (warehouse, product), ())
```
N elementów CUBE specyfikacji powoduje 2^N GROUPING SETS.
aggregate_name

Nazwa funkcji agregującej (MIN, MAX, COUNT, SUM, AVG itp.).
ODMIENNY

Usuwa duplikaty w wierszach wejściowych przed przekazaniem ich do funkcji agregujących.
FILTR

Filtruje wiersze wejściowe, dla których boolean_expression klauzula w WHERE klauzuli zwraca wartość true, są przekazywane do funkcji agregującej; inne wiersze są odrzucane.

Analiza grupowania mieszanego/zagnieżdżonego

Klauzula może GROUP BY zawierać wiele group_expressions i wiele CUBEelementów , ROLLUPi GROUPING SETSs.

GROUPING SETS może również zawierać zagnieżdżone CUBEklauzule , ROLLUPlub GROUPING SETS . Na przykład:

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

CUBE i ROLLUP jest po prostu składnią cukru dla GROUPING SETS. Zapoznaj się z powyższymi sekcjami, aby dowiedzieć się, jak przetłumaczyć CUBE element i ROLLUP na GROUPING SETS. group_expression w tym kontekście może być traktowana jako pojedyncza grupa GROUPING SETS .

W przypadku wielu GROUPING SETS w klauzuli GROUP BY usługa Databricks SQL generuje pojedynczy element GROUPING SETS , wykonując krzyżowy produkt oryginalnego GROUPING SETSelementu .

W przypadku zagnieżdżenia GROUPING SETS w klauzuli GROUPING SETS sql usługi Databricks pobiera zestawy grupowania i usuwa je. Na przykład następujące zapytania:

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

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

są równoważne z następującymi elementami:

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

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

jest odpowiednikiem GROUP BY GROUPING SETS((warehouse), (warehouse, product))elementu .

Przykłady

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

Udostępnij za pośrednictwem

GROUP BY, klauzula

Składnia

Parametry

Analiza grupowania mieszanego/zagnieżdżonego

Przykłady

Opinia

Dodatkowe zasoby

Udostępnij za pośrednictwem

GROUP BY, klauzula

Składnia

Parametry

Analiza grupowania mieszanego/zagnieżdżonego

Przykłady

Powiązane artykuły

Opinia

Dodatkowe zasoby