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 |
Resultado:
É 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.