1. Introdução
A cláusula GROUP BY é utilizada em instruções SELECT com o propósito de produzir resultados agregados por um ou mais campos. Iniciaremos este artigo relembrando rapidamente a sua forma de utilização. Para isto, faremos uso de um exemplo envolvendo os dados armazenados na tabela “T_VENDAS”, apresentada na Figura 1.
Imagine que esta tabela armazena os dados das compras efetuadas por clientes de uma loja de departamentos hipotética, em uma determinada semana. Cada registro representa um cliente diferente, identificado pelo atributo Id. Os campos “Sexo”, “Idade” e “UF” descrevem propriedades dos clientes, enquanto o campo e “Valor” indica o valor gasto na compra realizada por cada cliente. O script para criar e popular a tabela no Oracle é apresentado na Listagem 1.
Figura 1: Tabela T_VENDAS
Listagem 1: Script para criar e popular a tabela “T_VENDAS”
//criação da tabela
CREATE TABLE t_vendas (
id NUMBER PRIMARY KEY,
sexo CHAR(1),
idade CHAR(5),
UF CHAR(2),
VALOR NUMBER (5,2)
)
/
//inserindo dados na tabela
INSERT INTO t_vendas VALUES(1,'F','<18','SP',115);
INSERT INTO t_vendas VALUES(2,'M','26-30','RJ',120);
INSERT INTO t_vendas VALUES(3,'F','26-30','RJ',49);
INSERT INTO t_vendas VALUES(4,'F','18-25','MG',235);
INSERT INTO t_vendas VALUES(5,'F','31-40','SP',101);
INSERT INTO t_vendas VALUES(6,'M','<18','RJ',200);
INSERT INTO t_vendas VALUES(7,'F','26-30','MG',55);
INSERT INTO t_vendas VALUES(8,'F','<18','RJ',5);
INSERT INTO t_vendas VALUES(9,'M','18-25','RJ',68);
INSERT INTO t_vendas VALUES(10,'M','<18','SP',15);
COMMIT;
Com o uso da cláusula GROUP BY, podemos criar uma instrução SELECT capaz de gerar grupos formados pelas diferentes combinações de valores de campos escolhidos e produzir um determinado resultado matemático (soma, média, contagem de frequência, etc.) referente a cada grupo. Um exemplo é apresentado na Lisatagem 2 em que o SELECT com GROUP BY contabiliza a soma dos valores gastos nas compras dentro de grupos formados pelos campos “Sexo” e “UF”. . O resultado produzido é apresentado na Figura 2.
Listagem 2: SELECT com GROUP BY
SELECT SEXO, UF, SUM(VALOR)
FROM T_VENDAS
GROUP BY SEXO, UF
ORDER BY 1,2
Figura 2: Resultado do SELECT da Listagem 2
2. ROLLUP
Certamente, para a maioria dos leitores do artigo o texto apresentado até aqui não representou nenhuma novidade, pois a cláusula GROUP BY é bastante conhecida. No entanto, o que nem todos os desenvolvedores que trabalham com Oracle sabem é que é possível modificar o comportamento do GROUP BY com o uso de uma sub-cláusula denominada ROLLUP.
Basicamente, a operação de ROLLUP serve para produzir “agregados de agregados” em instruções SELECT com GROUP BY. Estes resultados “agregados de agregados” são chamados de “linhas superagregadas” no jargão do Oracle.
A Listagem 3 apresenta um exemplo de utilização da operação de ROLLUP. Neste exemplo, o ROLLUP é utilizado para adicionar algumas linhas de subtotais e uma linha de total geral no resultado do comando SELECT. Veja o resultado da execução deste SELECT na Figura 3 e compare com o resultado do SELECT com GROUP BY comum apresentado na Listagem 1.
Listagem 3: SELECT com GROUP BY e ROLLUP
SELECT SEXO, UF, SUM(VALOR)
FROM T_VENDAS
GROUP BY ROLLUP (SEXO, UF)
ORDER BY 1,2
Figura 3: Resultado do SELECT da Listagem 3
Em resumo, os efeitos produzidos pelo ROLLUP foram os seguintes:
- Nas linhas 4 e 7, são apresentados subtotais para cada valor do campo “Sexo”. Ou seja: Sexo Feminino gastou R$ 560,00 (linha 4) e Sexo Masculino gastou R$ 403,00 (linha 7).
- Na linha 8 apresenta-se o total geral dos valores gastos em compras. Ou seja: o somatório geral do campo “VALOR” considerando todos os grupos.
A seguir são relacionadas as regras de sintaxe para a sub-cláusula ROLLUP:
- A palavra-chave “ROLLUP” faz parte da cláusula “GROUP BY” e deve ser utilizada após a palavra-chave “GROUP BY”.
- A palavra-chave “ROLLUP” deve ser seguida por uma lista de campos (expressão de grupo) indicada entre parêntesis.
3. ROLLUP – Exemplos Adicionais
A operação de ROLLUP pode ser aplicada apenas a uma parte do GROUP BY. Na Listagem 4, ilustramos esse tipo de situação. A coluna “Idade” foi adicionada ao SELECT, porém a operação de ROLLUP continuou mantida apenas para os campos “Sexo” e “UF”. Veja os resultados na Figura 4.
Listagem 4: SELECT com operação de ROLLUP apenas sobre parte dos campos do GROUP BY
SELECT IDADE, SEXO, UF, SUM(VALOR)
FROM T_VENDAS
GROUP BY IDADE, ROLLUP (SEXO, UF)
ORDER BY 1,2, 3
Figura 4: Resultado do SELECT da Listagem 4
Neste exemplo, observe que o total geral (somatório do campo “VALOR”) não foi produzido, mas foram gerados subtotais para cada “Idade”. Isto ocorre porque a operação de ROLLUP só atua sobre os campos que estiverem especificados entre parêntesis.
Como comentário final, é importante observar que o ROLLUP não é utilizado apenas com a função SUM, mas também com qualquer outra função de grupo. No exemplo da Listagem 5 utilizamos COUNT(*) para contar as frequências de cada combinação de valores dos grupos (veja os resultados na Figura 5). Já no exemplo da Listagem 6, apresenta-se a forma de computar a média de gastos por grupo, com o uso da função AVG (resultados mostrados na Figura 6).
Listagem 5: SELECT com operação de ROLLUP e COUNT(*)
SELECT SEXO, UF, COUNT(*)
FROM T_VENDAS
GROUP BY ROLLUP (SEXO, UF)
ORDER BY 1,2
Figura 5: Resultado do SELECT da Listagem 5
Listagem 6: SELECT com operação de ROLLUP e função AVG
SELECT SEXO, UF, ROUND(AVG(VALOR),2)
FROM T_VENDAS
GROUP BY ROLLUP (SEXO, UF)
ORDER BY 1,2
Figura 6: Resultado do SELECT da Listagem 6
Assim encerramos o artigo sobre a operação de ROLLUP no Oracle. Até a próxima!
