如何在SQL中基于前序合格客户的missed状态计算目标列？

需求逻辑 #

要计算desired_behavior_prior_qualified_was_missed列，规则为：找到当前记录之前最近的一条qualified=1的记录，若该记录的missed值为1，则当前列赋值为1，否则为0。

解决方案SQL #

WITH data AS (
    SELECT 'a' AS customer, 'Jan 1' AS Period, 0 AS qualified, 0 AS missed
    UNION 
    SELECT 'b' AS customer, 'Jan 2' AS Period, 1 AS qualified, 1 AS missed
    UNION 
    SELECT 'c' AS customer, 'Jan 3' AS Period, 0 AS qualified, 0 AS missed
    UNION 
    SELECT 'd' AS customer, 'Jan 4' AS Period, 1 AS qualified, 0 AS missed
),
qualified_marks AS (
    SELECT 
        *,
        -- 提取当前记录前最近一条合格记录的missed值
        LAST_VALUE(CASE WHEN qualified = 1 THEN missed END IGNORE NULLS) 
            OVER (ORDER BY Period ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - 1) AS prior_qualified_missed
    FROM data
)
SELECT 
    customer,
    Period,
    qualified,
    missed,
    -- 根据前序合格记录的missed值生成目标列
    CASE WHEN prior_qualified_missed = 1 THEN 1 ELSE 0 END AS desired_behavior_prior_qualified_was_missed
FROM qualified_marks;

逻辑说明 #

1. 使用LAST_VALUE窗口函数结合IGNORE NULLS，按Period顺序筛选出当前记录之前最近一次qualified=1的记录的missed值，忽略非合格记录的NULL结果。
2. 最后通过CASE语句判断：若前序合格记录的missed为1，目标列设为1；无符合条件的前序合格记录或missed不为1时，目标列设为0。

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