Hello people,
How are you ?
Today I am going to introduce an interesting feature of SQL Server that most people do not know about, which is the use of Window functions to perform cumulative calculations on a SQL Server result set, similar to Excel's Auto Sum feature.
This need arose for me through a request similar to the one I will submit in this post, where we have a dated sales base and want to select the months where the year's cumulative sales amount was less than 40. If this value is reached during the month, this month should not be selected.
To come up with this information, I thought of grouping the data by month and creating an cumulative sum of sales quantity. With a small data mass, we can create a WHILE loop to traverse all rows in the table, retrieve the amount of that row, add a variable, and update this record again to do the sum, but when we apply it to a table with millions of records, This process may take hours to process.
If you want to know a few more different ways to group data, visit the post.SQL Server - Grouping Data Using ROLLUP, CUBE, and GROUPING SETS.
Test Base
Use this script to assemble the test base for this 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 |
Populated Sales Table Example:
Implementing the AutoSum Feature in SQL Server
To implement the AutoSum feature in SQL Server, we will use the SUM () aggregate function in conjunction with a Window function, as well as some parameters specially designed for this need.
If you want to implement this using the WHILE solution I mentioned above, you can do it as shown below. I needed to do something similar on a table of 1.4 million records and by my calculations, it was going to take a few hours just to process it, which eventually made me look for a new solution.
| 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 |
Example of Populated Grouped Sales Table:
To do this much more quickly and conveniently, let's use an aggregate function and Window function. In the example I gave above, in a table of 1.4 million records (already grouped), this query took 8 seconds to process.
| 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 |
Result:
That's it, guys.
I hope you enjoyed this post.
Hug!
SQL Server - How to create an AutoSum autosum (same as Excel) like excel excel-like using Window functions running totals running totals
SQL Server - How to create an AutoSum autosum (same as Excel) like excel excel-like using Window functions running totals running totals
Hello, I have a sales forecast table of MicroSiga, there are several orders of the same product for the same day, I need to create a column accumulated, but it must be in the select or as you did above, tried to adapt but unfortunately could not. I created in select itself using a sum, with a product it does fast, but when I use it in the whole table is locking the database, so I saw you work in a very different and fast way, if you can help me, if you need I can send you the file with the data.