Usando ROLLUP, CUBE e GROUPING SETS para calcular subtotais e total (ORACLE)

Os subcláusulas ROLLUP, CUBE e GROUPING SETS podem ser usados para calcular totais e subtotais de SELECTS com agrupamento e que usem funções agregadoras (ex.: SUM, COUNT, AVG, ...). 

Exemplo:

1
2
3
4
5
6
7
8
9
10

SELECT 
   SGL_ESTADO,
   DDD,
   COUNT(*)
FROM 
   ESTADOS_DDD
GROUP BY
   ROLLUP(SGL_ESTADO, DDD)
ORDER BY
   SGL_ESTADO;

O código acima retornará subtotais para o número de DDDs existentes em cada estado, bem como a soma total do número de DDDs.

A subcláusula CUBE retorna mais subtotais do que a função ROLLUP (possui a mesma sintaxe, porém retorna todos os subtotais possíveis), e a função GROUPING SETS permite escolher quais subtotais (e se total) serão retornados.

Exemplo de uso da função CUBE:

1
2
3
4
5
6
7
8
9
10

SELECT 
   CAMPO1,
   CAMPO2,
   FUNCAO_AGREGADORA(CAMPOX)
FROM 
   TABELA
GROUP BY
   CUBE(CAMPO1, CAMPO2)
ORDER BY
   CAMPO1, CAMPO2;

Na query acima, CUBE pode ser substituído por ROLLUP. A diferença é que somente os subtotais mais óbvios serão retornados com ROLLUP.

Exemplo de uso da função GROUPING SETS:

1
2
3
4
5
6
7
8
9
10
11

SELECT 
   CAMPO1,
   CAMPO2,
   CAMPO3,
   FUNCAO_AGREGADORA(CAMPOX)
FROM 
   TABELA
GROUP BY
   GROUPING SETS((CAMPO1, CAMPO2), (CAMPO3), NULL)
ORDER BY
   CAMPO1, CAMPO2;

No código acima, será mostrado subtotal para cada combinação de CAMPO1 e CAMPO2, bem como subtotal para cada CAMPO3. O NULL indica que um total geral deve ser retornado.

Em outras palavras, o SELECT acima, quando rodado, retornará resultado de um SELECT agrupando por CAMPO1 e CAMPO2 com união (UNION ALL) de um outro SELECT agrupando somente por CAMPO3 com união (UNION ALL) de uma linha de total. O resultado é o mesmo, com suas devidas adaptações, se 3 SELECTs fossem executados separadamente com UNION ALL.

Função GROUPING

A função GROUPING não deve ser confundida com a subcláusula GROUPING SETs. Pode ser usada com as subcláusulas ROLLUP e CUBE para identificar linhas agregadas e superagregadas. As linhas agregadas são as linhas agrupadas pelo GROUP BY comum e as superagregadas são as linhas de subtotais e total. A função GROUPING retornará 1 para as linhas superagregadas e 0 para as linhas agregadas. Exemplo de uso:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT 
   SGL_ESTADO,
   DDD,
   GROUPING(DDD),
   COUNT(*)
FROM 
   ESTADOS_DDD
GROUP BY
   ROLLUP(SGL_ESTADO, DDD)
ORDER BY
   SGL_ESTADO
;

Observação: Uma técnica muito comum é usar a função DECODE sobre o resultado da função GROUPING para formatar o resultado. Por exemplo, se for o valor 1, o texto "Total" pode ser apresentado, se for 0, "Subtotal".