Предложение GROUP BY

Статья
04/18/2024

Область применения: флажок Databricks SQL Databricks Runtime

Предложение GROUP BY используется для группирования строк на основе набора определенных выражений группирования и вычисления агрегатов для группы строк на основе одной или нескольких заданных агрегатных функций. Databricks SQL также поддерживает расширенные агрегаты для выполнения нескольких задач агрегирования с одним и тем же набором входных записей с помощью предложений GROUPING SETS, CUBE, ROLLUP. Выражения группирования и расширенные агрегаты можно комбинировать в предложении GROUP BY и вложить в предложение GROUPING SETS.

Дополнительные сведения см. в разделе "Аналитика смешанного или вложенного группирования".

Если предложение FILTER вложено в агрегатную функцию, в эту функцию передаются только соответствующие строки.

Синтаксис

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

При этом агрегатные функции определяются таким образом:

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

Параметры

ALL

Область применения: Databricks SQL Databricks Runtime 12.2 LTS и выше

Сокращенная нотация для добавления всех SELECTвыражений списка, не содержащих агрегатные функции как group_expressions. Если такого выражения GROUP BY ALL нет, это эквивалентно опущению GROUP BY предложения, которое приводит к глобальной агрегации.

GROUP BY ALL не гарантируется создание набора выражений группы, которые можно разрешить. Azure Databricks вызывает UNRESOLVED_ALL_IN_GROUP_BY или MISSING_AGGREGATION , если созданное предложение не является хорошо сформированным.
group_expression

Задает критерии группировки строк. Группирование строк выполняется на основе результирующих значений выражений группирования. Выражение группирования может быть именем столбца, например GROUP BY a, позицией столбца, например GROUP BY 0, или таким выражением: GROUP BY a + b. Если group_expression содержит агрегатную функцию Azure Databricks, возникает ошибка GROUP_BY_AGGREGATE .
grouping_set

Для определения набора группирования указывается ноль или несколько выражений, разделенных запятыми, в круглых скобках. Если в наборе группирования присутствует только один элемент, скобки можно опустить. Например, GROUPING SETS ((a), (b)) — это тоже самое, что и GROUPING SETS (a, b).
ГРУППИРОВАНИЕ НАБОРОВ

Группирует строки для каждого набора группирования, указанного после GROUPING SETS. Например:

GROUP BY GROUPING SETS ((warehouse), (product)) семантически эквивалентно объединению результатов GROUP BY warehouse и GROUP BY product.

Это предложение является сокращением UNION ALL, где каждая ветвь оператора UNION ALL выполняет агрегирование каждого набора группирования, определенного в предложении GROUPING SETS.

Аналогично, GROUP BY GROUPING SETS ((warehouse, product), (product), ()) семантически эквивалентно объединению результатов GROUP BY warehouse, product, GROUP BY product и глобального агрегата.

Примечание.

Для обеспечения совместимости с Hive в Databricks SQL можно использовать GROUP BY ... GROUPING SETS (...). Выражения GROUP BY обычно игнорируются, но если они содержат дополнительные выражения, помимо выражений GROUPING SETS, то эти дополнительные выражения будут включены в выражения группирования, а значение будет всегда равно NULL. Например, для SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b) выходные данные столбца "c" всегда будут иметь значение NULL.

СВЕРТКА

Задает несколько уровней агрегирования в одной инструкции. Это предложение используется для вычисления агрегатов на основе нескольких наборов группирования. ROLLUP является сокращением для GROUPING SETS. Например:

GROUP BY warehouse, product WITH ROLLUP или GROUP BY ROLLUP(warehouse, product) эквивалентны

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

Аналогично, GROUP BY ROLLUP(warehouse, product, (warehouse, location))

эквивалентно GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse), ()).

Элементы N спецификации ROLLUP приводят к такому результату: N+1 GROUPING SETS.
КУБ

Предложение CUBE используется для выполнения агрегирования на основе сочетания столбцов группирования, указанных в предложении GROUP BY. CUBE является сокращением для GROUPING SETS. Например:

GROUP BY warehouse, product WITH CUBE или GROUP BY CUBE(warehouse, product) эквивалентны

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

GROUP BY CUBE(warehouse, product, (warehouse, location)) эквивалентен следующему:
```
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, warehouse, location), (warehouse), (product), (warehouse, product), ())
```
Элементы N спецификации CUBE приводят к такому результату: 2^N GROUPING SETS.
aggregate_name

Имя агрегатной функции (MIN, MAX, COUNT, SUM, AVG и так далее).
DISTINCT

Удаляет дубликаты во входных строках перед их передачей в агрегатные функции.
ФИЛЬТР

Фильтрует входные строки, для которых boolean_expression в предложении WHERE равно true, и передает их в агрегатную функцию, а другие строки отбрасывает.

Аналитика смешанного или вложенного группирования

Предложение GROUP BY может включать несколько group_expression и несколько CUBE, ROLLUP и GROUPING SETS.

GROUPING SETS также может иметь вложенные предложения CUBE, ROLLUP или GROUPING SETS. Например:

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

CUBE и ROLLUP — это синтаксическое сокращение для GROUPING SETS. Сведения о преобразовании CUBE и ROLLUP в GROUPING SETS см. в разделах выше. В этом контексте group_expression может рассматриваться как одна группа GROUPING SETS.

Для нескольких GROUPING SETS в предложении GROUP BY Databricks SQL создает одно выражение GROUPING SETS, вычисляя перекрестное произведение исходного выражения GROUPING SETS.

Для вложенного GROUPING SETS GROUPING SETS в предложение Databricks SQL принимает наборы группирования и удаляет их. Например, следующие запросы:

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

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

эквивалентны следующему:

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

Аналогично, GROUP BY GROUPING SETS(GROUPING SETS(warehouse), GROUPING SETS((warehouse, product)))

эквивалентно GROUP BY GROUPING SETS((warehouse), (warehouse, product)).

Примеры

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

Поделиться через

Предложение GROUP BY

Синтаксис

Параметры

Аналитика смешанного или вложенного группирования

Примеры

Обратная связь

Дополнительные ресурсы

Поделиться через

Предложение GROUP BY

Синтаксис

Параметры

Аналитика смешанного или вложенного группирования

Примеры

Связанные статьи

Обратная связь

Дополнительные ресурсы