Cláusula GROUP BY

Artigo
04/18/2024

Aplica-se a: marca de seleção positiva SQL do Databricks Databricks Runtime

A cláusula GROUP BY é usada para agrupar as linhas com base em um conjunto de expressões de agrupamento especificadas e agregações de computação no grupo de linhas com base em uma ou mais funções de agregação especificadas. O SQL do Databricks também dá suporte a agregações avançadas para realizar várias agregações para o mesmo conjunto de registros de entrada pelas por meio de cláusulas GROUPING SETS, CUBE, ROLLUP. As expressões de agrupamento e agregações avançadas podem ser misturadas na cláusula GROUP BY e aninhadas em uma cláusula GROUPING SETS.

Confira mais detalhes na seção Análise de Agrupamento Misto/Aninhado.

Quando uma cláusula FILTER é anexada a uma função de agregação, somente 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 de agregação são definidas como

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

Parâmetros

ALL

Aplica-se a: SQL do Databricks Databricks Runtime 12.2 LTS e versões posteriores

Uma notação abreviada para adicionar todas as expressões -list SELECTque não contêm funções de agregação como group_expressions. Se essa expressão não existir, GROUP BY ALL será equivalente a omitir a cláusula GROUP BY que resulta em uma agregação global.

Não há garantias de o GROUP BY ALL produzir um conjunto de expressões de grupo que podem 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 é executado 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 de coluna como GROUP BY 0 ou uma expressão como GROUP BY a + b. Se group_expression contiver uma função de agregação, 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).
GROUPING SETS

Agrupa as linhas para cada conjunto de agrupamento 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.

Essa cláusula é uma abreviação para um UNION ALL em que cada segmento do operador UNION ALL executa a agregação de cada conjunto de agrupamento especificado na cláusula GROUPING SETS.

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 uma agregação global.

Observação

Para a compatibilidade do Hive o SQL do Databricks permite GROUP BY ... GROUPING SETS (...). As expressões GROUP BY geralmente são ignoradas, mas se elas contiverem expressões extras além das expressões GROUPING SETS, 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 será sempre nula.

ROLLUP

Especifica vários níveis de agregações em uma única instrução. Essa cláusula é usada para computar agregações com base em vários conjuntos de agrupamentos. ROLLUP é uma abreviação 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 especificação ROLLUP resultam em N+1 GROUPING SETS.
CUBE

A cláusula CUBE é usada para executar agregações com base em uma combinação de colunas de agrupamento especificadas na cláusula GROUP BY. CUBE é uma abreviação 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 especificação CUBE resultam em 2^N GROUPING SETS.
aggregate_name

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

Remove duplicatas nas linhas de entrada antes que elas sejam passadas para funções de agregação.
FILTRAR

Filtra as linhas de entrada para as quais a cláusula boolean_expression na WHERE avalia como true são passadas para a função de agregação. Outras linhas são descartadas.

Análise de agrupamento aninhado/misto

Uma cláusula GROUP BY pode incluir vários group_expressions e vários CUBE, ROLLUP e GROUPING SETSs.

GROUPING SETS também pode ter cláusulas aninhadas CUBE, ROLLUP ou GROUPING SETS. 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 uma "simplificação de sintaxe" para GROUPING SETS. Consulte as seções acima para saber como traduzir CUBE e ROLLUP para GROUPING SETS. group_expression pode ser tratado como um grupo único GROUPING SETS neste contexto.

Para vários GROUPING SETS na cláusula GROUP BY, o SQL do Databricks gera GROUPING SETS únicos criando um produto cruzado do original GROUPING SETS.

Para GROUPING SETS aninhados na cláusula GROUPING SETS, o Databricks SQL usa seus conjuntos de agrupamentos e os extrai. 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

Compartilhar via

Cláusula GROUP BY

Sintaxe

Parâmetros

Análise de agrupamento aninhado/misto

Exemplos

Comentários

Recursos adicionais

Compartilhar via

Cláusula GROUP BY

Sintaxe

Parâmetros

Análise de agrupamento aninhado/misto

Exemplos

Artigos relacionados

Comentários

Recursos adicionais