CUBE no SQL: todas as combinacoes de agrupamento em uma passada para relatorios cross-tab

CUBE e uma extensao do GROUP BY que, em uma unica passada, calcula agregados sobre todas as combinacoes das colunas indicadas: cada dimensao isolada, suas intersecoes e o conjunto de dados inteiro. E um motor pronto para relatorios cross-tab do tipo "soma por regiao, por produto, por regiao+produto e um total geral, em uma unica tabela".

O que o CUBE calcula

O exemplo canonico: vendas detalhadas por regiao e produto.

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

GROUP BY CUBE (region, product) constroi quatro agrupamentos de uma vez:

• (region, product) — as celulas de detalhe do cross-tab;
• (region) — um total por regiao (qualquer produto);
• (product) — um total por produto (qualquer regiao);
• () — o conjunto vazio, ou seja o total geral sobre todas as linhas.

Para n colunas, CUBE produz 2^n agrupamentos. Duas colunas dao 4 conjuntos, tres dao 8, quatro dao 16. Essa e a diferenca chave para o ROLLUP, que colapsa colunas apenas da direita para a esquerda (uma hierarquia, n + 1 niveis), enquanto o CUBE pega todos os subconjuntos. CUBE (a, b) produz tanto (a) quanto (b); ROLLUP (a, b) produz (a) mas nao (b).

Lendo subtotais NULL com GROUPING()

A pegadinha principal: as linhas de subtotal sao marcadas com NULL nas colunas colapsadas. Se seus dados ja contem NULL (por exemplo, uma venda sem regiao), voce nao consegue distinguir um NULL real de uma marca de total. A funcao GROUPING() remove a ambiguidade: ela retorna 1 quando uma coluna foi agregada naquela linha, e 0 quando ela contem um 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;

E mais limpo trocar as flags por rotulos legiveis logo de cara, para que o relatorio dispense notas de rodape:

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 escolher um "corte" especifico do cubo, filtre pelos bits de GROUPING() no HAVING. Por exemplo, apenas os totais por pais (status colapsado, country nao):

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 em um exemplo real de RH

CUBE encaixa muito bem em relatorios matriciais. Pegue os salarios de funcionarios detalhados por departamento e por se respondem a um gestor:

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;

Uma unica consulta devolve de uma vez: o salario medio de cada par "departamento + nivel", de cada departamento inteiro, de cada nivel em toda a empresa e o total geral. Sem CUBE voce teria que costurar quatro SELECT com UNION ALL.

CUBE vs ROLLUP vs GROUPING SETS

Os tres sao acucar sintatico sobre GROUPING SETS. Conhecer as equivalencias te da controle total:

• CUBE (a, b) = GROUPING SETS ((a, b), (a), (b), ()) — todas as combinacoes;
• ROLLUP (a, b) = GROUPING SETS ((a, b), (a), ()) — apenas a hierarquia da direita para a esquerda;
• precisa de um conjunto nao padrao? Escreva na mao: GROUPING SETS ((a, b), (b), ()).

Use ROLLUP quando as colunas formam uma hierarquia natural (ano, depois mes, depois dia) e voce nao precisa de subtotais laterais. Use CUBE quando as dimensoes sao independentes e voce quer a matriz completa. Pegadinha: CUBE sobre muitas colunas explode exponencialmente — CUBE sobre 5 colunas sao 32 agrupamentos; em tabelas grandes isso e caro, entao limite o numero de dimensoes ou use GROUPING SETS explicitos.

Sobre dialetos: CUBE, ROLLUP e GROUPING SETS sao suportados no PostgreSQL 9.5+. O MySQL oferece apenas GROUP BY ... WITH ROLLUP — sem CUBE nem GROUPING SETS explicito; la a matriz completa e montada com UNION ALL. O ClickHouse tem o modificador WITH CUBE, mas a ordem das linhas e o tratamento de NULL diferem do padrao, entao ao portar um relatorio verifique especificamente as linhas de totais.