SQL ROLLUP, CUBE y GROUPING SETS

Lección 4.5 · Tiempo de lectura: ~9 min · Tareas de práctica ↓

SQL ROLLUP, CUBE y GROUPING SETS amplían GROUP BY: además de los grupos normales, devuelven en el mismo resultado filas de subtotales y un total general. En esta lección aprenderás en qué se diferencian las tres construcciones, cómo usar WITH ROLLUP en MySQL y cómo la función GROUPING() separa las filas de total de los valores NULL reales.

Un informe típico muestra filas de detalle, subtotales y un total general: ingresos por mes, un total para cada año y el total de todo el período. Con un GROUP BY simple tendrías que ejecutar una consulta por nivel y unir los resultados. ROLLUP, CUBE y GROUPING SETS devuelven todos los niveles en una sola consulta.

Cómo funcionan los niveles de agrupamiento

Un nivel de agrupamiento es el conjunto de columnas por las que se agrupan las filas. Para dos columnas hay cuatro niveles posibles:

En una fila de subtotal, las columnas "acumuladas" contienen NULL.

WITH ROLLUP en MySQL y MariaDB

MySQL y MariaDB admiten ROLLUP con su propia sintaxis: WITH ROLLUP después de la lista del GROUP BY.

SELECT YEAR(payment_date) AS pay_year,
       MONTH(payment_date) AS pay_month,
       SUM(amount) AS revenue
FROM payment
GROUP BY YEAR(payment_date), MONTH(payment_date) WITH ROLLUP;

Resultado: los ingresos de cada mes (de mayo a agosto de 2005 y febrero de 2006), una fila de subtotal para cada año donde pay_month es NULL y una fila de total general donde ambas columnas son NULL.

El orden de las columnas en GROUP BY define la jerarquía: se agregan subtotales para cada valor de la primera columna, y la última columna es la que se "acumula" primero.

Distinguir filas de total con GROUPING()

En una fila de total, las columnas acumuladas son NULL. Pero los datos reales también pueden contener NULL, y entonces no se puede distinguir un subtotal de un grupo de filas sin valor. La función GROUPING(column) resuelve esto: devuelve 1 si la columna se acumuló en esa fila y 0 si el valor viene de los datos.

GROUPING() la admiten MySQL 8.0+, PostgreSQL, SQL Server y Oracle. MariaDB no la admite; allí solo se puede comprobar NULL.

-- MySQL 8.0+
SELECT CASE
           WHEN GROUPING(YEAR(payment_date)) = 1 THEN 'Grand total'
           WHEN GROUPING(MONTH(payment_date)) = 1 THEN 'Year total'
           ELSE 'Month'
       END AS row_type,
       YEAR(payment_date) AS pay_year,
       MONTH(payment_date) AS pay_month,
       SUM(amount) AS revenue
FROM payment
GROUP BY YEAR(payment_date), MONTH(payment_date) WITH ROLLUP;

Resultado: las mismas ocho filas, cada una etiquetada como Month, Year total o Grand total.

ROLLUP, CUBE y GROUPING SETS estándar

PostgreSQL, SQL Server y Oracle admiten la sintaxis estándar de las tres construcciones. Los ejemplos siguientes se ejecutan sobre la base AdventureWorks en SQL Server. Los productos se agrupan por el año de inicio de ventas y por color; algunos productos no tienen color, por lo que Color contiene valores NULL reales.

ROLLUP

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products,
       GROUPING(Color) AS is_year_total
FROM Product
GROUP BY ROLLUP (YEAR(SellStartDate), Color)
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, GROUPING(Color), Color;

Resultado: la cantidad de productos para cada año y color, un subtotal para cada año (is_year_total = 1) y el total general al final. Para 2006 hay dos filas con Color = NULL: la que tiene is_year_total = 0 es un grupo de 18 productos sin color, y la que tiene is_year_total = 1 es el subtotal del año.

CUBE

CUBE agrega totales para cada combinación de columnas, incluidos los totales por color sumando todos los años:

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products
FROM Product
GROUP BY CUBE (YEAR(SellStartDate), Color)
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, GROUPING(Color), Color;

Resultado: todo lo que devuelve ROLLUP, más un total para cada color sumando todos los años.

Con n columnas, CUBE produce 2ⁿ niveles de agrupamiento, así que el resultado crece rápido. Úsalo con pocas dimensiones.

GROUPING SETS

GROUPING SETS devuelve solo los niveles que enumeres. Por ejemplo, totales por año y totales por color, sin filas de detalle:

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products
FROM Product
GROUP BY GROUPING SETS ((YEAR(SellStartDate)), (Color), ())
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, Color;

Resultado: una fila por año, una fila por color y el total general.

ROLLUP y CUBE son atajos: ROLLUP (a, b) equivale a GROUPING SETS ((a, b), (a), ()).

Compatibilidad en las bases de datos

Cuando una construcción no está disponible, combina varias consultas GROUP BY con UNION ALL, que aprenderás en la lección 5.10.

Cuándo usar cada una

• ROLLUP para jerarquías: año → mes, región → ciudad, categoría → producto.
• CUBE para analizar todos los cortes de unas pocas dimensiones independientes.
• GROUPING SETS cuando solo necesitas niveles concretos, por ejemplo dos desgloses independientes en un solo resultado.

Agrega siempre ORDER BY para que las filas de total aparezcan en un lugar predecible, y etiquétalas con GROUPING() y CASE.

Conclusiones clave:

• ROLLUP, CUBE y GROUPING SETS agregan filas de subtotales y de total general al resultado de GROUP BY.
• ROLLUP (a, b) da (a, b), (a), (); CUBE (a, b) además agrega (b).
• GROUPING SETS devuelve exactamente los niveles enumerados.
• GROUPING(column) = 1 marca una columna acumulada y separa las filas de total de los valores NULL reales.
• MySQL y MariaDB admiten solo WITH ROLLUP; MariaDB no tiene GROUPING().

Preguntas frecuentes

¿Cuál es la diferencia entre ROLLUP y CUBE?

ROLLUP crea subtotales jerárquicos de derecha a izquierda: para (year, month) agrega totales por año y un total general. CUBE crea subtotales para todas las combinaciones, así que también agrega totales por mes sumando todos los años.

¿Cómo agrego una fila de total a una consulta con GROUP BY?

En MySQL y MariaDB, agrega WITH ROLLUP después de las columnas del GROUP BY. En PostgreSQL, SQL Server y Oracle, usa GROUP BY ROLLUP (columns), o GROUP BY GROUPING SETS ((columns), ()) para tener solo el detalle y el total general.

¿Por qué aparecen valores NULL en los resultados de ROLLUP?

En las filas de subtotal y total, las columnas acumuladas no tienen un único valor, así que son NULL. Usa GROUPING(column), que devuelve 1 para esas filas, para distinguirlas de los valores NULL reales de los datos.