Galera hoje estou aqui com o primeiro post de uma série de três, que tem como tema os novos operadores ROLLUP, CUBE e GROUPING SETS que são extensões da cláusula GROUP BY no SQL Server 2008. O tema de hoje será o operador ROLLUP. A cláusula ROLLUP permite agregar resultados, produzindo contagens sobre as contagens, somas das somas ou médias das médias. Esses operadores são de grande ajuda quando se trata de criação relatórios.

Para exemplificar o uso desse simples operador, criei alguns exemplos que esboçam seu uso na prática. Os exemplos a seguir foram executados na base de dados “AdventureWorks”, que pode ser baixado gratuitamente a qualquer momento nos sites da Microsoft (http://msftdbprodsamples.codeplex.com/releases/view/55926).

1º) Nesse primeiro exemplo é criada uma query que tem como retorno alguns produtos em suas respectivas categorias.

SELECT
     Categoria.Name as 'Categoria'
    ,Produto.Name as 'Produto'
FROM Production.Product Produto
    INNER JOIN Production.ProductSubcategory Categoria
    ON Categoria.ProductSubcategoryID = Produto.ProductSubcategoryID
WHERE Categoria.Name
    IN ('Bib-Shorts','Bike Racks','Bike Stands','Bottles and Cages','Bottom Brackets','Brakes');

Primeiro Exemplo Part 1

Com a utilização do operador ROLLUP na cláusula GROUP BY, conseguimos retornar o total de produtos por categoria e ainda obter um total geral dos produtos agrupados.

SELECT
    Categoria.Name as 'Categoria', COUNT(Produto.Name) as 'QtdProduto'
FROM Production.Product Produto
    INNER JOIN Production.ProductSubcategory Categoria
    ON Categoria.ProductSubcategoryID = Produto.ProductSubcategoryID
    WHERE Categoria.Name IN ('Bib-Shorts','Bike Racks','Bike Stands','Bottles and Cages','Bottom Brackets','Brakes')
    GROUP BY ROLLUP (Categoria.Name);

Primeiro Exemplo Part 2

Ainda podemos melhorar a visualização dos dados, caso a intenção seja criar um relatório. A melhoria será simples, trocaremos o valor “NULL” por uma informação personalizada.

SELECT
    CASE
       WHEN Categoria.Name IS NULL
       THEN 'Total Geral'
         ELSE Categoria.Name
       END as 'Categoria', COUNT(Produto.Name) as 'QtdProduto'
    FROM Production.Product Produto
       INNER JOIN Production.ProductSubcategory Categoria
         ON Categoria.ProductSubcategoryID = Produto.ProductSubcategoryID
       WHERE Categoria.Name IN ('Bib-Shorts','Bike Racks','Bike Stands','Bottles and Cages','Bottom Brackets','Brakes')
       GROUP BY ROLLUP (Categoria.Name);

Primeiro Exemplo Part 3

2º) No segundo exemplo seremos um pouco mais audaciosos, utilizaremos as funções SUM, AVG e COUNT associados ao operador ROLLUP na cláusula GROUP BY. Apesar do uso de todos esses recursos em apenas uma query, se trata de uma consulta simples que retorna os dados já formatados, ou seja, não teremos a preocupação de realizar o tratamento dos dados na aplicação ou em uma planilha Excel. Enfim, a query tem como objetivo retornar um resultado analítico de venda de determinadas regiões. Resultados como: quota de venda por região, quota média por região e quantidade de vendedores.

SELECT
      Regiao.Name as 'Regiao',
      Vendedor.SalesPersonID 'ID Vendedor',
      SUM(Vendedor.SalesQuota) as 'Quota por Região',
      AVG(Vendedor.SalesQuota) as 'Quota Média por Região',
      COUNT(Vendedor.SalesQuota) as 'Qtd Vendedor por Região',
FROM Sales.SalesPerson Vendedor
      INNER JOIN Sales.SalesTerritory Regiao ON Regiao.TerritoryID = Vendedor.TerritoryID
      GROUP BY ROLLUP (Regiao.Name,Vendedor.SalesPersonID);

Segundo Exemplo Part 1

Agora vamos melhorar a apresentação dos dados trocando os valores “NULL”, por uma informação personalizada.

SELECT
   CASE
     WHEN Regiao.Name IS NULL
       THEN 'Total Geral'
     ELSE Regiao.Name
       END as 'Regiao',
     CASE
        WHEN Regiao.Name IS NULL AND Vendedor.SalesPersonID IS NULL
      THEN '---'
        WHEN Vendedor.SalesPersonID IS NULL
      THEN 'Total Vendedor'
    ELSE CAST(Vendedor.SalesPersonID AS VARCHAR)
      END as 'ID Vendedor',
      SUM(Vendedor.SalesQuota) as 'Quota por Região',
      AVG(Vendedor.SalesQuota) as 'Quota Média por Região',
      COUNT(Vendedor.SalesQuota) as 'Qtd Vendedor por Região'
    FROM Sales.SalesPerson Vendedor
 INNER JOIN Sales.SalesTerritory Regiao
    ON Regiao.TerritoryID = Vendedor.TerritoryID
    GROUP BY ROLLUP (Regiao.Name,Vendedor.SalesPersonID);

Segundo Exemplo Part 2

E para finalizar, irei apresentar a função GROUPING, que é responsável por identificar em qual linha está acontecendo o agrupamento, o que pode ser muito útil para quem ainda deseja realizar algum tipo de tratamento dos dados na aplicação.

SELECT
   CASE
     WHEN Regiao.Name IS NULL
       THEN 'Total Geral'
     ELSE Regiao.Name
     END as 'Regiao',
   CASE
     WHEN Regiao.Name IS NULL AND Vendedor.SalesPersonID IS NULL
       THEN '---'
     WHEN Vendedor.SalesPersonID IS NULL
       THEN 'Total Vendedor'
   ELSE CAST(Vendedor.SalesPersonID AS VARCHAR)
   END as 'ID Vendedor',
SUM(Vendedor.SalesQuota) as 'Quota por Região',
AVG(Vendedor.SalesQuota) as 'Quota Média por Região',
COUNT(Vendedor.SalesQuota) as 'Qtd Vendedor por Região',
GROUPING(Vendedor.SalesPersonID) AS 'Agrupamento'
   FROM Sales.SalesPerson Vendedor
   INNER JOIN Sales.SalesTerritory Regiao
   ON Regiao.TerritoryID = Vendedor.TerritoryID
   GROUP BY ROLLUP (Regiao.Name,Vendedor.SalesPersonID);

Bem galera, espero ter explicado o recurso ROLLUP de uma forma simples e clara. Aguardem pelos próximos Posts.