Snowflake按规则生成latest_status列的SQL实现求助

需求说明 #

现有Snowflake表包含ref_id、ord_id、status字段，每个ref_id对应多条ord_id记录及状态，需新增latest_status列，规则如下：

• 优先取每个ref_id下ord_id最大的记录的status
• 若最大ord_id的status为null，且第二大ord_id的status为Active，则latest_status取Active
• 其他情况（如最大ord_id为null但第二大状态非Active），latest_status为null

原表数据 #

预期结果 #

错误尝试SQL #

select distinct ord_id,status,
IFF(status is NULL,NTH_VALUE(IFF(status IN ('Active','Renew'),status,null),2)OVER(PARTITION BY ref_id ORDER BY ord_id desc),
    FIRST_VALUE(status)
    OVER(PARTITION BY ref_id ORDER BY ord_id DESC))as latest_status from table where ref_id='R2'

错误原因 #

该SQL逻辑偏差：用当前行的status是否为null作为判断条件，但需求是基于每个ref_id下最大ord_id记录的状态来判断，而非当前行状态；同时NTH_VALUE未正确定位到第二大ord_id的有效状态。

正确SQL实现 #

方案一：基于窗口函数提取前序状态 #

WITH ranked_data AS (
    SELECT 
        ref_id,
        ord_id,
        status,
        -- 获取当前ref_id下ord_id最大的状态
        FIRST_VALUE(status) OVER (PARTITION BY ref_id ORDER BY ord_id DESC) AS top1_status,
        -- 获取当前ref_id下，第二大ord_id的Active状态（若存在）
        FIRST_VALUE(CASE WHEN status = 'Active' THEN status END) OVER (
            PARTITION BY ref_id 
            ORDER BY ord_id DESC 
            ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
        ) AS top2_active_status
    FROM your_table_name
)
SELECT 
    ref_id,
    ord_id,
    status,
    CASE
        WHEN top1_status IS NOT NULL THEN top1_status
        WHEN top2_active_status = 'Active' THEN 'Active'
        ELSE NULL
    END AS latest_status
FROM ranked_data
ORDER BY ref_id, ord_id DESC;

方案二：直接用条件窗口函数判断 #

SELECT
    ref_id,
    ord_id,
    status,
    CASE
        -- 规则1：取最大ord_id的状态
        WHEN FIRST_VALUE(status) OVER (PARTITION BY ref_id ORDER BY ord_id DESC) IS NOT NULL
            THEN FIRST_VALUE(status) OVER (PARTITION BY ref_id ORDER BY ord_id DESC)
        -- 规则2：最大ord_id状态为null时，检查第二大ord_id是否为Active
        WHEN NTH_VALUE(CASE WHEN status = 'Active' THEN status END, 1) OVER (
            PARTITION BY ref_id 
            ORDER BY ord_id DESC 
            ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
        ) = 'Active'
            THEN 'Active'
        ELSE NULL
    END AS latest_status
FROM your_table_name
ORDER BY ref_id, ord_id DESC;

说明 #

两个方案均先通过窗口函数获取每个ref_id下的关键状态值，再通过CASE语句匹配需求规则，确保逻辑符合预期。注意替换your_table_name为实际表名。

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