基于特定行值条件为现有表新增Amount difference列的SQL查询

示例源表 #

需求说明 #

需新增名为Amount difference的列，规则如下：

• 当连续两行的D ticket status依次为"Success"和"Waiting"，且这两行的A Id、B session Id、C customer Type完全匹配时，"Success"行的该列值为自身E Amount减去下一行的E Amount，其余行值为NULL
• 若"Success"所在行的下一行不是"Waiting"（包括没有下一行的情况），则该行的该列值等于自身的E Amount，其余行值为NULL

SQL查询语句 #

SELECT
    `A Id`,
    `B session Id`,
    `C customer Type`,
    `D ticket status`,
    `E Amount`,
    ORDINAL,
    CASE
        -- 处理Success后紧跟同组Waiting的情况
        WHEN `D ticket status` = 'Success' 
             AND LEAD(`D ticket status`) OVER (PARTITION BY `A Id`, `B session Id`, `C customer Type` ORDER BY ORDINAL) = 'Waiting'
        THEN `E Amount` - LEAD(`E Amount`) OVER (PARTITION BY `A Id`, `B session Id`, `C customer Type` ORDER BY ORDINAL)
        -- 处理Success后非Waiting或无后续行的情况
        WHEN `D ticket status` = 'Success' 
             AND (LEAD(`D ticket status`) OVER (PARTITION BY `A Id`, `B session Id`, `C customer Type` ORDER BY ORDINAL) != 'Waiting'
                  OR LEAD(`D ticket status`) OVER (PARTITION BY `A Id`, `B session Id`, `C customer Type` ORDER BY ORDINAL) IS NULL)
        THEN `E Amount`
        -- 其余情况返回NULL
        ELSE NULL
    END AS `Amount difference`
FROM
    your_table_name;

说明 #

• 使用LEAD()窗口函数按A Id、B session Id、C customer Type分组，以ORDINAL排序，获取下一行的状态和金额数据
• 通过CASE分支判断不同场景，精准计算目标列的值
• 请将语句中的your_table_name替换为实际表名

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