Cláusula GROUP BY

Artículo
04/18/2024

Se aplica a: casilla marcada como Sí Databricks SQL Databricks Runtime

La cláusula GROUP BY se usa para agrupar las filas en función de un conjunto de expresiones de agrupación y agregaciones de proceso especificadas en el grupo de filas basado en una o varias funciones de agregado especificadas. Databricks SQL también admite agregaciones avanzadas para realizar varias agregaciones para el mismo conjunto de registros de entrada a través de las cláusulas GROUPING SETS, CUBE y ROLLUP. Las expresiones de agrupación y las agregaciones avanzadas se pueden mezclar en la cláusula GROUP BY y anidar en una cláusula GROUPING SETS.

Consulte más detalles en la sección Análisis de agrupación mixta o anidada.

Cuando se adjunta una cláusula FILTER a una función de agregado, solo se pasan las filas coincidentes a esa función.

Sintaxis

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

Mientras que las funciones de agregado se definen como

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

Parámetros

ALL

Se aplica a: Databricks SQL Databricks Runtime 12.2 LTS y versiones posteriores

Notación abreviada para agregar todas las SELECTexpresiones -list que no contienen funciones de agregado como group_expressions. Si no existe dicha expresión GROUP BY ALL es equivalente a omitir la cláusula GROUP BY que da como resultado una agregación global.

GROUP BY ALL no se garantiza que genere un conjunto de expresiones de grupo que se puedan resolver. Azure Databricks genera UNRESOLVED_ALL_IN_GROUP_BY o MISSING_AGGREGATION si la cláusula generada no tiene el formato correcto.
group_expression

Especifica los criterios para agrupar filas. La agrupación de filas se realiza en función de los valores de resultado de las expresiones de agrupación. Una expresión de agrupación puede ser un nombre de columna como GROUP BY a, una posición de columna como GROUP BY 0 o una expresión como GROUP BY a + b. Si group_expression contiene una función de agregado, Azure Databricks genera un error GROUP_BY_AGGREGATE.
grouping_set

Un conjunto de agrupación se especifica mediante cero o más expresiones separadas por comas entre paréntesis. Cuando el conjunto de agrupación tiene solo un elemento, se pueden omitir los paréntesis. Por ejemplo, GROUPING SETS ((a), (b)) es igual que GROUPING SETS (a, b).
CONJUNTOS GROUPING

Agrupa las filas de cada conjunto de agrupación especificado después de GROUPING SETS. Por ejemplo:

GROUP BY GROUPING SETS ((warehouse), (product)) es semánticamente equivalente a una unión de resultados de GROUP BY warehouse y GROUP BY product.

Esta cláusula es una abreviatura de UNION ALL, donde cada uno de los segmentos del operador UNION ALL realiza la agregación de cada conjunto de agrupación especificado en la cláusula GROUPING SETS.

De forma similar, GROUP BY GROUPING SETS ((warehouse, product), (product), ()) es semánticamente equivalente a la unión de resultados de GROUP BY warehouse, product, GROUP BY product y un agregado global.

Nota:

Para que sea compatible con Hive, Databricks SQL permite GROUP BY ... GROUPING SETS (...). Las expresiones GROUP BY normalmente se omiten, pero si contienen expresiones adicionales además de las expresiones GROUPING SETS, las expresiones adicionales se incluirán en las expresiones de agrupación y el valor siempre es NULL. Por ejemplo, SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b): la salida de la columna c siempre es NULL.

ACUMULACIÓN

Especifica varios niveles de agregaciones en una sola instrucción. Esta cláusula se usa para calcular agregaciones basadas en varios conjuntos de agrupación. ROLLUP es la abreviatura de GROUPING SETS. Por ejemplo:

GROUP BY warehouse, product WITH ROLLUP o GROUP BY ROLLUP(warehouse, product) es equivalente a

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

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

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

Los N elementos de una especificación ROLLUP tienen como resultado GROUPING SETS N+1.
CUBE

La cláusula CUBE se usa para realizar agregaciones basadas en una combinación de columnas de agrupación especificadas en la cláusula GROUP BY. CUBE es la abreviatura de GROUPING SETS. Por ejemplo:

GROUP BY warehouse, product WITH CUBE o GROUP BY CUBE(warehouse, product) es equivalente a

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

GROUP BY CUBE(warehouse, product, (warehouse, location)) es equivalente al código siguiente:
```
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, warehouse, location), (warehouse), (product), (warehouse, product), ())
```
Los N elementos de una especificación CUBE tienen como resultado GROUPING SETS 2^N.
aggregate_name

Un nombre de función de agregado (MIN, MAX, COUNT, SUM, AVG, etc.).
DISTINCT

Quita los duplicados de las filas de entrada antes de que pasen a las funciones de agregado.
FILTRO

Filtra las filas de entrada que boolean_expression en la cláusula WHERE evalúa como true y pasan a la función de agregado, mientras se descartan otras filas.

Análisis de agrupación anidada o mezclada

Una cláusula GROUP BY puede contener varios elementos group_expressions y varios CUBE, ROLLUP y GROUPING SETS.

GROUPING SETS también puede tener cláusulas CUBE, ROLLUP o GROUPING SETS anidadas. Por ejemplo:

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

CUBE y ROLLUP es azúcar sintáctico de GROUPING SETS. Consulte las secciones anteriores para obtener información sobre cómo traducir CUBE y ROLLUP a GROUPING SETS. group_expression puede tratarse como un GROUPING SETS de un único grupo en este contexto.

Para varios GROUPING SETS en la cláusula GROUP BY, Databricks SQL genera un único GROUPING SETS al realizar un producto cruzado del GROUPING SETS original.

Para GROUPING SETS anidados en la cláusula GROUPING SETS, Databricks SQL toma los conjuntos de agrupación y los elimina. Por ejemplo, estas consultas...:

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

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

...son equivalentes a lo siguiente:

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

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

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

Ejemplos

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

Sintaxis

Parámetros

Análisis de agrupación anidada o mezclada

Ejemplos

Artículos relacionados

Recursos adicionales