GROUPING SETS en SQL: subtotales y totales en una sola consulta

GROUPING SETS permite que una sola consulta calcule varios niveles de agregacion a la vez — por estado, por usuario y un total general — donde antes habia que pegar varios bloques GROUP BY con UNION ALL. No solo es mas corto, tambien mas rapido: la tabla se lee una sola vez.

El problema: una pila de UNION

Supongamos que sobre orders(id, user_id, amount, status, created_at) necesitas tres cortes: totales por estado, totales por usuario y un total general. La solucion ingenua concatena tres consultas.

SELECT status, NULL::int AS user_id, SUM(amount) AS total
FROM orders GROUP BY status
UNION ALL
SELECT NULL, user_id, SUM(amount)
FROM orders GROUP BY user_id
UNION ALL
SELECT NULL, NULL, SUM(amount)
FROM orders;

Las desventajas saltan a la vista: la tabla se escanea tres veces, la consulta es larga y es facil olvidar un cuarto corte al ampliarla.

La solucion: GROUPING SETS

El mismo resultado en una sola pasada. Cada tupla entre parentesis es un nivel de agrupacion distinto; los parentesis vacios () producen el total general.

SELECT status, user_id, SUM(amount) AS total
FROM orders
GROUP BY GROUPING SETS ((status), (user_id), ());

• (status) — totales por estado;
• (user_id) — totales por usuario;
• () — una unica fila de total general.

En las columnas que no forman parte de un conjunto dado, el motor rellena con NULL. Por eso las filas de subtotal por estado llevan un user_id igual a NULL.

GROUPING(): subtotal frente a NULL real

Aqui aparece la trampa clasica: que pasa si status contiene de verdad valores NULL (un pedido sin estado)? Entonces no puedes distinguir a simple vista una fila de subtotal de una fila de datos reales. Para eso existe GROUPING(col): devuelve 1 cuando la columna esta agregada en el conjunto actual y 0 cuando el valor es real.

SELECT
  CASE WHEN GROUPING(status) = 1 THEN 'ALL STATUSES' ELSE status END AS status,
  CASE WHEN GROUPING(user_id) = 1 THEN 'ALL USERS' ELSE user_id::text END AS usr,
  SUM(amount) AS total
FROM orders
GROUP BY GROUPING SETS ((status), (user_id), ());

Trampa: GROUPING(col) = 1 significa "esta columna no esta en el conjunto actual", no "el valor es NULL". No lo confundas con col IS NULL — son comprobaciones distintas.

ROLLUP y CUBE como atajos

GROUPING SETS es la primitiva; ROLLUP y CUBE solo generan conjuntos habituales. ROLLUP (dept, manager_id) produce una jerarquia: por (dept, manager_id), luego (dept), luego el total general — comodo para informes con subtotales por departamento.

SELECT dept, manager_id, SUM(salary) AS payroll, GROUPING(dept, manager_id) AS g
FROM employees
GROUP BY ROLLUP (dept, manager_id)
ORDER BY dept NULLS LAST, manager_id NULLS LAST;

CUBE (a, b) construye todas las combinaciones: (a,b), (a), (b), () — una tabla cruzada completa. GROUPING(dept, manager_id) devuelve una mascara de bits (por ejemplo 2 = binario 10 — solo la segunda columna esta agregada).

Compatibilidad

• PostgreSQL — soporta por completo GROUPING SETS, ROLLUP, CUBE y GROUPING() desde la 9.5.
• MySQL — solo WITH ROLLUP (GROUP BY status, user_id WITH ROLLUP) y la funcion GROUPING() desde la 8.0; no hay GROUPING SETS/CUBE generales.
• ClickHouse — soporta GROUP BY GROUPING SETS, ROLLUP, CUBE y grouping(), pero el orden de las filas no esta garantizado sin un ORDER BY explicito.

Conclusion practica: cuando necesites varios cortes a la vez, usa GROUPING SETS en lugar de una pila de UNION ALL, y marca siempre las filas de subtotal con GROUPING() para no confundirlas con NULL reales.