Totalizando dados usando os operadores ROLLUP e CUBE no SQLServer 2000

Veja os operadores ROLLUP e CUBE utilizados para simplificar o trabalho dos densenvolvedores em situações onde dados consolidados sejam necessários.

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:

figura1
Figura 1 – resultset da tabela vendas_veiculos

Operador ROLLUP

Imagine agora que você precisa criar um relatório que totalize esses dados mostrando as seguintes informações:

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:

figura2
Figura 2 – resultset 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:

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.

Figura 3 – resultset 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.

Artigos relacionados