Totalizando dados usando os operadores ROLLUP e CUBE no SQLServer 2000

Reydeval Rocha

 

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:

 

19-10pic01.JPG 

Figura 1resultset da tabela vendas_veiculos

 

 

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:

 

19-10pic02.JPG 

Figura 2resultset  mostrando a utilização do operador ROLLUP

 

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.

 

19-10pic03.JPG  

Figura 3resultset  mostrando a utilização do operador CUBE

 

 

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.