Introdução
Quando trabalhamos com data warehouses, business intelligences ou simples relatórios gerenciais, freqüentemente precisamos criar totais, subtotais ou agrupamentos para que as informações sejam, sumarizadas.
Um modelo bastante utilizado é criar um select que retorne os dados para que a aplicação faça os agrupamentos necessários. Outra abordagem é a criação de stored procedures que retornem as informações num formato desejado.
Os operadores ROLLUP e CUBE podem, em alguns casos, ser utilizados para suprir a necessidade de totalizações fornecendo as informações desejadas com apenas um select. Vejamos um exemplo a seguir.
Seja a tabela vendas_veículos contendo as informações dos veículos vendidos numa dada revendedora. A tabela possui os campos: veiculo, cor e o valor do veículo. A instrução select * from vendas_veículos retorna os dados abaixo:
Operador ROLLUP
Imagine agora que você precisa criar um relatório que totalize esses dados mostrando as seguintes informações:
- Total vendido de cada veículo (independente da cor)
- Total geral
O operador ROLLUP pode ser utilizado da seguinte forma:
SELECT CASE
WHEN (GROUPING(veiculo) = 1) THEN 'Total'
ELSE ISNULL(veiculo, 'NÃO INFORMADO')
END AS veiculo,
CASE
WHEN (GROUPING(cor) = 1) THEN 'Total'
ELSE ISNULL(cor, 'NÃO INFORMADO')
END AS cor,
SUM(valor) AS Total_Vendido
FROM vendas_veiculos
GROUP BY veiculo, cor WITH rollup
O select acima retorna os valores vendidos por veículo, independente da cor, e o total geral vendido. Note que foi utilizada função GROUPING no select. Esta é uma função agregada que retorna 1 quando a linha do resultset foi gerada pelo operador ROLLUP ou CUBE, ou seja não pertence à tabela, e retorna 0 caso contrário.
Quando o NULL é retornado numa instrução select que utiliza o operador ROLLUP ou CUBE ele possui um significado especial. O dado NULL marca um “total” para aquele agrupamento e tem o significado de “todos os valores” no resultset. A saída do select anterior seria:
O operador ROLLUP gera todas as combinações possíveis de valores das cores com os valores de veículos.
Operador CUBE
Suponha que seja necessário retornar para um relatório as seguintes informações:
- Total vendido de cada veículo (independente da cor)
- Total vendido de cada cor (independente do veículo)
- Total geral
O operador CUBE pode ser utilizado da seguinte forma:
SELECT CASE
WHEN (GROUPING(veiculo) = 1) THEN 'Todos'
ELSE ISNULL(veiculo, 'NÃO INFORMADO')
END AS veiculo,
CASE
WHEN (GROUPING(cor) = 1) THEN 'Todos'
ELSE ISNULL(cor, 'NÃO INFORMADO')
END AS cor,
SUM(valor) AS Total_Vendido
FROM vendas_veiculos
GROUP BY veiculo, cor WITH cube
O select acima retorna os valores vendidos por veículo, independente da cor, os valores vendidos por cor, independente do veículo e o total geral vendido. Novamente foi utilizada função GROUPING no select.
O operador CUBE gera não apenas todas as combinações possíveis de valores das cores com os valores de veículos, mas também todas as combinações possíveis de valores de veículos com os valores das cores.
Conclusão
Os operadores ROLLUP e CUBE podem ser utilizados para simplificar o trabalho dos desenvolvedores em situações onde dados consolidados sejam necessários, diminuindo o código nas aplicações cliente para gerar tais totalizações.
