Módulo 14 — GROUPING SETS, ROLLUP & CUBE

1) Conceitos & Quando usar

Em relatórios é comum precisar de detalhes + subtotais + total geral. Fazer isso com vários UNION ALL aumenta manutenção e IO. As construções GROUPING SETS, ROLLUP e CUBE resolvem em uma única passagem.

2) GROUPING SETS

Permite declarar explicitamente os conjuntos de agrupamento desejados.

-- Total por UF, total por Cidade, e Total Geral (em uma consulta)
SELECT
  c.UF,
  c.Cidade,
  SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY GROUPING SETS (
  (c.UF, c.Cidade),   -- detalhe
  (c.UF),             -- subtotal por UF
  ()                  -- total geral
);

O conjunto vazio () representa o Total Geral.

3) ROLLUP (hierarquias)

Gera agregações em cascata respeitando a ordem das colunas (da direita para a esquerda), além do total geral.

-- Hierarquia UF → Cidade + Total Geral
SELECT
  c.UF,
  c.Cidade,
  SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY ROLLUP (c.UF, c.Cidade);

Resultado inclui: (UF,Cidade), (UF,NULL) e (NULL,NULL).

4) CUBE (todas as combinações)

Gera todas as combinações possíveis das colunas, além do total geral.

-- Exemplo com UF e Trimestre (derivado de DataVenda)
SELECT
  c.UF,
  DATEPART(QUARTER, v.DataVenda) AS Trimestre,
  SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY CUBE (c.UF, DATEPART(QUARTER, v.DataVenda));

5) GROUPING() & GROUPING_ID()

Como subtotais/total usam NULL nos níveis agregados, use GROUPING() para diferenciar NULL real de NULL de subtotal. GROUPING_ID() retorna um número que codifica quais colunas foram agregadas.

SELECT
  c.UF,
  c.Cidade,
  SUM(v.Valor) AS Total,
  GROUPING(c.UF)     AS G_UF,     -- 1 se agregada (subtotal/total), 0 se detalhe
  GROUPING(c.Cidade) AS G_Cidade,
  GROUPING_ID(c.UF, c.Cidade) AS GID
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY ROLLUP (c.UF, c.Cidade)
ORDER BY
  GROUPING_ID(c.UF, c.Cidade),  -- detalhe primeiro, totais por último
  c.UF, c.Cidade;

-- Rotulando linhas de subtotal/total
SELECT
  ISNULL(c.UF, CASE WHEN GROUPING(c.UF) = 1 THEN '*** TOTAL GERAL ***' END) AS UF_Label,
  CASE
    WHEN GROUPING(c.UF) = 0 AND GROUPING(c.Cidade) = 1 THEN 'SUBTOTAL UF'
    WHEN GROUPING(c.UF) = 1 AND GROUPING(c.Cidade) = 1 THEN 'TOTAL GERAL'
    ELSE c.Cidade
  END AS Cidade_Label,
  SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY ROLLUP (c.UF, c.Cidade);

6) Laboratório Guiado

6.1) Setup (reaproveitando tabelas)

-- Cria tabelas se não existirem
IF OBJECT_ID('dbo.Clientes','U') IS NULL
BEGIN
  CREATE TABLE dbo.Clientes
  (
      IdCliente      int            IDENTITY PRIMARY KEY,
      Nome           nvarchar(100)  NOT NULL,
      CPF            varchar(11)    NULL,
      DataNascimento date           NULL,
      Cidade         nvarchar(100)  NULL,
      UF             char(2)        NULL,
      Ativo          bit            NOT NULL DEFAULT 1
  );
END
IF OBJECT_ID('dbo.Vendas','U') IS NULL
BEGIN
  CREATE TABLE dbo.Vendas
  (
      IdVenda     int            IDENTITY PRIMARY KEY,
      IdCliente   int            NOT NULL REFERENCES dbo.Clientes(IdCliente),
      DataVenda   date           NOT NULL,
      Valor       decimal(10,2)  NOT NULL
  );
END

-- Dados mínimos se vazio
IF NOT EXISTS (SELECT 1 FROM dbo.Clientes)
BEGIN
  INSERT INTO dbo.Clientes (Nome, CPF, DataNascimento, Cidade, UF, Ativo) VALUES
  (N'João Silva', '11122233344', '1993-05-10', N'São Paulo', 'SP', 1),
  (N'Ana Souza',  '55566677788', '1997-09-21', N'Campinas',  'SP', 1),
  (N'Bruno Lima', NULL,          '1985-12-02', N'Curitiba',  'PR', 0);
END
IF NOT EXISTS (SELECT 1 FROM dbo.Vendas)
BEGIN
  INSERT INTO dbo.Vendas (IdCliente, DataVenda, Valor) VALUES
  (1, '2025-07-28',  300.00),
  (1, '2025-08-01', 1200.00),
  (1, '2025-08-15',  350.50),
  (2, '2025-08-10',  999.90),
  (2, '2025-06-03',  180.00);
END
GO

6.2) Subtotais com GROUPING SETS

SELECT
  c.UF, c.Cidade, SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY GROUPING SETS ((c.UF,c.Cidade),(c.UF),());

6.3) Hierarquia com ROLLUP

SELECT
  c.UF, c.Cidade, SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY ROLLUP (c.UF, c.Cidade)
ORDER BY GROUPING_ID(c.UF, c.Cidade), c.UF, c.Cidade;

6.4) CUBE com Trimestre

SELECT
  c.UF,
  DATEPART(QUARTER, v.DataVenda) AS Trimestre,
  SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY CUBE (c.UF, DATEPART(QUARTER, v.DataVenda))
ORDER BY c.UF, Trimestre;

6.5) Rotulando Totais

SELECT
  CASE WHEN GROUPING(c.UF) = 1     THEN 'TOTAL GERAL' ELSE c.UF     END AS UF_Label,
  CASE WHEN GROUPING(c.Cidade) = 1 THEN 'SUBTOTAL UF' ELSE c.Cidade END AS Cidade_Label,
  SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY ROLLUP (c.UF, c.Cidade);

7) Exercícios

1. Subtotal + Total: Traga total de vendas por UF e o total geral usando GROUPING SETS.
2. ROLLUP hierárquico: Use ROLLUP (UF, Cidade) e ordene com GROUPING_ID deixando detalhes primeiro.
3. CUBE com tempo: Agregue por UF e Trimestre usando CUBE e filtre apenas linhas onde UF não é subtotal (dica: GROUPING(UF)=0).
4. Labels: Rotule subtotais/total usando GROUPING() de forma legível para relatório.
5. Mensal/Anual: Crie um relatório com GROUPING SETS para mostrar por ano, por ano+mês e total.

8) Gabarito (sugestões)

-- 1) UF + Total Geral
SELECT c.UF, SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY GROUPING SETS ((c.UF), ());

-- 2) ROLLUP com ordenação por GID
SELECT c.UF, c.Cidade, SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY ROLLUP (c.UF, c.Cidade)
ORDER BY GROUPING_ID(c.UF, c.Cidade), c.UF, c.Cidade;

-- 3) CUBE UF x Trimestre filtrando detalhe de UF
SELECT c.UF, DATEPART(QUARTER, v.DataVenda) AS Trimestre, SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY CUBE (c.UF, DATEPART(QUARTER, v.DataVenda))
HAVING GROUPING(c.UF) = 0;

-- 4) Labels com GROUPING()
SELECT
  CASE WHEN GROUPING(c.UF) = 1     THEN 'TOTAL GERAL' ELSE c.UF END AS UF_Label,
  CASE WHEN GROUPING(c.Cidade) = 1 THEN 'SUBTOTAL UF' ELSE c.Cidade END AS Cidade_Label,
  SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY ROLLUP (c.UF, c.Cidade);

-- 5) Ano, Ano+Mês e Total
SELECT
  YEAR(v.DataVenda) AS Ano,
  DATEFROMPARTS(YEAR(v.DataVenda), MONTH(v.DataVenda), 1) AS AnoMes,
  SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
GROUP BY GROUPING SETS (
  (YEAR(v.DataVenda), DATEFROMPARTS(YEAR(v.DataVenda), MONTH(v.DataVenda), 1)), -- ano+mês
  (YEAR(v.DataVenda)),  -- anual
  ()                    -- total
)
ORDER BY Ano, AnoMes;

9) Erros Comuns & Boas Práticas

10) Próximos Passos

• Identificar relatórios que usam vários UNION ALL e migrar para GROUPING SETS.
• Comparar custo lógico/IO vs abordagens tradicionais.
• Preparar o Módulo 15: CTEs & Recursividade (modelagem hierárquica).