Cláusula GROUP BY

Artigo
04/18/2024

Aplica-se a: Marque Sim Databricks SQL Databricks Runtime

A GROUP BY cláusula é usada para agrupar as linhas com base em um conjunto de expressões de agrupamento especificadas e computar agregações no grupo de linhas com base em uma ou mais funções agregadas especificadas. O Databricks SQL também suporta agregações avançadas para fazer várias agregações para o mesmo conjunto de registros de entrada por meio de GROUPING SETScláusulas , CUBEROLLUP . As expressões de agrupamento e agregações avançadas podem ser misturadas na GROUP BY cláusula e aninhadas em uma GROUPING SETS cláusula.

Veja mais detalhes na seção Análise de agrupamento misto/aninhado.

Quando uma FILTER cláusula é anexada a uma função agregada, apenas as linhas correspondentes são passadas para essa função.

Sintaxe

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

Enquanto as funções agregadas são definidas como

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

Parâmetros

TODOS

Aplica-se a: Databricks SQL Databricks Runtime 12.2 LTS e superior

Uma notação abreviada para adicionar todas as SELECTexpressões -list que não contêm funções agregadas como group_expressions. Se tal expressão não existir GROUP BY ALL , equivale a omitir a GROUP BY cláusula que resulta numa agregação global.

GROUP BY ALL não é garantido que produza um conjunto de expressões de grupo que possam ser resolvidas. O Azure Databricks gera UNRESOLVED_ALL_IN_GROUP_BY ou MISSING_AGGREGATION se a cláusula produzida não estiver bem formada.
group_expression

Especifica os critérios para agrupar linhas. O agrupamento de linhas é realizado com base nos valores de resultado das expressões de agrupamento. Uma expressão de agrupamento pode ser um nome de coluna como GROUP BY a, posição da coluna como GROUP BY 0, ou uma expressão como GROUP BY a + b. Se group_expression contiver uma função agregada, o Azure Databricks gerará um erro GROUP_BY_AGGREGATE .
grouping_set

Um conjunto de agrupamento é especificado por zero ou mais expressões separadas por vírgulas entre parênteses. Quando o conjunto de agrupamento tem apenas um elemento, os parênteses podem ser omitidos. Por exemplo, GROUPING SETS ((a), (b)) é o mesmo que GROUPING SETS (a, b).
CONJUNTOS DE AGRUPAMENTO

Agrupa as linhas para cada conjunto de agrupamentos especificado após GROUPING SETS. Por exemplo:

GROUP BY GROUPING SETS ((warehouse), (product)) é semanticamente equivalente a uma união de resultados de GROUP BY warehouse e GROUP BY product.

Esta cláusula é uma abreviatura de a UNION ALL onde cada perna do operador executa a UNION ALL agregação de cada conjunto de agrupamento especificado na GROUPING SETS cláusula.

Da mesma forma, GROUP BY GROUPING SETS ((warehouse, product), (product), ()) é semanticamente equivalente à união de resultados de GROUP BY warehouse, product, GROUP BY product e um agregado global.

Nota

Para compatibilidade com o Hive, o Databricks SQL permite GROUP BY ... GROUPING SETS (...): . As GROUP BY expressões geralmente são ignoradas, mas se contiverem expressões extras além das GROUPING SETS expressões, as expressões extras serão incluídas nas expressões de agrupamento e o valor será sempre nulo. Por exemplo, SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b), a saída da coluna c é sempre nula.

ROLLUP

Especifica vários níveis de agregações em uma única instrução. Esta cláusula é usada para calcular agregações com base em vários conjuntos de agrupamento. ROLLUP é uma abreviatura de GROUPING SETS. Por exemplo:

GROUP BY warehouse, product WITH ROLLUP ou GROUP BY ROLLUP(warehouse, product) é equivalente a

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

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

é equivalente a GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse), ()).

Os elementos N de uma ROLLUP especificação resultam em N+1 GROUPING SETS.
CUBO

A CUBE cláusula é usada para executar agregações com base em uma combinação de colunas de agrupamento especificadas na GROUP BY cláusula. CUBE é uma abreviatura de GROUPING SETS. Por exemplo:

GROUP BY warehouse, product WITH CUBE ou GROUP BY CUBE(warehouse, product) é equivalente a

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

GROUP BY CUBE(warehouse, product, (warehouse, location)) é equivalente ao seguinte:
```
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, warehouse, location), (warehouse), (product), (warehouse, product), ())
```
Os elementos N de uma CUBE especificação resultam em 2^N GROUPING SETS.
aggregate_name

Um nome de função agregado (MIN, MAX, COUNT, SUM, AVG, etc.).
DISTINTOS

Remove duplicatas em linhas de entrada antes que elas sejam passadas para funções agregadas.
FILTRAR

Filtra as linhas de entrada para as quais a boolean_expressionWHERE cláusula na avaliação como true são passadas para a função agregada, outras linhas são descartadas.

Análise de agrupamento misto/aninhado

Uma GROUP BY cláusula pode incluir vários group_expressions e múltiplos CUBE, ROLLUPe GROUPING SETSs.

GROUPING SETS também pode ter aninhado CUBE, ROLLUP, ou GROUPING SETS cláusulas. Por exemplo:

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

CUBE e ROLLUP é apenas açúcar de sintaxe para GROUPING SETS. Consulte as seções acima para saber como traduzir CUBE e ROLLUP para GROUPING SETS. group_expression podem ser tratados como um grupo GROUPING SETS único neste contexto.

Para múltiplos GROUPING SETS na cláusula, o GROUP BY Databricks SQL gera um único GROUPING SETS fazendo um produto cruzado do original GROUPING SETS.

Para aninhado GROUPING SETS na cláusula, o GROUPING SETS Databricks SQL pega seus conjuntos de agrupamento e os remove. Por exemplo, as seguintes consultas:

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

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

são equivalentes ao seguinte:

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

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

é equivalente a GROUP BY GROUPING SETS((warehouse), (warehouse, product)).

Exemplos

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

Cláusula GROUP BY

Sintaxe

Parâmetros

Análise de agrupamento misto/aninhado

Exemplos

Artigos relacionados

Recursos adicionais