如何在T-SQL中实现逐行累积计算（Val1+Val3-Val2）

需求说明：

• 现有每日数据表格，包含day、Val1、Val2、Val3字段
• 递推规则：首行Val1使用初始值，后续每行Val1 = 上一行计算后的Val1 + 当前行Val3 - 当前行Val2

初始表格 #

day   Val1    Val2  Val3
1    20230310    100     50   0
2    20230311    NaN     10   3
3    20230312    NaN      5   2
4    20230313    NaN     -3   1
5    20230314    NaN     40   0

期望结果表格 #

day   Val1    Val2  Val3
1    20230310    100     10    0
2    20230311     53     50    3
3    20230312     50      5    2
4    20230313     54     -3    1
5    20230314     14     40    0

计算逻辑示例 #

day   Val1        Val2  Val3
1    20230310    100         10    0
2    20230311    100-50+3=53   50    3
3    20230312    53-5+2=50      5    2
4    20230313    50-(-3)+1=54  -3    1
5    20230314    54-40=14     40    0

T-SQL实现代码 #

使用递归CTE可以直观实现这种行递推计算，代码如下：

WITH RecursiveCTE AS (
    -- 锚点成员：获取首行数据，保留初始Val1值
    SELECT 
        day,
        Val1,
        Val2,
        Val3
    FROM YourTableName
    WHERE day = (SELECT MIN(day) FROM YourTableName) -- 按日期取首行，确保day字段有序唯一

    UNION ALL

    -- 递归成员：按日期顺序递推计算后续行的Val1
    SELECT 
        t.day,
        r.Val1 + t.Val3 - t.Val2 AS Val1,
        t.Val2,
        t.Val3
    FROM YourTableName t
    INNER JOIN RecursiveCTE r 
        ON t.day = (SELECT MIN(day) FROM YourTableName WHERE day > r.day)
)
SELECT * FROM RecursiveCTE ORDER BY day;

注意事项 #

1. 将代码中的YourTableName替换为实际的表格名称
2. 确保day字段是唯一且可排序的（如日期类型、整数类型），若需按其他逻辑排序，可调整锚点和递归部分的筛选条件
3. 若数据量较大，递归CTE性能不足时，可尝试基于窗口函数的累积计算方案，递推公式可转换为：初始Val1 + SUM(Val3 - Val2) OVER(ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - (Val3 - Val2)（首行需单独处理）

内容的提问来源于stack exchange，提问作者Ulewsky