Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Agrupando dados utilizando ROLLUP, CUBE e GROUPING SETS

Visualizações: 13.672 views
Tempo de Leitura: 9 minutos

OlĂ¡ pessoal,
Bom dia!

Neste post vou falar sobre um assunto que nĂ£o Ă© nenhuma novidade no SQL Server, estĂ¡ presente desde o SQL Server 2008, mas nĂ£o vejo muita gente utilizando em suas consultas, que Ă© o agrupamento de dados (sumarizaĂ§Ă£o) utilizando ROLLUP, CUBE e GROUPING SETS.

Esse tipo de recurso Ă© especialmente Ăºtil para gerar totais e subtotais sem precisar criar vĂ¡rias subquerys, pois permite realizar essa tarefa em um Ăºnico comando, conforme vou demonstrar abaixo.

Para criar um recurso de AutoSoma em seu conjunto de dados, assim com o Excel implementa, veja mais no post SQL Server – Como criar uma AutoSoma (igual do Excel) utilizando Window functions

Base de Testes

Para os exemplos desse post, vou disponibilizar a base abaixo para que possamos criar um ambiente de testes e demonstrações.

Em um agrupamento de dados simples, apenas trazendo as quantidades filtrando por categoria e produto, conseguimos retornar essa visĂ£o utilizando a consulta abaixo:

Sumarizando os valores

Mas e se vocĂª precisar agrupar os dados criando um totalizador de cada categoria e no final e um totalizador geral? Como vocĂª faria?

Utilizando UNION ALL

Uma forma tradicional de se atender essa necessidade, Ă© utilizando subquery com UNION ALL, de forma que vocĂª retorne 3 resultsets contendo os dados analĂ­ticos, os totalizadores por categoria e depois a soma geral. Quanto maior o nĂºmero de filtros, mais complexo serĂ¡ a sua subquery, pois precisarĂ¡ de mais consultas a cada nĂ­vel.

Utilizando GROUP BY ROLLUP

Uma forma muito simples e prĂ¡tica de se resolver esse problema Ă© utilizando a funĂ§Ă£o ROLLUP() no GROUP BY, que jĂ¡ cria os agrupamentos e sumarizações de acordo com as colunas agrupadas na funĂ§Ă£o.

Utilizando essa funĂ§Ă£o, vocĂª verĂ¡ que ela cria os totalizadores logo abaixo de cada agrupamento e o totalizador geral na Ăºltima linha do resultset.

Exemplo 1:

Como vocĂªs devem ter notado, as linhas contendo as sumarizações possuem o valor NULL nas colunas agrupadas. Neste exemplo, o subtotal por categoria possui a coluna Ds_Produto com o valor NULL e no total geral, as duas colunas possuem valor NULL. Para que a nossa consulta produza o mesmo resultado da outra consulta com UNION ALL, basta tratarmos esses valores NULL, conforme demonstro abaixo:

Exemplo 2:

Utilizando GROUP BY CUBE

Assim como a funĂ§Ă£o ROLLUP(), a funĂ§Ă£o CUBE() permite criar totalizadores agrupados de uma forma muito prĂ¡tica e fĂ¡cil e sua sintaxe Ă© igual ao da funĂ§Ă£o ROLLUP.

Utilizando essa funĂ§Ă£o, vocĂª verĂ¡ que a diferença dela para a ROLLUP, Ă© que a funĂ§Ă£o CUBE cria os totalizadores para cada tipo de agrupamento.

Exemplo:

Vejam que utilizando a funĂ§Ă£o CUBE(), eu consigo obter o totalizador geral, o totalizador da coluna Mes_Venda e o totalizador da coluna Categoria. Ou seja, na funĂ§Ă£o CUBE, ele gera todas as possibilidades de combinaĂ§Ă£o entre as colunas utilizadas na funĂ§Ă£o.

Utilizando GROUP BY GROUPING SETS

Utilizando a funĂ§Ă£o GROUPING SETS no GROUP BY nos possibilita gerar totalizadores dos nossos dados utilizando as colunas inseridas na funĂ§Ă£o, de forma que ela gere totalizadores diferentes em uma Ăºnica consulta. Diferente das funções ROLLUP e CUBE, a funĂ§Ă£o GROUPING SETS nĂ£o retorna um totalizador geral.

Exemplo 1:

Neste exemplo, a funĂ§Ă£o GROUPING SETS nos retorna o totalizador por produto e depois o totalizador por categoria, onde vocĂª precisaria executar 2 consultas para mostrar essa mesma visĂ£o.

Exemplo 2:

Neste exemplo, a funĂ§Ă£o GROUPING SETS retornou a soma dos valores agrupados pelas 3 colunas que utilizei na funĂ§Ă£o: Soma por Mes_Venda, Soma por Produto e Soma por Categoria. Para reproduzir esse mesmo resultado, eu precisaria criar uma query com 3 consultas. Caso fossem 10 colunas, a query precisaria de 10 consultas diferentes para trazer esse resultado, o que conseguimos com uma Ăºnica consulta utilizando a funĂ§Ă£o GROUPING SETS.

Obs: Todas as 3 funções apresentadas nesse post aceitam N colunas como parĂ¢metros, nĂ£o sendo limitadas a apenas 2 colunas.

Desempenho e Performance

Agora que jĂ¡ vimos como funcionam as 3 funções e vimos o quĂ£o prĂ¡tica Ă© sua utilizaĂ§Ă£o, economizando muitas linhas de cĂ³digo, vamos analisar se elas sĂ£o performĂ¡ticas ou nĂ£o.

Vou procurar utilizar a mesma query para todos os casos, embora em alguns, o resultado (output) seja diferente, pois realmente tem objetivos diferentes, mas apenas para demonstrar o custo e plano de execuĂ§Ă£o de cada um.

UNION ALL (Query do exemplo):
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(19 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#Produtos’. Scan count 2, logical reads 204, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#Vendas’. Scan count 3, logical reads 64, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 79 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

GROUP BY ROLLUP (Query do exemplo 1)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(19 row(s) affected)
Table ‘#Vendas’. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#Produtos’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 57 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

GROUP BY CUBE (Query do exemplo 1)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.

(49 row(s) affected)
Table ‘Worktable’. Scan count 2, logical reads 71, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#Produtos’. Scan count 0, logical reads 200, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#Vendas’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 210 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

GROUP BY GROUPING SETS (query do exemplo 1)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 5 ms.

(18 row(s) affected)
Table ‘Worktable’. Scan count 2, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#Vendas’. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#Produtos’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 58 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Executando as 4 queries juntas:
Plano de execuĂ§Ă£o do SQL Sentry Plan Explorer

Plano de execuĂ§Ă£o do SQL Server Management Studio

Ou seja, com esses testes e utilizando essa massa de dados, podemos dizer que as consultas realizadas com essas funções sĂ£o mais performĂ¡ticas do que utilizar subquery com vĂ¡rias consultas para retornar os dados agrupados. Isso nĂ£o quer dizer que essa afirmaĂ§Ă£o serĂ¡ sempre verdade, vai depender muito do seu ambiente e da sua massa de dados.

Espero que tenham gostado desse post.
Um abraço!