在SQL Server中计算递减累积和

Question

我试图实现的是非负列的累积和，其中每行减少1，但结果也必须是非负的。

例如，对于下表，对"ID"列排序的"VALUE"列求和：

| ID | VALUE    |
-----------------
|  1 |        0 |
|  2 |        0 |
|  3 |        2 |
|  4 |        0 |
|  5 |        0 |
|  6 |        3 |
|  7 |        0 |
|  8 |        2 |
|  9 |        0 |
| 10 |        0 |
| 11 |        0 |
| 12 |        0 |

我期望：

| ID | VALUE    | SUM   |
-------------------------
|  1 |        0 |     0 |
|  2 |        0 |     0 |
|  3 |        2 |     2 |
|  4 |        0 |     1 |
|  5 |        0 |     0 |
|  6 |        3 |     3 |
|  7 |        0 |     2 |
|  8 |        2 |     3 |
|  9 |        0 |     2 |
| 10 |        0 |     1 |
| 11 |        0 |     0 |
| 12 |        0 |     0 |

Answer 1

您的问题描述得不太好。我最好的解释是，你想从正值开始倒计时，从下一个开始。

您可以使用非零值的累积和来定义组。然后在组上使用累积和：

select t.*,
(case when max(value) over (partition by grp) < row_number() over (partition by grp order by id) - 1
then 0
else (max(value) over (partition by grp) - 
(row_number() over (partition by grp order by id) - 1)
)
end) as my_value
from (select t.*,
sum(case when value <> 0 then 1 else 0 end) over (order by id) as grp
from t
) t

这是一个数据库<>不停摆弄

编辑：

我突然想到，你可能想保留所有的"正"值并倒计时——记住它们是否不降为零。唉，我认为在这种情况下最简单的方法是递归CTE：

with tn as (
select t.id, t.value, row_number() over (order by id) as seqnum
from t
),
cte as (
select tn.id, tn.value, tn.seqnum, tn.value as s
from tn
where id = 1
union all
select tn.id, tn.value, tn.seqnum,
(case when cte.s = 0
then tn.value
when tn.value = 0 and cte.s > 0
then cte.s - 1
--  when tn.value > 0 and cte.value > 0
else tn.value + cte.s - 1
end)
from cte join
tn 
on tn.seqnum = cte.seqnum + 1
)
select *
from cte;

数据库<>fiddle有两种解决方案。