SQL ROLLUP, CUBE e GROUPING SETS

Lição 4.5 · Tempo de leitura: ~9 min · Tarefas para praticar ↓

SQL ROLLUP, CUBE e GROUPING SETS estendem o GROUP BY: além dos grupos normais, devolvem no mesmo resultado linhas de subtotal e um total geral. Nesta lição, você vai aprender como as três construções diferem, como usar WITH ROLLUP no MySQL e como a função GROUPING() separa as linhas de total dos valores NULL reais.

Um relatório típico mostra linhas de detalhe, subtotais e um total geral: receita por mês, um total para cada ano e o total de todo o período. Com um GROUP BY simples, seria preciso executar uma consulta para cada nível e juntar os resultados. ROLLUP, CUBE e GROUPING SETS devolvem todos os níveis numa única consulta.

Como Funcionam os Níveis de Agrupamento

Um nível de agrupamento é o conjunto de colunas pelas quais as linhas são agrupadas. Para duas colunas, há quatro níveis possíveis:

Numa linha de subtotal, as colunas "agregadas" contêm NULL.

WITH ROLLUP no MySQL e no MariaDB

MySQL e MariaDB suportam ROLLUP com sintaxe própria: WITH ROLLUP depois da lista do GROUP BY.

SELECT YEAR(payment_date) AS pay_year,
       MONTH(payment_date) AS pay_month,
       SUM(amount) AS revenue
FROM payment
GROUP BY YEAR(payment_date), MONTH(payment_date) WITH ROLLUP;

Resultado: a receita de cada mês (de maio a agosto de 2005 e fevereiro de 2006), uma linha de subtotal para cada ano em que pay_month é NULL e uma linha de total geral em que ambas as colunas são NULL.

A ordem das colunas no GROUP BY define a hierarquia: são adicionados subtotais para cada valor da primeira coluna, e a última coluna é "agregada" primeiro.

Distinguir Linhas de Total com GROUPING()

Numa linha de total, as colunas agregadas são NULL. Mas os dados reais também podem conter NULL, e aí não é possível distinguir um subtotal de um grupo de linhas sem valor. A função GROUPING(column) resolve isso: devolve 1 se a coluna foi agregada nesta linha e 0 se o valor vem dos dados.

GROUPING() é suportada pelo MySQL 8.0+, PostgreSQL, SQL Server e Oracle. O MariaDB não a suporta; nele só é possível verificar NULL.

-- MySQL 8.0+
SELECT CASE
           WHEN GROUPING(YEAR(payment_date)) = 1 THEN 'Grand total'
           WHEN GROUPING(MONTH(payment_date)) = 1 THEN 'Year total'
           ELSE 'Month'
       END AS row_type,
       YEAR(payment_date) AS pay_year,
       MONTH(payment_date) AS pay_month,
       SUM(amount) AS revenue
FROM payment
GROUP BY YEAR(payment_date), MONTH(payment_date) WITH ROLLUP;

Resultado: as mesmas oito linhas, cada uma rotulada como Month, Year total ou Grand total.

ROLLUP, CUBE e GROUPING SETS Padrão

PostgreSQL, SQL Server e Oracle suportam a sintaxe padrão das três construções. Os exemplos abaixo são executados no banco AdventureWorks no SQL Server. Os produtos são agrupados pelo ano de início das vendas e pela cor; alguns produtos não têm cor, por isso Color contém valores NULL reais.

ROLLUP

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products,
       GROUPING(Color) AS is_year_total
FROM Product
GROUP BY ROLLUP (YEAR(SellStartDate), Color)
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, GROUPING(Color), Color;

Resultado: o número de produtos para cada ano e cor, um subtotal para cada ano (is_year_total = 1) e o total geral por último. Para 2006 há duas linhas com Color = NULL: a que tem is_year_total = 0 é um grupo de 18 produtos sem cor, e a que tem is_year_total = 1 é o subtotal do ano.

CUBE

O CUBE adiciona totais para cada combinação das colunas, incluindo totais por cor somando todos os anos:

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products
FROM Product
GROUP BY CUBE (YEAR(SellStartDate), Color)
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, GROUPING(Color), Color;

Resultado: tudo o que o ROLLUP devolve, mais um total para cada cor somando todos os anos.

Com n colunas, o CUBE produz 2ⁿ níveis de agrupamento, então o resultado cresce rapidamente. Use-o com poucas dimensões.

GROUPING SETS

GROUPING SETS devolve apenas os níveis listados. Por exemplo, totais por ano e totais por cor, sem linhas de detalhe:

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products
FROM Product
GROUP BY GROUPING SETS ((YEAR(SellStartDate)), (Color), ())
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, Color;

Resultado: uma linha por ano, uma linha por cor e o total geral.

ROLLUP e CUBE são atalhos: ROLLUP (a, b) é o mesmo que GROUPING SETS ((a, b), (a), ()).

Suporte nos Bancos de Dados

Quando uma construção não é suportada, combine várias consultas GROUP BY com UNION ALL, que você vai aprender na Lição 5.10.

Quando Usar Cada Uma

• ROLLUP para hierarquias: ano → mês, região → cidade, categoria → produto.
• CUBE para analisar todos os recortes de algumas dimensões independentes.
• GROUPING SETS quando você precisa apenas de níveis específicos, por exemplo duas divisões independentes num só resultado.

Adicione sempre ORDER BY, para que as linhas de total apareçam num lugar previsível, e rotule-as com GROUPING() e CASE.

Principais conclusões:

• ROLLUP, CUBE e GROUPING SETS adicionam linhas de subtotal e de total geral ao resultado do GROUP BY.
• ROLLUP (a, b) dá (a, b), (a), (); CUBE (a, b) também adiciona (b).
• GROUPING SETS devolve exatamente os níveis listados.
• GROUPING(column) = 1 marca uma coluna agregada e separa as linhas de total dos valores NULL reais.
• MySQL e MariaDB suportam apenas WITH ROLLUP; o MariaDB não tem GROUPING().

Perguntas Frequentes

Qual é a diferença entre ROLLUP e CUBE?

O ROLLUP cria subtotais hierárquicos da direita para a esquerda: para (year, month), adiciona totais por ano e um total geral. O CUBE cria subtotais para todas as combinações, então também adiciona totais por mês somando todos os anos.

Como adicionar uma linha de total a uma consulta com GROUP BY?

No MySQL e no MariaDB, adicione WITH ROLLUP depois das colunas do GROUP BY. No PostgreSQL, SQL Server e Oracle, use GROUP BY ROLLUP (columns) ou GROUP BY GROUPING SETS ((columns), ()) para ter apenas o detalhe e o total geral.

Por que há valores NULL nos resultados do ROLLUP?

Nas linhas de subtotal e de total, as colunas agregadas não têm um valor único, por isso são NULL. Use GROUPING(column), que devolve 1 para essas linhas, para distingui-las dos valores NULL reais dos dados.