GROUPING SETS no SQL: subtotais e totais gerais em uma consulta

GROUPING SETS permite que uma unica consulta calcule varios niveis de agregacao de uma vez — por status, por usuario e um total geral — onde antes era preciso colar varios blocos GROUP BY com UNION ALL. Nao e so mais curto, e mais rapido: a tabela e lida uma unica vez.

O problema: uma pilha de UNION

Suponha que, sobre orders(id, user_id, amount, status, created_at), voce precise de tres recortes: totais por status, totais por usuario e um total geral. A solucao 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;

As desvantagens sao evidentes: a tabela e varrida tres vezes, a consulta fica longa e e facil esquecer um quarto recorte ao ampliar.

A solucao: GROUPING SETS

O mesmo resultado em uma unica passagem. Cada tupla entre parenteses e um nivel de agrupamento separado; parenteses vazios () produzem o total geral.

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

• (status) — totais por status;
• (user_id) — totais por usuario;
• () — uma unica linha de total geral.

Nas colunas que nao fazem parte de um conjunto, o motor preenche com NULL. E por isso que as linhas de subtotal por status carregam um user_id igual a NULL.

GROUPING(): subtotal versus NULL real

Aqui surge a armadilha classica: e se status realmente contiver valores NULL (um pedido sem status)? Entao voce nao consegue distinguir a olho nu uma linha de subtotal de uma linha de dados reais. E para isso que existe GROUPING(col): retorna 1 quando a coluna esta agregada no conjunto atual e 0 quando o valor e 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), ());

Armadilha: GROUPING(col) = 1 significa "esta coluna nao esta no conjunto atual", nao "o valor e NULL". Nao confunda com col IS NULL — sao verificacoes diferentes.

ROLLUP e CUBE como atalhos

GROUPING SETS e a primitiva; ROLLUP e CUBE apenas geram conjuntos comuns. ROLLUP (dept, manager_id) produz uma hierarquia: por (dept, manager_id), depois (dept), depois o total geral — pratico para relatorios com subtotais 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) constroi todas as combinacoes: (a,b), (a), (b), () — uma tabela cruzada completa. GROUPING(dept, manager_id) retorna uma mascara de bits (por exemplo 2 = binario 10 — apenas a segunda coluna esta agregada).

Compatibilidade

• PostgreSQL — suporta totalmente GROUPING SETS, ROLLUP, CUBE e GROUPING() desde a 9.5.
• MySQL — apenas WITH ROLLUP (GROUP BY status, user_id WITH ROLLUP) e a funcao GROUPING() desde a 8.0; nao ha GROUPING SETS/CUBE gerais.
• ClickHouse — suporta GROUP BY GROUPING SETS, ROLLUP, CUBE e grouping(), mas a ordem das linhas nao e garantida sem um ORDER BY explicito.

Licao pratica: quando precisar de varios recortes de uma vez, use GROUPING SETS em vez de uma pilha de UNION ALL, e sempre marque as linhas de subtotal com GROUPING() para nunca confundi-las com NULL reais.