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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
IF (OBJECT_ID('tempdb..#Produtos') IS NOT NULL) DROP TABLE #Produtos CREATE TABLE #Produtos ( Codigo INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Ds_Produto VARCHAR(50) NOT NULL, Ds_Categoria VARCHAR(50) NOT NULL, Preco NUMERIC(18, 2) NOT NULL ) IF (OBJECT_ID('tempdb..#Vendas') IS NOT NULL) DROP TABLE #Vendas CREATE TABLE #Vendas ( Codigo INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Dt_Venda DATETIME NOT NULL, Cd_Produto INT NOT NULL ) INSERT INTO #Produtos ( Ds_Produto, Ds_Categoria, Preco ) VALUES ( 'Processador i7', 'InformĂ¡tica', 1500.00 ), ( 'Processador i5', 'InformĂ¡tica', 1000.00 ), ( 'Processador i3', 'InformĂ¡tica', 500.00 ), ( 'Placa de VĂdeo Nvidia', 'InformĂ¡tica', 2000.00 ), ( 'Placa de VĂdeo Radeon', 'InformĂ¡tica', 1500.00 ), ( 'Celular Apple', 'Celulares', 10000.00 ), ( 'Celular Samsung', 'Celulares', 2500.00 ), ( 'Celular Sony', 'Celulares', 4200.00 ), ( 'Celular LG', 'Celulares', 1000.00 ), ( 'Cama', 'Utilidades do Lar', 2000.00 ), ( 'Toalha', 'Utilidades do Lar', 40.00 ), ( 'Lençol', 'Utilidades do Lar', 60.00 ), ( 'Cadeira', 'Utilidades do Lar', 200.00 ), ( 'Mesa', 'Utilidades do Lar', 1000.00 ), ( 'Talheres', 'Utilidades do Lar', 50.00 ) DECLARE @Contador INT = 1, @Total INT = 100 WHILE(@Contador <= @Total) BEGIN INSERT INTO #Vendas ( Cd_Produto, Dt_Venda ) SELECT (SELECT TOP 1 Codigo FROM #Produtos ORDER BY NEWID()) AS Cd_Produto, DATEADD(DAY, (CAST(RAND() * 364 AS INT)), '2017-01-01') AS Dt_Venda SET @Contador += 1 END |
Em um agrupamento de dados simples, apenas trazendo as quantidades filtrando por categoria e produto, conseguimos retornar essa visĂ£o utilizando a consulta abaixo:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT B.Ds_Categoria, B.Ds_Produto, COUNT(*) AS Qt_Vendas, SUM(B.Preco) AS Vl_Total FROM #Vendas A JOIN #Produtos B ON A.Cd_Produto = B.Codigo GROUP BY B.Ds_Categoria, B.Ds_Produto ORDER BY 1, 2 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SELECT * FROM ( SELECT B.Ds_Categoria, B.Ds_Produto, COUNT(*) AS Qt_Vendas, SUM(B.Preco) AS Vl_Total FROM #Vendas A JOIN #Produtos B ON A.Cd_Produto = B.Codigo GROUP BY B.Ds_Categoria, B.Ds_Produto UNION ALL SELECT B.Ds_Categoria, 'Subtotal' AS Ds_Produto, COUNT(*) AS Qt_Vendas, SUM(B.Preco) AS Vl_Total FROM #Vendas A JOIN #Produtos B ON A.Cd_Produto = B.Codigo GROUP BY B.Ds_Categoria UNION ALL SELECT 'Total' AS Ds_Categoria, 'Total' AS Ds_Produto, COUNT(*) AS Qt_Vendas, SUM(B.Preco) AS Vl_Total FROM #Vendas A JOIN #Produtos B ON A.Cd_Produto = B.Codigo ) A ORDER BY (CASE WHEN A.Ds_Categoria = 'Total' THEN 1 ELSE 0 END), A.Ds_Categoria, (CASE WHEN A.Ds_Produto = 'Subtotal' THEN 1 ELSE 0 END), A.Ds_Produto |
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:
1 2 3 4 5 6 7 8 9 10 |
SELECT B.Ds_Categoria, B.Ds_Produto, COUNT(*) AS Qt_Vendas, SUM(B.Preco) AS Vl_Total FROM #Vendas A JOIN #Produtos B ON A.Cd_Produto = B.Codigo GROUP BY ROLLUP(B.Ds_Categoria, B.Ds_Produto) |
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:
1 2 3 4 5 6 7 8 9 10 |
SELECT ISNULL(B.Ds_Categoria, 'Total') AS Ds_Categoria, ISNULL(B.Ds_Produto, 'Subtotal') AS Ds_Produto, COUNT(*) AS Qt_Vendas, SUM(B.Preco) AS Vl_Total FROM #Vendas A JOIN #Produtos B ON A.Cd_Produto = B.Codigo GROUP BY ROLLUP(B.Ds_Categoria, B.Ds_Produto) |
Exemplo 2:
1 2 3 4 5 6 7 8 9 10 |
SELECT ISNULL(CONVERT(VARCHAR(10), MONTH(A.Dt_Venda)), 'Total') AS Mes_Venda, ISNULL(B.Ds_Categoria, 'Subtotal') AS Ds_Categoria, COUNT(*) AS Qt_Vendas, SUM(B.Preco) AS Vl_Total FROM #Vendas A JOIN #Produtos B ON A.Cd_Produto = B.Codigo GROUP BY ROLLUP(MONTH(A.Dt_Venda), B.Ds_Categoria) |
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:
1 2 3 4 5 6 7 8 9 10 |
SELECT ISNULL(CONVERT(VARCHAR(10), MONTH(A.Dt_Venda)), 'Total') AS Mes_Venda, ISNULL(B.Ds_Categoria, 'Subtotal') AS Ds_Categoria, COUNT(*) AS Qt_Vendas, SUM(B.Preco) AS Vl_Total FROM #Vendas A JOIN #Produtos B ON A.Cd_Produto = B.Codigo GROUP BY CUBE(MONTH(A.Dt_Venda), B.Ds_Categoria) |
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:
1 2 3 4 5 6 7 8 9 10 |
SELECT ISNULL(B.Ds_Produto, 'Total') AS Ds_Produto, ISNULL(B.Ds_Categoria, 'Subtotal') AS Ds_Categoria, COUNT(*) AS Qt_Vendas, SUM(B.Preco) AS Vl_Total FROM #Vendas A JOIN #Produtos B ON A.Cd_Produto = B.Codigo GROUP BY GROUPING SETS(B.Ds_Categoria, B.Ds_Produto) |
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:
1 2 3 4 5 6 7 8 9 10 11 |
SELECT MONTH(A.Dt_Venda) AS Mes_Venda, B.Ds_Categoria, B.Ds_Produto, COUNT(*) AS Qt_Vendas, SUM(B.Preco) AS Vl_Total FROM #Vendas A JOIN #Produtos B ON A.Cd_Produto = B.Codigo GROUP BY GROUPING SETS(MONTH(A.Dt_Venda), B.Ds_Categoria, B.Ds_Produto) |
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!
Excelente explicaĂ§Ă£o. Com os exemplos, as imagens e o passo a passo facilita muito a compreensĂ£o! Obrigado professor.
excelente conteĂºdo.
Muito bom o post. NĂ£o conhecia o comando. Vou passar a utilizar.
Ă“timo post Dirceu! Sempre usei o UNION ALL para fazer essas sumarizações por desconhecer esses comandos. Vou colocar em prĂ¡tica a partir de agora.
Abraço.
Luiz Vitor
Valeu pelo retorno, Luiz Vitor. TambĂ©m jĂ¡ usei muito Union all pra fazer isso.. Rs