T-SQL 函数，如 "filter" 用于 sum(x) filter(condition) over (分区依据)

Question

我正在尝试用过滤器对窗口求和。我看到了类似的东西sum(x) filter(condition) over (partition by...)但它似乎不适用于t-sql，SQL Server 2017。

从本质上讲，我想对在另一列上有条件的最后 5 行求和。

我试过了sum(case when condition...) over (partition...)和sum(cast(nullif(x))) over (partition...).

我尝试使用where条件向左连接表以过滤掉条件。

以上所有内容都将添加当前行起点的最后 5 个条件。

我想要的是当前行。将上面满足条件的最后 5 个值相加。

Date| Value | Condition | Result
1-1   10      1          
1-2   11      1 
1-3   12      1
1-4   13      1
1-5   14      0
1-6   15      1
1-7   16      0
1-8   17      0      sum(15+13+12+11+10)
1-9   18      1      sum(18+15+13+12+11)
1-10  19      1      sum(19+18+15+13+12)

在上面的例子中，我想要的条件是 1，忽略 0，但仍然让"窗口"大小为 5 个非 0 值。

Answer 1

这可以使用相关的子查询轻松实现：

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

DECLARE @T AS TABLE
(
[Date] Date, 
[Value] int, 
Condition bit
)
INSERT INTO @T ([Date], [Value], Condition) VALUES
('2019-01-01', 10, 1),
('2019-01-02', 11, 1),
('2019-01-03', 12, 1),
('2019-01-04', 13, 1),
('2019-01-05', 14, 0),
('2019-01-06', 15, 1),
('2019-01-07', 16, 0),
('2019-01-08', 17, 0),
('2019-01-09', 18, 1),
('2019-01-10', 19, 1)

查询：

SELECT [Date], [Value], Condition,
(
SELECT Sum([Value])
FROM 
(
SELECT TOP 5 [Value] 
FROM @T AS t1
WHERE Condition = 1
AND t1.[Date] <= t0.[Date]
-- If you want the sum to appear starting from a specific date, unremark the next row
--AND t0.[Date] >  '2019-01-07'
ORDER BY [Date] DESC                 
) As t2
HAVING COUNT(*) = 5 -- there are at least 5 rows meeting the condition
) As Result
FROM @T As T0

结果：

Date        Value   Condition   Result
2019-01-01  10      1           
2019-01-02  11      1           
2019-01-03  12      1           
2019-01-04  13      1           
2019-01-05  14      0           
2019-01-06  15      1           61
2019-01-07  16      0           61
2019-01-08  17      0           61
2019-01-09  18      1           69
2019-01-10  19      1           77