ROLLUP en SQL: subtotales jerarquicos y un total general en una sola consulta

ROLLUP es una extension de GROUP BY que, en una sola consulta, calcula no solo los grupos normales sino tambien subtotales sobre prefijos de columnas mas una fila de total general. Es un motor listo para informes del estilo "ingresos por mes y region, con subtotales y una suma final".

Que calcula ROLLUP

GROUP BY ROLLUP (a, b) agrega en varios niveles, descartando columnas de derecha a izquierda: primero (a, b), luego (a), luego () el conjunto vacio, es decir todo el conjunto de datos. Con dos columnas obtienes las filas de detalle, subtotales por a y una unica fila de total general.

SELECT
  country,
  status,
  SUM(amount) AS revenue
FROM orders
GROUP BY ROLLUP (country, status)
ORDER BY country, status;

Ademas de los pares normales (country, status), el resultado anade:

• una fila por pais donde status es NULL: ese es el subtotal por pais;
• una fila donde tanto country como status son NULL: el total general de todos los pedidos.

El orden de las columnas importa: ROLLUP (country, status) y ROLLUP (status, country) producen subtotales distintos, porque la jerarquia se colapsa de derecha a izquierda.

Leer los NULL finales con GROUPING()

El tropiezo principal: las filas de subtotal se marcan con NULL en las columnas colapsadas. Pero si tus datos ya contienen NULL (por ejemplo, un pedido sin pais), no puedes distinguir un NULL real de una marca de subtotal. La funcion GROUPING() lo resuelve: devuelve 1 cuando una columna se agrego en esa fila, y 0 cuando contiene un valor real.

SELECT
  GROUPING(country)             AS is_total_country,
  GROUPING(status)              AS is_total_status,
  country,
  status,
  SUM(amount)                   AS revenue
FROM orders
GROUP BY ROLLUP (country, status)
ORDER BY GROUPING(country), country, GROUPING(status), status;

Es mas limpio cambiar las banderas por etiquetas legibles desde el principio, para que el informe no necesite notas al pie:

SELECT
  CASE WHEN GROUPING(country) = 1 THEN 'All countries' ELSE country END AS country,
  CASE WHEN GROUPING(status)  = 1 THEN 'All statuses'  ELSE status  END AS status,
  SUM(amount) AS revenue
FROM orders
GROUP BY ROLLUP (country, status)
ORDER BY country NULLS LAST, status NULLS LAST;

Un informe de detalle por mes y region

Un informe de gestion clasico son los ingresos desglosados por mes y region, mostrando el detalle, el subtotal por mes y el total general. Aqui ROLLUP combina muy bien con DATE_TRUNC.

SELECT
  CASE WHEN GROUPING(DATE_TRUNC('month', o.created_at)) = 1
       THEN 'All months'
       ELSE TO_CHAR(DATE_TRUNC('month', o.created_at), 'YYYY-MM') END AS month,
  COALESCE(u.country, 'All regions') AS region,
  SUM(o.amount)                      AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY ROLLUP (DATE_TRUNC('month', o.created_at), u.country)
ORDER BY DATE_TRUNC('month', o.created_at) NULLS LAST, u.country NULLS LAST;

Cada mes recibe sus filas por region, luego un subtotal del mes (region = 'All regions'), y al final del todo All months con los ingresos completos. Ordenar con NULLS LAST garantiza que los subtotales caigan debajo de sus filas de detalle y no encima.

Relacion con GROUPING SETS

ROLLUP es solo una forma abreviada de GROUPING SETS. La consulta GROUP BY ROLLUP (a, b) es estrictamente equivalente a:

GROUP BY GROUPING SETS ((a, b), (a), ());

Saber esto te da flexibilidad:

• quieres CUBE? Eso es GROUPING SETS sobre todas las combinaciones: (a, b), (a), (b), ();
• quieres solo detalle y un total general, sin subtotales intermedios? Escribe GROUPING SETS ((a, b), ()) a mano;
• puedes combinar varios ROLLUP y columnas normales dentro de un mismo GROUP BY.

Sobre dialectos: ROLLUP, CUBE y GROUPING SETS se soportan en PostgreSQL 9.5+. MySQL solo ofrece GROUP BY ... WITH ROLLUP (sin CUBE ni GROUPING SETS explicito), y ORDER BY junto con WITH ROLLUP es delicado. ClickHouse tiene los modificadores WITH ROLLUP, WITH CUBE y WITH TOTALS, pero su sintaxis y el orden de las filas difieren del estandar. Si portas un informe entre motores, revisa especificamente las filas de totales.