Dicas T-SQL – Soma cumulativa

Vamos apresentar hoje mais uma dica de escrita de código para resolver problemas comuns no dia a dia de quem escreve consultas em T-SQL.

Imagine que você precisa consultar o valor das vendas acumuladas por ano/mês de um período. Uma possibilidade bastante comum é calcular a venda de cada ano/mês e depois realizar uma subconsulta para calcular o valor acumulado, algo semelhante a isto:

Vendas por ano/mês:

SELECT 
  CONVERT(VARCHAR(6), H.OrderDate, 112) AS AnoMes
  ,SUM((D.UnitPrice - D.UnitPriceDiscount) * D.OrderQty)AS Total 
FROM Sales.SalesOrderDetail D
JOIN Sales.SalesOrderHeader H ON D.SalesOrderID = H.SalesOrderID
WHERE H.OrderDate BETWEEN '20130101' AND '20131231'
GROUP BY CONVERT(VARCHAR(6), H.OrderDate, 112)
ORDER BY AnoMes;

Acumulado como subconsulta:

WITH CTE AS (
  SELECT 
    CONVERT(VARCHAR(6), H.OrderDate, 112) AS AnoMes
    ,SUM((D.UnitPrice - D.UnitPriceDiscount) * D.OrderQty) AS Total 
  FROM Sales.SalesOrderDetail D
  JOIN Sales.SalesOrderHeader H ON D.SalesOrderID = H.SalesOrderID
  WHERE H.OrderDate BETWEEN '20130101' AND '20131231'
  GROUP BY CONVERT(VARCHAR(6), H.OrderDate, 112)
)
SELECT A.AnoMes
  ,A.Total
  ,(SELECT SUM(B.Total) FROM CTE B WHERE B.AnoMes <= A.AnoMes) AS Acumulado
FROM CTE A
ORDER BY AnoMes;

Embora a consulta acima retorne o resultado esperado, notamos que o tempo de execução dela é elevado, principalmente se aumentarmos o período. Mas como podemos melhorar?

Podemos usar o conceito de Window Function. Não vamos entrar em muitos detalhes sobre isto agora, mas vamos aplicar o conceito em nossa consulta de teste.

SELECT 
  CONVERT(VARCHAR(6), H.OrderDate, 112) AS AnoMes
  ,SUM((D.UnitPrice - D.UnitPriceDiscount) * D.OrderQty) AS Total 
  ,SUM(SUM((D.UnitPrice - D.UnitPriceDiscount) * D.OrderQty)) OVER (ORDER BY CONVERT(VARCHAR(6), H.OrderDate, 112)) AS Acumulado
FROM Sales.SalesOrderDetail D
JOIN Sales.SalesOrderHeader H ON D.SalesOrderID = H.SalesOrderID
WHERE H.OrderDate BETWEEN '20130101' AND '20131231'
GROUP BY CONVERT(VARCHAR(6), H.OrderDate, 112)
ORDER BY AnoMes
;

Esta nova consulta produz o mesmo resultado da primeira, porém com menor tempo de execução. O segredo está na utilização da cláusula OVER com ORDER BY no operador de agregação SUM. Quando fazemos isto, estamos dizendo ao SQL para realizar uma soma cumulativa.

Para os testes utilizei a base AdventureWorks2017, base de exemplo disponibilizada pela Microsoft.

Dúvida sobre como utilizar essa abordagem? Deixe um comentário.

Links úteis:

https://docs.microsoft.com/pt-br/sql/samples/adventureworks-install-configure https://docs.microsoft.com/pt-br/sql/t-sql/queries/select-over-clause-transact-sql

Deixe um comentário Cancelar resposta