编写SQL实现基于前序行值的表转换，Lag窗口函数无法满足需求求解

核心思路 #

你需要实现的是逐行递推计算逻辑，普通窗口函数LAG只能读取原始表的上一行字段值，无法读取前序计算生成的New value结果，因此需要用递归公共表表达式（CTE）实现需求。

实现步骤 #

• 第一步：预处理数据，过滤掉Type = 'D'的无效记录，同时给每个Type分组内的记录按Concat_date_hour升序排序生成行号，固定计算顺序
• 第二步：定义递归CTE的锚点成员，取每个Type分组内首行（行号=1）数据，Actual Value直接取原表Value值，New value按规则计算为首行Value+首行Next_Movement
• 第三步：定义递归成员，关联上一轮递归的结果和当前行的原始数据，当前行Actual Value直接取上一行的New value，再按规则计算当前行的New value

完整SQL代码 #

WITH ranked_data AS (
    -- 数据预处理：过滤+排序生成行号
    SELECT 
        Concat_date_hour,
        Type,
        Value,
        Next_Movement,
        ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Concat_date_hour ASC) AS rn
    FROM 你的原表名称
    WHERE Type != 'D' -- 过滤Type为D的记录
),
recursive_calc AS (
    -- 锚点：每个Type分组首行计算
    SELECT 
        Concat_date_hour,
        Type,
        Value AS `Actual Value`,
        Value + Next_Movement AS `New value`,
        rn
    FROM ranked_data
    WHERE rn = 1
    UNION ALL
    -- 递归：后续行依赖前序计算结果
    SELECT 
        curr.Concat_date_hour,
        curr.Type,
        prev.`New value` AS `Actual Value`,
        prev.`New value` + curr.Next_Movement AS `New value`,
        curr.rn
    FROM recursive_calc prev
    INNER JOIN ranked_data curr 
        ON prev.Type = curr.Type 
        AND prev.rn + 1 = curr.rn
)
-- 输出最终结果
SELECT Concat_date_hour, Type, `Actual Value`, `New value`
FROM recursive_calc
ORDER BY Type, Concat_date_hour ASC;

注意事项 #

• 上述SQL为标准SQL语法，支持PostgreSQL、SQL Server、MySQL 8.0+、Hive、Spark SQL等支持递归CTE的引擎
• 若Concat_date_hour存在同Type下重复的情况，需要补充其他唯一排序字段保证行号生成顺序稳定，避免计算结果出错

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