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.

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.

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.

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