在 SQL Server 2012 中使用 OVER 子句的 30 天移动总和

Question

我正在尝试在SQL Server 2012中编写一个视图，在其中计算一组事务的30天移动总和。

当前脚本：

SELECT CustNo, TransactionDate, TransactionAmount, SUM(TransactionAmount) 
OVER (PARTITION BY CustNo ORDER BY TransactionDate) AS MovingAmount
FROM         dbo.TransactionData

数据集：

CustNo     TransactionDate    TransactionAmount
1111       5/7/2015           3,000
1111       5/14/2015          3,000
2222       5/17/2015          100
1111       5/21/2015          3,000
1111       5/28/2015          3,000
3333       5/31/2015          11,000
1111       6/10/2015          3,000

预期成果：

CustNo     TransactionDate    TransactionAmount   MovingAmount
1111       5/7/2015           3,000               12,000 
1111       5/14/2015          3,000               12,000
2222       5/17/2015          100                 100
1111       5/21/2015          3,000               9,000
1111       5/28/2015          3,000               6,000
3333       5/31/2015          11,000              11,000
1111       6/10/2015          3,000               3,000  

其他尝试：

SELECT CustNo, TransactionDate, TransactionAmount, SUM(TransactionAmount) 
OVER (PARTITION BY CustomerNumber ORDER BY TransactionDate, 
BETWEEN TransactionDate AND DATEADD(day, 30, TransactionDate)) 
AS MovingAmount
FROM         dbo.TransactionData

Answer 1

我假设您想要的结果的第一行中的 12,000 是一个错字，它应该是 3,000。 如果我是对的，您的第一个查询似乎工作正常，如果不对整个查询进行order by，就很难看到：

创建并填充示例表(请在以后的问题中保存此步骤(

CREATE TABLE TransactionData 
(
CustNo int,
TransactionDate date,
TransactionAmount int
)
INSERT INTO TransactionData(CustNo, TransactionDate, TransactionAmount) VALUES
(1111, '2015-05-07', 3000),
(1111, '2015-05-14', 3000),
(2222, '2015-05-17', 100),
(1111, '2015-05-21', 3000),
(1111, '2015-05-28', 3000),
(3333, '2015-05-31', 11000),
(1111, '2015-06-10', 3000)

按照 Tab Alleman 对问题的解释，不能用 over 子句来完成，你需要使用相关的子查询：

SELECT  CustNo, 
TransactionDate, 
TransactionAmount, 
(SELECT SUM(TransactionAmount) 
FROM dbo.TransactionData t1 
WHERE t1.CustNo = t0.CustNo
AND t1.TransactionDate >= t0.TransactionDate
AND t1.TransactionDate <= DATEADD(DAY, 30, t0.TransactionDate)) As MovingAmount
FROM dbo.TransactionData t0
ORDER BY CustNo, TransactionDate

结果：

CustNo  TransactionDate         TransactionAmount   MovingAmount
1111    07.05.2015 00:00:00     3000                12000
1111    14.05.2015 00:00:00     3000                12000
1111    21.05.2015 00:00:00     3000                9000
1111    28.05.2015 00:00:00     3000                6000
1111    10.06.2015 00:00:00     3000                3000
2222    17.05.2015 00:00:00     100                 100
3333    31.05.2015 00:00:00     11000               11000

您可以在rextester上看到现场演示。