基于关联条件标记数据列的SQL查询错误排查求助

输入数据 #

预期输出 #

规则逻辑 #

• 若status列值不为'Delay'，则flag为'N'
• 若status为'Delay'，需满足同一id下，rnk小于该Delay记录rnk的行中存在status为'Open'或'Close'，则flag为'Y'，否则为'N'

问题SQL #

你当前的SQL逻辑错误，导致所有flag均返回'N'：

SELECT 
    *, 
    CASE WHEN status != 'Delay' THEN 'N'
         WHEN rnk < (COALESCE(MAX(CASE WHEN status = 'Delay' THEN rnk ELSE -1 END) OVER(PARTITION BY id))) 
                         AND status IN ('Open','Close') THEN 'Y' 
         ELSE 'N'
    END AS flag 
FROM TABLE

错误原因：当处理status='Delay'的行时，你同时判断了当前行status IN ('Open','Close')，但当前行是Delay，这个条件永远不成立，所以永远返回'N'。

修正后的SQL #

以下是符合逻辑的正确SQL：

SELECT
    id,
    status,
    rnk,
    CASE
        -- 非Delay行直接标记N
        WHEN status != 'Delay' THEN 'N'
        -- 对Delay行，检查同id下rnk小于当前rnk的记录中是否存在Open/Close
        WHEN MAX(CASE WHEN status IN ('Open', 'Close') THEN 1 ELSE 0 END) OVER (
            PARTITION BY id
            ORDER BY rnk
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) = 1 THEN 'Y'
        ELSE 'N'
    END AS flag
FROM your_table_name;

逻辑说明 #

• 使用窗口函数MAX(...) OVER (...)，按id分区，按rnk排序，统计当前行之前（ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING）的所有行中，是否存在status为'Open'或'Close'的记录（存在则返回1，否则0）
• 若统计结果为1，说明满足条件，标记'Y'，否则标记'N'

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