Snowflake中按设备ID和日期将状态列首行设为P的实现方案

需求回顾 #

现有视图包含Device ID、Date、Op Cost字段，需实现：

• 按Device_ID分组，按日期升序排序
• 每组首行的Status固定为'P'
• 后续行中，若当前行Op Cost大于前一行值，Status设为'P'，否则为'I'

原SQL存在两个问题：首行Status未固定为'P'，且判断逻辑与需求相反。

修正后的SQL代码 #

SELECT
    Device_ID, 
    Date,
    Op_Cost,
    LAG(Op_Cost) OVER (PARTITION BY Device_ID ORDER BY Date ASC) AS Lag_Op_Cost,
    ROW_NUMBER() OVER (PARTITION BY Device_ID ORDER BY Date ASC) AS RN,
    CASE 
        WHEN RN = 1 THEN 'P'
        WHEN Op_Cost > Lag_Op_Cost THEN 'P'
        ELSE 'I'
    END AS Status
FROM
    Device_Details;

关键修改点 #

1. 首行处理：利用已生成的RN（行号）判断首行（RN = 1），直接将Status设为'P'，无需额外子查询或CTE
2. 逻辑修正：原代码中Lag_Op_Cost - Op_Cost > 0等价于Op_Cost < Lag_Op_Cost，和需求中“当前行Op Cost大于前一行则设为P”的逻辑完全相反，修正为Op_Cost > Lag_Op_Cost
3. 兼容生产环境：所有修改都在原查询的CASE语句内完成，未使用CTE或中间视图，符合环境限制

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