Lección 4.5: Agregación Avanzada con ROLLUP, CUBE y GROUPING SETS en SQL

A medida que crecen las necesidades de informes, el GROUP BY regular a menudo no es suficiente. Por ejemplo, puede que necesite obtener todo lo siguiente a la vez:

• detalle por estado de pedido y cliente;
• totales intermedios por estado;
• totales por cliente;
• un total general para todo el conjunto de datos.

Puede escribir múltiples consultas y combinarlas con UNION ALL, pero eso es verboso y más difícil de mantener. Para estas tareas, SQL utiliza modificadores de expresión de agrupamiento: ROLLUP, CUBE y GROUPING SETS.

Más precisamente: estos modificadores enriquecen los resultados de las consultas agregadas con filas en un nivel de agregación más alto (subtotales y total general).

Importante: todos los ejemplos prácticos en esta lección utilizan SQL Server (AdventureWorks).

Nota de sintaxis: ROLLUP, CUBE, GROUPING SETS y GROUPING() a continuación se muestran utilizando la sintaxis de SQL Server. En MySQL, la funcionalidad es más limitada y la sintaxis es parcialmente diferente (por ejemplo, WITH ROLLUP se utiliza comúnmente, mientras que CUBE y GROUPING SETS pueden no estar disponibles en su forma clásica).

En esta lección, cubriremos:

• cómo difieren ROLLUP, CUBE y GROUPING SETS;
• cómo se construyen las filas de subtotal y total;
• cómo distinguir las filas de total generadas utilizando GROUPING().

Por Qué Esto Es Importante

La agregación avanzada le ayuda a:

• construir informes de múltiples niveles en una sola consulta;
• reducir la duplicación de SQL;
• producir detalles consistentes, subtotales y totales generales;
• enriquecer la salida a nivel de detalle con filas en niveles de agregación más altos.

Idea Central

Supongamos que tenemos datos de ventas en SalesOrderHeader con dimensiones Status, CustomerID y métrica TotalDue.

El GROUP BY regular devuelve solo un nivel de agrupamiento. Los constructos de agrupamiento extendido devuelven múltiples niveles a la vez.

ROLLUP: Totales Jerárquicos

ROLLUP construye una jerarquía de derecha a izquierda en la lista de columnas.

Sintaxis

GROUP BY ROLLUP (col1, col2, col3)

Niveles generados:

• (col1, col2, col3) - detalle;
• (col1, col2) - subtotal sobre col3;
• (col1) - subtotal sobre col2 y col3;
• () - total general.

Ejemplo: totales de monto de pedido por estado y cliente

SELECT
    Status,
    CustomerID,
    SUM(TotalDue) AS total_amount
FROM SalesOrderHeader
GROUP BY ROLLUP (Status, CustomerID)
ORDER BY Status, CustomerID;

Resultado:

• filas para cada par Status + CustomerID;
• subtotal por Status;
• total general.

CUBE: Todas las Combinaciones de Dimensiones

CUBE construye agregados para todas las combinaciones posibles de las columnas listadas.

Sintaxis

GROUP BY CUBE (col1, col2)

Para dos columnas, los niveles son:

• (col1, col2);
• (col1);
• (col2);
• ().

Para tres columnas, las combinaciones ya son $2^3 = 8$, por lo que el tamaño del resultado puede crecer rápidamente.

Ejemplo: totales de monto de pedido por estado y cliente en todos los cortes

SELECT
    Status,
    CustomerID,
    SUM(TotalDue) AS total_amount
FROM SalesOrderHeader
GROUP BY CUBE (Status, CustomerID)
ORDER BY Status, CustomerID;

Resultado: además del detalle y el total general, también obtienes:

• totales por Status;
• totales por CustomerID.

GROUPING SETS: Control Preciso de Niveles

GROUPING SETS le permite listar explícitamente solo los niveles de agrupamiento que necesita.

Sintaxis

GROUP BY GROUPING SETS (
    (col1, col2),
    (col1),
    ()
)

Ejemplo: solo niveles requeridos, sin combinaciones adicionales

SELECT
    Status,
    CustomerID,
    SUM(TotalDue) AS total_amount
FROM SalesOrderHeader
GROUP BY GROUPING SETS (
    (Status, CustomerID),
    (Status),
    ()
)
ORDER BY Status, CustomerID;

Esto es equivalente a múltiples consultas GROUP BY ... UNION ALL ..., pero es más compacto y generalmente mejor optimizado.

Distinguiendo Filas Totales con GROUPING()

En las filas totales generadas, los valores de dimensión a menudo se convierten en NULL. El problema es que los datos de origen también pueden contener valores NULL reales.

GROUPING(column) ayuda a distinguirlos:

• 0 - valor regular de los datos de origen;
• 1 - valor generado por el nivel de agregación.

Ejemplo con banderas de nivel

SELECT
    Status,
    CustomerID,
    SUM(TotalDue) AS total_amount,
    GROUPING(Status) AS g_status,
    GROUPING(CustomerID) AS g_customer
FROM SalesOrderHeader
GROUP BY ROLLUP (Status, CustomerID)
ORDER BY Status, CustomerID;

Patrón de etiquetado de informe práctico:

CASE
    WHEN GROUPING(Status) = 1 AND GROUPING(CustomerID) = 1 THEN 'TOTAL GENERAL'
    WHEN GROUPING(CustomerID) = 1 THEN 'SUBTOTAL POR ESTADO'
    ELSE 'DETALLE'
END AS row_type

Cuándo Usar Qué

• Use ROLLUP cuando necesite totales jerárquicos (por ejemplo, año -> mes -> día).
• Use CUBE cuando necesite todos los cortes analíticos a través de dimensiones.
• Use GROUPING SETS cuando desee un control estricto sobre exactamente qué niveles se devuelven.

Recomendaciones Prácticas

• Siempre verifique el tamaño del resultado: CUBE puede aumentar significativamente el conteo de filas.
• Etiquete los tipos de fila (DETALLE, SUBTOTAL, TOTAL GENERAL) para mejorar la legibilidad.
• Agregue un ORDER BY explícito para que los totales aparezcan en un orden predecible.
• Si necesita filtrado de agregados, combine con HAVING.

Ejemplo de MySQL

A continuación se muestra un ejemplo de MySQL en la tabla payment utilizando subtotales con WITH ROLLUP:

SELECT
    staff_id,
    customer_id,
    SUM(amount) AS total_amount
FROM
    payment
GROUP BY
    staff_id, customer_id WITH ROLLUP
ORDER BY
    GROUPING(staff_id),
    staff_id,
    GROUPING(customer_id),
    customer_id;

En esta consulta:

• se devuelve el detalle por cada par staff_id + customer_id;
• WITH ROLLUP agrega subtotales por staff_id y un total general;
• ORDER BY GROUPING(...) coloca las filas en un orden conveniente: detalles, subtotales, luego total general.

Notas importantes para MySQL:

• WITH ROLLUP proporciona totales jerárquicos, pero no es un equivalente completo de CUBE/GROUPING SETS.
• Para combinaciones de agrupamiento más complejas, a menudo necesita múltiples consultas con UNION ALL.
• Si su versión de MySQL no admite GROUPING(), el ordenamiento y etiquetado de filas totales generalmente se realiza con verificaciones de NULL.

Uso Práctico

1. Informe de monto de pedido por estado y cliente con totales: ROLLUP (Status, CustomerID) proporciona detalle, subtotales por estado y total general.

2. Análisis de ventas multidimensional: CUBE (Status, CustomerID) proporciona todas las combinaciones de cortes a través de estado y cliente.

3. Informe de monto de pedido personalizado: GROUPING SETS le permite mantener solo los niveles que necesita: detalle + subtotal departamental + total general.

Conclusiones Clave de Esta Lección

• ROLLUP, CUBE y GROUPING SETS extienden el GROUP BY estándar.
• ROLLUP crea totales jerárquicos, CUBE crea todas las combinaciones, GROUPING SETS crea solo los niveles listados explícitamente.
• GROUPING() es esencial para interpretar correctamente las filas totales generadas.
• Estas herramientas ayudan a construir informes analíticos flexibles sobre montos de pedidos en una sola consulta.

Al dominar estos constructos, puede diseñar informes SQL más potentes sin largas cadenas de UNION ALL.