¿Qué es la cláusula SQL GROUPING SETS y cómo se utiliza?

Los CONJUNTOS DE AGRUPACIÓN son grupos, o conjuntos, de columnas por los que se pueden agrupar las filas. En lugar de escribir varias consultas y combinar los resultados con un UNION, puede utilizar simplemente GROUPING SETS.

GROUPING SETS en SQL puede considerarse una extensión de la cláusula GROUP BY. Permite definir múltiples conjuntos de agrupación en la misma consulta.

Veamos su sintaxis y cómo puede ser equivalente a un GROUP BY con múltiples cláusulas UNION ALL.

Sintaxis de los conjuntos de agrupación de SQL

La sintaxis general de GROUPING SETS es la siguiente:

SELECT
    aggregate_function(column_1)
    column_2,
    column_3,
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (column_2, column_3),
        (column_2),
        (column_3),
        ()
);

Se puede ver cómo estamos agrupando por los diferentes conjuntos.

Esta sintaxis es equivalente a la siguiente consulta más larga que utiliza GROUP BY con UNION ALL para combinar los resultados:

SELECT SUM(column_1), column_2, column_3
FROM table_name
GROUP BY
    column_2,
    column_3

UNION ALL

SELECT SUM(column_1), column_2, NULL
FROM table_name
GROUP BY column_2

UNION ALL

SELECT SUM(column_1), NULL, column_3
FROM table_name
GROUP BY column_3

UNION ALL

SELECT SUM(column_1), NULL, NULL
FROM table_name

Si utiliza GROUP BY de esta manera, necesitará varias cláusulas UNION ALL para combinar los datos de diferentes fuentes. UNION ALL también requiere que todos los conjuntos de resultados tengan el mismo número de columnas con tipos de datos compatibles, por lo que deberá ajustar las consultas añadiendo un valor NULL cuando sea necesario.

Aunque la consulta funcione como se espera, tiene dos problemas principales:

• Es larga y poco manejable.
• Puede dar lugar a un problema de rendimiento, porque SQL tiene que escanear la tabla de ventas cada vez.

La cláusula GROUPING SETS resuelve estos problemas. Pero, ¿cómo afecta a la salida en comparación con una cláusula tradicional GROUP BY? Es hora de ver un ejemplo.

Ejemplo de conjuntos de agrupación SQL

Necesitamos algunos datos de ejemplo. Vamos a crear una tabla llamada payments que contiene todos los pagos que nuestra empresa ha recibido en enero, febrero y marzo de los últimos cuatro años, de 2018 a 2021. La tienda exacta donde se produjo el pago se denota en la columna store_id.

Para crear esta tabla, ejecute la siguiente consulta:

CREATE TABLE payment (payment_amount decimal(8,2), payment_date date, store_id int);

INSERT INTO payment
VALUES
(1200.99, '2018-01-18', 1),
(189.23, '2018-02-15', 1),
(33.43, '2018-03-03', 3),
(7382.10, '2019-01-11', 2),
(382.92, '2019-02-18', 1),
(322.34, '2019-03-29', 2),
(2929.14, '2020-01-03', 2),
(499.02, '2020-02-19', 3),
(994.11, '2020-03-14', 1),
(394.93, '2021-01-22', 2),
(3332.23, '2021-02-23', 3),
(9499.49, '2021-03-10', 3),
(3002.43, '2018-02-25', 2),
(100.99, '2019-03-07', 1),
(211.65, '2020-02-02', 1),
(500.73, '2021-01-06', 3);

Puedes ver los datos utilizando esta sencilla cláusula SELECT:

SELECT * FROM payment ORDER BY payment_date;

Al ejecutar esta consulta se obtiene el resultado:

Puedes ver que hay varias entradas para algunos almacenes. Imagina que estamos preparando un informe y queremos ver un total para cada tienda. La función de agregación SUM() puede ayudarnos con esto. También utilizaremos la cláusula GROUP BY para agrupar nuestros resultados por año y tienda.

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY YEAR(payment_date), store_id
ORDER BY YEAR(payment_date), store_id;

Los resultados se agregan por cada combinación única de año y tienda.

Sin embargo, no podemos ver los pagos totales por año: los pagos totales de 2018, 2019, 2020 o 2021. Tampoco podemos ver los totales por tienda, que sería una métrica útil de tener. El uso de GROUPING SETS nos permite ver estos totales.

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment_new
GROUP BY GROUPING SETS (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id;

Vaya, ¡nuestros resultados han cambiado drásticamente! Ahora, sólo vemos los totales generales de cada tienda junto con los totales generales de cada año.

Para las columnas por las que las filas no están agrupadas, se ven los valores de NULL.

Recuerde que puede incluir varias cláusulas GROUP BY en su GROUPING SETS. Aplicando esto a nuestra consulta se obtiene lo siguiente:

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment_new
GROUP BY GROUPING SETS
(
(YEAR(payment_date), store_id),
(YEAR(payment_date)),
(store_id)
)
ORDER BY YEAR(payment_date), store_id;

Antes de terminar este tutorial, debemos mencionar otras dos extensiones de SQL GROUP BY que podrían resultar útiles para su proyecto o escenario particular: ROLLUP y CUBE. Estos temas se tratan con gran detalle en este tema de aprendizaje de SQL avanzado de LearnSQL.esque incluye funciones de ventana, extensiones de GROUP BY y consultas recursivas.

Ejemplo de ROLLUP SQL

De forma similar a GROUPING SETS, puede utilizar la opción ROLLUP en una sola consulta para generar múltiples conjuntos de agrupación.

ROLLUP asume una jerarquía entre las columnas de entrada. Por ejemplo, si las columnas de entrada son

GROUP BY ROLLUP(column_1,column_2)

la jerarquía para esto es column_1 > column_2, y ROLLUP genera los siguientes conjuntos de agrupación:

(column_1, column_2)
(column_1)
()

ROLLUP genera todos los conjuntos de agrupación que tienen sentido en esta jerarquía. Genera una fila de subtotal cada vez que el valor de column_1 cambia; esta es la jerarquía que hemos proporcionado. Por esta razón, a menudo utilizamos ROLLUP para generar subtotales y totales generales en los informes. El orden de las columnas en ROLLUP es muy importante.

Veamos una consulta que utiliza ROLLUP:

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY ROLLUP (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id

El total general se muestra en la parte superior del resultado:

El resto del resultado está estructurado de la siguiente manera. En primer lugar, se muestra el total anual:

A continuación se muestran los totales por tienda y por año:

Como se puede ver, ROLLUP genera una fila de subtotal cada vez que cambia el valor de Payment Year, ya que ésta es la jerarquía que hemos proporcionado. Este ejemplo muestra lo útil que puede ser ROLLUP para la elaboración de informes.

Ejemplo de SQL CUBE

Similar a ROLLUP, CUBE es una extensión de la cláusula GROUP BY. Permite generar subtotales para todas las combinaciones de las columnas de agrupación especificadas en la cláusula GROUP BY.

La cláusula CUBE es como la combinación de GROUPING SETS y ROLLUP. Muestra la salida detallada de ambas.

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY CUBE (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id

La principal diferencia de esta salida con respecto al ejemplo de ROLLUP es que aquí también se muestra el total general de cada tienda.

Aparte de estas filas, todas las filas de este resultado son las mismas que el resultado de ROLLUP.

¡Con esto concluye nuestra comparación de GROUPING SETS, ROLLUP, y CUBE! Puedes encontrar más ejemplos en este artículo sobre la agrupación, el enrollamiento y la cubicación de datos.

Agrupar sus datos eficazmente con las extensiones SQL GROUP BY

Para dominar las extensiones de SQL GROUP BY es necesario practicar. Opciones como GROUPING SETS, ROLLUP, y CUBE le permiten manipular los resultados de sus consultas de diferentes maneras. Saber utilizar estas extensiones de forma eficaz reduce la necesidad de formatear manualmente los datos antes de transmitirlos a los interesados.

Para ampliar sus conocimientos en esta área, considere este curso de extensiones GROUP BY de LearnSQL.es que cubre GROUPING SETS, ROLLUP, y CUBE.