ROLLUP no SQL: subtotais hierarquicos e um total geral em uma unica consulta

ROLLUP e uma extensao do GROUP BY que, em uma unica consulta, calcula nao apenas os grupos comuns mas tambem subtotais sobre prefixos de colunas mais uma linha de total geral. E um motor pronto para relatorios do tipo "receita por mes e regiao, com subtotais e uma soma final".

O que o ROLLUP calcula

GROUP BY ROLLUP (a, b) agrega em varios niveis, descartando colunas da direita para a esquerda: primeiro (a, b), depois (a), depois () o conjunto vazio, ou seja todo o conjunto de dados. Com duas colunas isso da as linhas de detalhe, subtotais por a e uma unica linha de total geral.

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

Alem dos pares comuns (country, status), o resultado adiciona:

• uma linha por pais onde status e NULL: esse e o subtotal por pais;
• uma linha onde tanto country quanto status sao NULL: o total geral de todos os pedidos.

A ordem das colunas importa: ROLLUP (country, status) e ROLLUP (status, country) produzem subtotais diferentes, porque a hierarquia colapsa da direita para a esquerda.

Lendo os NULL finais com GROUPING()

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

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
GROUP BY ROLLUP (country, status)
ORDER BY country NULLS LAST, status NULLS LAST;

Um relatorio de detalhamento por mes e regiao

Um relatorio gerencial classico e a receita detalhada por mes e regiao, mostrando o detalhe, o subtotal por mes e o total geral. Aqui o ROLLUP combina muito bem com 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 recebe suas linhas por regiao, depois um subtotal do mes (region = 'All regions'), e bem no final All months com a receita completa. Ordenar com NULLS LAST garante que os subtotais fiquem abaixo de suas linhas de detalhe, e nao acima.

Relacao com GROUPING SETS

ROLLUP e apenas uma forma abreviada de GROUPING SETS. A consulta GROUP BY ROLLUP (a, b) e estritamente equivalente a:

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

Saber disso te da flexibilidade:

• quer CUBE? Isso e GROUPING SETS sobre todas as combinacoes: (a, b), (a), (b), ();
• quer apenas detalhe e um total geral, sem subtotais intermediarios? Escreva GROUPING SETS ((a, b), ()) na mao;
• voce pode combinar varios ROLLUP e colunas comuns dentro de um mesmo GROUP BY.

Sobre dialetos: ROLLUP, CUBE e GROUPING SETS sao suportados no PostgreSQL 9.5+. O MySQL oferece apenas GROUP BY ... WITH ROLLUP (sem CUBE nem GROUPING SETS explicito), e ORDER BY junto com WITH ROLLUP e instavel. O ClickHouse tem os modificadores WITH ROLLUP, WITH CUBE e WITH TOTALS, mas a sintaxe e a ordem das linhas diferem do padrao. Se voce portar um relatorio entre motores, verifique especificamente as linhas de totais.