Recursos Rollup e Cube do T-SQL no SQL Server 2012

Fala galera, no dia 06/07/2012 fui palestrar no TDC2012 em São Paulo na trilha de Banco de Dados, e, assistindo a apresentação do Wagner Crivelini (Artigos SQL Server Central | Artigos DevMedia | Twitter) sobre Geração de Relatórios com Linguagem SQL, baseado no padrão ISO, percebi que algumas pessoas não conheciam as instruções de CUBE ou ROLLUP que ele apresentou. Mesmo com a boa explicacão do Wagner, e seus exemplos práticos, o pessoal parecia ter dúvidas.

Por esse motivo, decidi escrever esse post explicando estes recursos dentro do SQL Server e mostrando códigos T-SQL.

Um ponto importante sobre o CUBE e o ROLLUP é que são aplicados ao SQL Server exclusivamente com a cláusula GROUP BY, como acontece com o HAVING.

Group By

Esta função é conhecida por todos (assim espero) e não vou “chover no molhado” explicando seu funcionamento. O que vou fazer aqui é colocar o código T-SQL que usei e o resultado obtido, para servir de comparação com os outros códigos que, aí sim, é o objetivo do artigo.

Este código T-SQL será base para os exemplos e faz parte da tabela PurchaseOrderDetail do database de exemplo AdventureWorks2012. Este código representa o somatório de cada pedido, agrupado pelo mês e ano.

   1:  -- 34 registros

   2:  select sum(LineTotal) [Total], month(DueDate) [Mes], year(DueDate) [Ano]

   3:  from [Purchasing].[PurchaseOrderDetail]

   4:  where year(DueDate) >= 2005

   5:  group by month(DueDate), year(DueDate)

   6:  order by [Ano],[Mes]

Sendo o resultado:

Função Rollup

Esta função gera resultados de subtotal baseado nos grupos das colunas definidos no Group By, resultando mais algumas linhas ao resultset. Estas linhas extras são os subtotais que foram calculados, sendo uma linha para cada subtotal e uma linha para o total final (somatório de todos os subtotais). Neste exemplo de código, o Group By convencional retornou 34 registros. Já com o Rollup o retorno foi de 47. Isso ocorre, porque o calculo foi baseado na coluna Mes que adicionou 12 subtotais ao resultset + 1 coluna calculando o total de todos os subtotais calculados.

   1:  -- 47 registros

   2:  select sum(LineTotal) [Total], month(DueDate) [Mes], year(DueDate) [Ano]

   3:  from [Purchasing].[PurchaseOrderDetail]

   4:  where year(DueDate) >= 2005

   5:  group by rollup (month(DueDate), year(DueDate))

O resultado é parecido com o do Group By, porém possui valores NULL inseridos em determinadas linhas. Esses valores NULL indicam que naquela linha do resultado existe um subtotal calculado, e um onde pode-se encontrar dois valores NULL na linha, significa que é o somatório do resultado de todos os subtotais.

Repare que onde existe o valor NULL na coluna Ano, a coluna Mes está preenchida com o valor do mês, isso significa que o resultado da coluna Total é o somatório de todos os meses referentes ao valor da coluna Mes, independente do ano.

Função Cube

Esta função gera ainda mais algumas linhas de subtotais que a Rollup, pois ela calcula todos os subtotais possíveis para o agrupamento, e não somente o subtotal de uma coluna. No caso do Rollup acima, foi calculado o subtotal dos meses, já no Cube foi calculado o subtotal dos meses e também o subtotal dos anos. Totalizando 51 registros (34 registros do Group By + 12 registros dos subtotais de meses + 4 subtotais de anos + 1 subtotal de TUDO = 51 registros)

   1:  -- 51 registros

   2:  select sum(LineTotal) [Total], month(DueDate) [Mes], year(DueDate) [Ano]

   3:  from [Purchasing].[PurchaseOrderDetail]

   4:  where year(DueDate) >= 2005

   5:  group by cube (month(DueDate), year(DueDate))

O resultado é parecido com o do Rollup porém com mais subtotais, calculando não somente os subtotais dos meses como também os subtotais dos anos, e também o somatório de todos os subtotais. Na mesma explicação que temos no Rollup, quando existem valores NULL na coluna Mes significa que o valor existente na coluna Total é relativo ao ano representado na coluna Ano. Mesma situação continua se aplicado às colunas Mes, quando se encontra valor NULL na coluna Ano.

Repare que o crescimento de linhas no resultset é exponencial, baseado na quantidade de agrupadores que são gerados pelo Group By. Isso significa que você precisa se preocupar com os dados, pois o processamento destes subtotais cruzados (como acontece na função Cube) pode impactar negativamente a performance da sua consulta, uma vez que o SQL Server deverá cruzar os somatórios de todas as possibilidades existentes em todas as colunas.