Preciso calcular uma soma contínua em um intervalo de datas. Para ilustrar, usando o banco de dados de exemplo AdventureWorks , a seguinte sintaxe hipotética faria exatamente o que eu preciso:
SELECT
TH.ProductID,
TH.TransactionDate,
TH.ActualCost,
RollingSum45 = SUM(TH.ActualCost) OVER (
PARTITION BY TH.ProductID
ORDER BY TH.TransactionDate
RANGE BETWEEN
INTERVAL 45 DAY PRECEDING
AND CURRENT ROW)
FROM Production.TransactionHistory AS TH
ORDER BY
TH.ProductID,
TH.TransactionDate,
TH.ReferenceOrderID;
Infelizmente, a RANGEextensão do quadro da janela não permite atualmente um intervalo no SQL Server.
Eu sei que posso escrever uma solução usando uma subconsulta e uma agregação regular (não janela):
SELECT
TH.ProductID,
TH.TransactionDate,
TH.ActualCost,
RollingSum45 =
(
SELECT SUM(TH2.ActualCost)
FROM Production.TransactionHistory AS TH2
WHERE
TH2.ProductID = TH.ProductID
AND TH2.TransactionDate <= TH.TransactionDate
AND TH2.TransactionDate >= DATEADD(DAY, -45, TH.TransactionDate)
)
FROM Production.TransactionHistory AS TH
ORDER BY
TH.ProductID,
TH.TransactionDate,
TH.ReferenceOrderID;
Dado o seguinte índice:
CREATE UNIQUE INDEX i
ON Production.TransactionHistory
(ProductID, TransactionDate, ReferenceOrderID)
INCLUDE
(ActualCost);
O plano de execução é:
Embora não seja terrivelmente ineficiente, parece que deve ser possível expressar essa consulta usando apenas funções analíticas e agregadas de janela com suporte no SQL Server 2012, 2014 ou 2016 (até agora).
Para maior clareza, estou procurando uma solução que execute uma única passagem pelos dados.
Em T-SQL, isso provavelmente significa que a OVERcláusula fará o trabalho e o plano de execução apresentará Spools de Janelas e Agregados de Janelas. Todos os elementos de linguagem que usam a OVERcláusula são um jogo justo. Uma solução SQLCLR é aceitável, desde que seja garantida a produção de resultados corretos.
Para soluções T-SQL, quanto menos Hashes, Classificações e Spools/Agregados de Janelas no plano de execução, melhor. Sinta-se à vontade para adicionar índices, mas estruturas separadas não são permitidas (portanto, nenhuma tabela pré-computada é mantida em sincronia com gatilhos, por exemplo). São permitidas tabelas de referência (tabelas de números, datas, etc.)
Idealmente, as soluções produzirão exatamente os mesmos resultados na mesma ordem que a versão da subconsulta acima, mas qualquer coisa comprovadamente correta também é aceitável. O desempenho é sempre uma consideração, portanto, as soluções devem ser pelo menos razoavelmente eficientes.
Sala de bate-papo dedicada: Criei uma sala de bate-papo pública para discussões relacionadas a esta pergunta e suas respostas. Qualquer usuário com pelo menos 20 pontos de reputação pode participar diretamente. Por favor, ping me em um comentário abaixo se você tem menos de 20 representantes e gostaria de participar.
