SQL Server中的滑动窗口函数，高级计算

Question

例如，我有一个问题很容易在C#代码中解决，但我不知道如何在SQL查询中编写。

情况如下：假设我有一个有3列的表(ID, Date, Amount)，这里有一些数据：

ID  Date         Amount
-----------------------
1   01.01.2016    -500
2   01.02.2016    1000
3   01.03.2016    -200
4   01.04.2016     300
5   01.05.2016     500
6   01.06.2016    1000
7   01.07.2016    -100
8   01.08.2016     200

我想从表中得到的结果是(ID, Amount .... Order By Date)：

ID  Amount
-----------------------
2    300
4    300
5    500
6    900
8    200

这个想法是将金额分为几期，但问题是，当负金额开始发挥作用时，你需要从最后一期中删除金额。我不知道我有多清楚，所以这里有一个例子：

假设我有3张发票，金额分别是500、200、-300。

如果我开始分发这些发票，首先我会分发500，然后是200。但当我到了第三张发票-300时，我需要从最后一张发票中删除。在其他工作中，200-300=-100，因此第二张发票中的金额将消失，但仍有-100需要从第一张发票中减去。所以500-100=400。我需要的结果是具有一行的数据集(金额为400的第一张发票)

另一个例子是，第一张发票的金额为负数(-500300500)。在这种情况下，第一张(-500)发票将使第二张发票消失，另一张200将从第三张发票中减去。因此，结果将是：第三张发票，金额为300。

这有点像编程语言中的Stack实现，但我需要在SQL Server中使用滑动窗口函数。

如果有人有任何想法，请分享。

谢谢。

Answer 1

我使用TSQL解决了它。但我认为这个任务也可以用递归CTE来解决。我使用ID查找上一行或下一行。

-- create and fill test table
CREATE TABLE Invoices(
ID int,
[Date] date,
Amount float
)
INSERT Invoices(ID,Date,Amount) VALUES
(1,'20160101', -500),
(2,'20160201', 1000),
(3,'20160301', -200),
(4,'20160401',  300),
(5,'20160501',  500),
(6,'20160601', 1000),
(7,'20160701', -100),
(8,'20160801',  200)

我的解决方案

-- copy all the data into temp table
SELECT *
INTO #Invoices
FROM Invoices
DECLARE
@nID int,
@nAmount float,
@pID int
-- run infinity loop
WHILE 1=1
BEGIN
-- set all the variables to NULL
SET @nID=NULL
SET @nAmount=NULL
SET @pID=NULL
-- get data from the last negative row
SELECT
@nID=ID,
@nAmount=Amount
FROM
(
SELECT TOP 1 *
FROM #Invoices
WHERE Amount<0
ORDER BY ID DESC
) q
-- get prev positive row
SELECT @pID=ID
FROM
(
SELECT TOP 1 *
FROM #Invoices
WHERE ID<@nID
AND Amount>0
ORDER BY ID DESC
) q
IF(@pID IS NULL)
BEGIN
-- get next positive row
SELECT @pID=ID
FROM
(
SELECT TOP 1 *
FROM #Invoices
WHERE ID>@nID
AND Amount>0
ORDER BY ID
) q
END
-- exit from loop
IF(@pID IS NULL) BREAK
-- substract amount from positive row
UPDATE #Invoices
SET
Amount+=@nAmount
WHERE ID=@pID
-- delete used negative row
DELETE #Invoices
WHERE ID=@nID
END
-- show result
SELECT *
FROM #Invoices
DROP TABLE #Invoices