CUBE en SQL: todas las combinaciones de agrupacion en una pasada para informes cross-tab

CUBE es una extension de GROUP BY que, en una sola pasada, calcula agregados sobre todas las combinaciones de las columnas indicadas: cada dimension por separado, sus intersecciones y el conjunto de datos completo. Es un motor listo para informes cross-tab del estilo "suma por region, por producto, por region+producto y un total general, en una sola tabla".

Que calcula CUBE

El ejemplo canonico: ventas desglosadas por region y producto.

SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product);

GROUP BY CUBE (region, product) construye cuatro agrupaciones a la vez:

• (region, product) — las celdas de detalle del cross-tab;
• (region) — un total por region (cualquier producto);
• (product) — un total por producto (cualquier region);
• () — el conjunto vacio, es decir el total general sobre todas las filas.

Para n columnas, CUBE produce 2^n agrupaciones. Dos columnas dan 4 conjuntos, tres dan 8, cuatro dan 16. Esa es la diferencia clave con ROLLUP, que colapsa columnas solo de derecha a izquierda (una jerarquia, n + 1 niveles), mientras que CUBE toma todos los subconjuntos. CUBE (a, b) produce tanto (a) como (b); ROLLUP (a, b) produce (a) pero no (b).

Leer subtotales NULL con GROUPING()

El tropiezo principal: las filas de subtotal se marcan con NULL en las columnas colapsadas. Si tus datos ya contienen NULL (por ejemplo, una venta sin region), no puedes distinguir un NULL real de una marca de total. La funcion GROUPING() elimina la ambiguedad: devuelve 1 cuando una columna se agrego en esa fila, y 0 cuando contiene un valor real.

SELECT
  GROUPING(country) AS g_country,
  GROUPING(status)  AS g_status,
  country,
  status,
  SUM(amount)       AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY CUBE (country, status)
ORDER BY g_country, country, g_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 o
JOIN users u ON u.id = o.user_id
GROUP BY CUBE (country, status)
ORDER BY country NULLS LAST, status NULLS LAST;

Para elegir un "corte" concreto del cubo, filtra por los bits de GROUPING() en HAVING. Por ejemplo, solo los totales por pais (status colapsado, country no):

SELECT country, SUM(amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY CUBE (country, status)
HAVING GROUPING(country) = 0 AND GROUPING(status) = 1;

CUBE en un ejemplo real de RR. HH.

CUBE encaja de maravilla en informes matriciales. Toma los salarios de empleados desglosados por departamento y por si reportan a un jefe:

SELECT
  COALESCE(dept, 'ALL DEPTS')                       AS dept,
  CASE WHEN GROUPING(manager_id IS NULL) = 1
       THEN 'ALL'
       WHEN manager_id IS NULL THEN 'top-level'
       ELSE 'has manager' END                       AS reports_to,
  COUNT(*)                                           AS headcount,
  ROUND(AVG(salary), 2)                              AS avg_salary
FROM employees
GROUP BY CUBE (dept, (manager_id IS NULL))
ORDER BY dept NULLS LAST;

Una sola consulta devuelve a la vez: el salario medio de cada par "departamento + nivel", de cada departamento completo, de cada nivel en toda la empresa y el total general. Sin CUBE tendrias que coser cuatro SELECT con UNION ALL.

CUBE vs ROLLUP vs GROUPING SETS

Los tres son azucar sintactico sobre GROUPING SETS. Conocer las equivalencias te da control total:

• CUBE (a, b) = GROUPING SETS ((a, b), (a), (b), ()) — todas las combinaciones;
• ROLLUP (a, b) = GROUPING SETS ((a, b), (a), ()) — solo la jerarquia de derecha a izquierda;
• necesitas un conjunto no estandar? Escribelo a mano: GROUPING SETS ((a, b), (b), ()).

Usa ROLLUP cuando las columnas forman una jerarquia natural (ano, luego mes, luego dia) y no necesitas subtotales laterales. Usa CUBE cuando las dimensiones son independientes y quieres la matriz completa. Gotcha: CUBE sobre muchas columnas explota exponencialmente — CUBE sobre 5 columnas son 32 agrupaciones; en tablas grandes eso es caro, asi que limita el numero de dimensiones o usa GROUPING SETS explicitos.

Sobre dialectos: CUBE, ROLLUP y GROUPING SETS se soportan en PostgreSQL 9.5+. MySQL solo ofrece GROUP BY ... WITH ROLLUP — sin CUBE ni GROUPING SETS explicito; alli la matriz completa se arma con UNION ALL. ClickHouse tiene el modificador WITH CUBE, pero el orden de las filas y el manejo de NULL difieren del estandar, asi que al portar un informe revisa especificamente las filas de totales.