如何用SQL的Lag函数实现累积计算以生成新列Z？

需求说明 #

我有一张包含Xdate、X、Y三列的表，需要生成新列Z，计算规则如下：

• 首行的Z等于当前行X与Y的乘积
• 后续行的Z等于（当前行X值 + 之前所有行的Z值总和）乘以当前行Y值

示例输入数据 #

Xdate          X     Y
2022-01-01     1     2
2022-01-02     2     2
2022-01-03     2     1
2022-01-04     3     1

预期输出结果 #

Xdate         X      Y     Z
2022-01-01    1      2     2 (1*2)
2022-01-02    2      2     8 ((2+2) * 2)
2022-01-03    2      1     12 ((2+8+2) * 1)
2022-01-04    3      1     25 ((3+12+8+2) * 1)

现有问题SQL #

我尝试了以下SQL，但无法实现正确逻辑：

select
    *, 
    Lag(X+Y, 1, X+Y) OVER(ORDER BY Xdate) AS Z
from temp

解决方案 #

这个需求属于递归依赖计算，每一行的Z值依赖于之前所有行的Z总和，需要使用递归CTE（Common Table Expression）来逐行计算：

WITH recursive cte AS (
    -- 锚点成员：取排序后的第一行，计算首行Z值
    SELECT 
        Xdate, 
        X, 
        Y, 
        CAST(X * Y AS BIGINT) AS Z,
        CAST(X * Y AS BIGINT) AS total_Z  -- 记录到当前行的Z累积总和
    FROM temp
    ORDER BY Xdate
    LIMIT 1

    UNION ALL

    -- 递归成员：逐行计算后续行的Z值
    SELECT 
        t.Xdate, 
        t.X, 
        t.Y,
        CAST((t.X + c.total_Z) * t.Y AS BIGINT) AS Z,
        CAST(c.total_Z + (t.X + c.total_Z) * t.Y AS BIGINT) AS total_Z
    FROM temp t
    JOIN cte c ON t.Xdate > c.Xdate
    -- 确保每次只关联到上一行，避免多对多关联
    WHERE NOT EXISTS (
        SELECT 1 
        FROM temp t2 
        WHERE t2.Xdate > c.Xdate AND t2.Xdate < t.Xdate
    )
)
SELECT Xdate, X, Y, Z
FROM cte
ORDER BY Xdate;

逻辑说明 #

1. 锚点成员：先取出按Xdate排序后的第一行，计算首行的Z值（X*Y），同时记录到当前行的Z累积总和total_Z
2. 递归成员：依次取出下一行，用之前所有行的Z累积总和（c.total_Z）加上当前行X，再乘以当前行Y得到新的Z，同时更新total_Z为之前的累积总和加上新计算的Z
3. 使用NOT EXISTS子句确保每次只关联到上一行，避免出现多对多关联导致的计算错误
4. 用CAST(... AS BIGINT)防止数值溢出，可根据实际数据类型调整

内容的提问来源于stack exchange，提问作者Gys---p