OlĂ¡ pessoal,
Como vocĂªs estĂ£o ?
Hoje vou apresentar um recurso interessante do SQL Server e que muita gente nĂ£o conhece, que Ă© o uso de Window functions para realizar cĂ¡lculos cumulativos em um result set no SQL Server, semelhantes ao recurso de Auto Soma do Excel.
Essa necessidade surgiu para mim atravĂ©s de uma solicitaĂ§Ă£o similar Ă que vou apresentar nesse post, onde temos uma base de vendas com data e quero selecionar os meses onde a quantidade de vendas acumulada do ano foi menor que 40. Caso esse valor seja atingido durante o mĂªs, esse mĂªs nĂ£o deve ser selecionado.
Para chegar a essa informaĂ§Ă£o, pensei em agrupar os dados por mĂªs e criar uma soma acumulativa da quantidade de vendas. Com uma massa de dados pequena, podemos criar um loop WHILE para percorrer todas as linhas da tabela, recuperar a quantidade daquela linha, somar numa variĂ¡vel e atualizar novamente esse registro pra fazer a soma, mas quando aplicamos isso numa tabela com milhões de registros, esse processo pode demorar horas para processar.
Caso vocĂª queira conhecer mais algumas formas diferentes de agrupar dados, acesse o postSQL Server – Agrupando dados utilizando ROLLUP, CUBE e GROUPING SETS.
Base de Testes
Utilize esse script para montar a base de testes para esse post.
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 50 51 |
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 SELECT * FROM #Vendas |
Exemplo de tabela de Vendas populada:
Implementando o recurso de AutoSoma no SQL Server
Para implementar o recurso de AutoSoma no SQL Server, vamos utilizar a funĂ§Ă£o de agregaĂ§Ă£o SUM() em conjunto com uma Window function, alĂ©m de alguns parĂ¢metros especialmente criados para essa necessidade.
Caso vocĂª queira implementar isso utilizando a soluĂ§Ă£o do WHILE que mencionei acima, vocĂª pode fazer da forma que demonstrei logo abaixo. Precisei fazer algo parecido em uma tabela de 1.4 milhões de registros e pelos meus cĂ¡lculos, ia demorar algumas horas sĂ³ para processar isso, o que acabou me fazendo buscar uma nova soluĂ§Ă£o.
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 |
IF (OBJECT_ID('tempdb..#Vendas_Agrupadas') IS NOT NULL) DROP TABLE #Vendas_Agrupadas SELECT IDENTITY(INT, 1, 1) AS Ranking, CONVERT(VARCHAR(6), Dt_Venda, 112) AS Periodo, COUNT(*) AS Qt_Vendas_No_Mes, NULL AS Qt_Vendas_Acumuladas INTO #Vendas_Agrupadas FROM #Vendas GROUP BY CONVERT(VARCHAR(6), Dt_Venda, 112) DECLARE @Contador INT = 1, @Total INT = (SELECT COUNT(*) FROM #Vendas_Agrupadas), @Qt_Vendas_Acumuladas INT = 0, @Qt_Vendas_No_Mes INT = 0 WHILE(@Contador <= @Total) BEGIN SELECT @Qt_Vendas_No_Mes = Qt_Vendas_No_Mes FROM #Vendas_Agrupadas WHERE Ranking = @Contador SET @Qt_Vendas_Acumuladas += @Qt_Vendas_No_Mes UPDATE #Vendas_Agrupadas SET Qt_Vendas_Acumuladas = @Qt_Vendas_Acumuladas WHERE Ranking = @Contador SET @Contador += 1 END SELECT * FROM #Vendas_Agrupadas |
Exemplo de tabela de Vendas agrupada populada:
Para fazer isso de forma muito mais rĂ¡pida e prĂ¡tica, vamos utilizar uma funĂ§Ă£o de agregaĂ§Ă£o e Window function. No exemplo que dei acima, em uma tabela de 1.4 milhões de registros (jĂ¡ agrupados), essa query demorou 8 segundos para processar.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
IF (OBJECT_ID('tempdb..#Vendas_Agrupadas') IS NOT NULL) DROP TABLE #Vendas_Agrupadas SELECT CONVERT(VARCHAR(6), Dt_Venda, 112) AS Periodo, COUNT(*) AS Qt_Vendas_No_Mes, NULL AS Qt_Vendas_Acumuladas INTO #Vendas_Agrupadas FROM #Vendas GROUP BY CONVERT(VARCHAR(6), Dt_Venda, 112) SELECT Periodo, SUM(Qt_Vendas_No_Mes) OVER(ORDER BY Periodo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Qt_Vendas_Acumuladas FROM #Vendas_Agrupadas |
É isso aĂ, pessoal.
Espero que vocĂªs tenham gostado desse post.
Abraço!
SQL Server – Como criar uma AutoSoma autosum (igual do Excel) like excel excel-like utilizando Window functions running totals running totals
SQL Server – Como criar uma AutoSoma autosum (igual do Excel) like excel excel-like utilizando Window functions running totals running totals
OlĂ¡, tenho uma tabela de previsao de vendas do Microssiga, nela existem vĂ¡rios pedidos do mesmo produto para um mesmo dia, preciso criar uma coluna acumulado, mas precisa ser no select ou da forma como fez acima, tentei adaptar mas infelizmente nĂ£o consegui. Eu criei no prĂ³prio select usando uma soma, com um produto ela faz rĂ¡pido, mas quando uso na tabela inteira estĂ¡ travando o banco de dados, pelo que vi vocĂª trabalha de uma forma bem diferente e rĂ¡pido, se tiver como me ajudar, se precisar posso te enviar o arquivo com os dados.