Cláusula GROUP BY

La cláusula 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 en función de una o varias funciones de GROUP BY agregado especificadas. Databricks Runtime también admite agregaciones avanzadas para realizar varias agregaciones para el mismo conjunto de registros de entrada mediante GROUPING SETSCUBE cláusulas , ROLLUP y . Las expresiones de agrupación y las agregaciones avanzadas se pueden mezclar en la GROUP BY cláusula y anidar en una GROUPING SETS cláusula .

Consulte más detalles en la sección Mixed/Nested Grouping Analytics (Análisis de agrupación mixta o anidada).

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

Sintaxis

GROUP BY group_expression [, ...] [ WITH ROLLUP | WITH CUBE ]

GROUP BY { group_expression | { ROLLUP | CUBE | GROUPING SETS } ( grouping_set [, ...] ) } [, ...]

grouping_set
   { expression |
     ( [ expression [, ...] ] ) }

Aunque las funciones de agregado se definen como

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

Parámetros

  • group_expression

    Especifica los criterios en función de los cuales las filas se agrupan. 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, a , una posición de columna como o una expresión como GROUP BY 0GROUP BY a + b .

  • 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).

  • GROUPING SETS

    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 los resultados GROUP BY warehouse de y GROUP BY product .

    Esta cláusula es una forma abreviada de un objeto donde cada una de las etapas del operador realiza la agregación de cada conjunto de agrupación UNION ALL especificado en la cláusula UNION ALLGROUPING SETS .

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

Nota

Para compatibilidad con Hive Databricks Runtime permite GROUP BY ... GROUPING SETS (...) . Las expresiones normalmente se omiten, pero si contienen expresiones adicionales además de las expresiones, las expresiones adicionales se incluirán en las expresiones de agrupación y el valor siempre es GROUP BYGROUPING SETS 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.

  • ROLLUP

    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 una 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))

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

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

  • CUBE

    La CUBE cláusula se usa para realizar agregaciones basadas en una combinación de columnas de agrupación especificadas en la cláusula GROUP BY . CUBE es una 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), ()).

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

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

    Los N elementos de una CUBE especificación tienen como resultado 2^N GROUPING SETS .

  • aggregate_name

    Nombre de función de agregado (MIN, MAX, COUNT, SUM, AVG, etc.).

  • DISTINCT

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

  • FILTER

    Filtra las filas de entrada para las que en la cláusula se evalúa como true y se pasan a la función boolean_expressionWHERE de agregado; se descartan otras filas.

###m Mixed/Nested Grouping Analytics

Una GROUP BY cláusula puede incluir varios group_expressions y varios , y CUBEROLLUPGROUPING SETS s.

GROUPING SETS también puede tener CUBEROLLUP cláusulas anidadas , o GROUPING SETS . 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 simplemente el nivel de sintaxis de GROUPING SETS . Consulte las secciones anteriores para obtener información sobre cómo traducir CUBEROLLUP y a GROUPING SETS . group_expression se puede tratar como un único grupo GROUPING SETS en este contexto.

Para varios GROUPING SETS en la cláusula , Databricks Runtime genera un único haciendo un producto cruzado del GROUP BYGROUPING SETSGROUPING SETS original.

Para GROUPING SETS anidados en GROUPING SETS la cláusula , Databricks Runtime simplemente toma sus conjuntos de agrupación y los quita. Por ejemplo:

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

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

son equivalentes a 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)))

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

Ejemplos

CREATE TABLE dealer (id INT, city STRING, car_model STRING, quantity INT);
INSERT INTO dealer 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

-- 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 TABLE person (id INT, name STRING, age INT);
> INSERT INTO person 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