sql - 在 SQL Server 中使用最大和最小上限运行总和

Question

我有一张看起来像这样的 table

|ID1| ID2| Date       |count |
+---+----+------------+------+
|1  | 1  | 2019-07-24 | 3    | 
|1  | 1  | 2019-07-25 | 3    |
|1  | 1  | 2019-07-26 | 3    |
|1  | 1  | 2019-07-27 | 1    |
|1  | 1  | 2019-07-28 | -3   |
|1  | 2  | 2019-07-24 | 1    |
|1  | 2  | 2019-07-25 | -3   |
|1  | 2  | 2019-07-26 | 3    |
|1  | 2  | 2019-07-27 | 3    |
|1  | 2  | 2019-07-28 | 3    |

我有兴趣计算最小上限为 0 和最大上限为 8 的运行总和。结果表将如下所示。

|ID1| ID2| Date       |count |runningSum|
+---+----+------------+------+----------+
|1  | 1  | 2019-07-24 | 3    | 3        |
|1  | 1  | 2019-07-25 | 3    | 6        |
|1  | 1  | 2019-07-26 | 3    | 8        |
|1  | 1  | 2019-07-27 | 1    | 8        |
|1  | 1  | 2019-07-28 | -3   | 5        |
|1  | 2  | 2019-07-24 | 1    | 1        |
|1  | 2  | 2019-07-25 | -3   | 0        |
|1  | 2  | 2019-07-26 | 3    | 3        |
|1  | 2  | 2019-07-27 | 3    | 6        |
|1  | 2  | 2019-07-28 | 3    | 8        |

我知道 Oracle 有许多不同的解决方案来解决这个问题，比如
此处在第 7 条中描述
https://blog.jooq.org/2016/04/25/10-sql-tricks-that-you-didnt-think-were-possible/ . Microsoft SQL Server 是否存在像这样简单的事情。

请注意，我是 不是 允许创建表、临时表或表变量。

编辑 我正在使用 Azure 数据仓库，其中递归 CTE 和游标语句不可用。在 SQL Server 中真的没有其他方法可以解决这个问题吗？

Answer 1

我不认为你可以用窗口函数来做到这一点，唉。问题是上限引入了状态更改，因此您必须逐步处理行以获取给定行的值。

递归 CTE 进行迭代，因此它可以执行您想要的操作:

with t as (
      select t.*,
             row_number() over (partition by id1, id2 order by date) as seqnum
      from <yourtable> t
     ),
     cte as (
      select id1, id2, date, count,
             (case when count < 0 then 0
                   when count > 8 then 8
                   else count
              end) as runningsum,
             seqnum
      from t
      where seqnum = 1
      union all
      select cte.id1, cte.id2, t.date, t.count,
             (case when t.count + cte.runningsum < 0 then 0
                   when t.count + cte.runningsum > 8 then 8
                   else t.count + cte.runningsum
              end) as runningsum, t.seqnum
      from cte join
           t
           on t.seqnum = cte.seqnum + 1 and
              t.id1 = cte.id1 and t.id2 = cte.id2
     )
select *
from cte
order by id1, id2, date;

Here是一个db<> fiddle 。

请注意，非常相似的代码将适用于支持递归 CTE 的 Oracle 12C。在早期版本的 Oracle 中，您可以使用 connect by .