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: 14.508 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!