Snowflake中如何移除同一ID分组内与前一行ACTIVE_STATUS值相同的行

我来帮你搞定这个需求！你想要在每个ID的分组内，只保留ACTIVE_STATUS发生变化的行（以及每个分组的第一行），这个场景正好适合用窗口函数LAG()来实现，你当前的GROUP BY语句没办法达到目的，因为它只是按三个字段分组，没法对比前后行的状态。

解决方案代码 #

SELECT ID, ACTIVE_STATUS, DATE
FROM (
    SELECT 
        ID, 
        ACTIVE_STATUS, 
        DATE,
        -- 按ID分区，按日期排序，获取前一行的ACTIVE_STATUS值
        LAG(ACTIVE_STATUS) OVER (PARTITION BY ID ORDER BY DATE) AS prev_status
    FROM MY_TABLE
) AS filtered_data
-- 筛选条件：要么是分组的第一行（无前一行，prev_status为NULL），要么当前状态和前一行不同
WHERE prev_status IS NULL OR ACTIVE_STATUS != prev_status
-- 最后按ID和日期排序，保证结果有序
ORDER BY ID, DATE;

代码逻辑说明 #

1. 内层子查询：使用LAG()窗口函数，按ID分区（把同一个ID的行归为一组），再按DATE排序（保证行的顺序是时间先后），这样就能拿到每一行的前一行对应的ACTIVE_STATUS，存为prev_status。
2. 外层查询：过滤掉那些当前ACTIVE_STATUS和prev_status相同的行，只保留两种情况：
   • 分组的第一行（此时prev_status为NULL，因为没有前一行）
   • 当前行状态和前一行不同的行（状态发生了变化）

验证结果 #

这个查询会完全符合你的期望输出：

• 对于ID=45：保留2022-06-12（第一行）和2022-07-01（状态从TRUE变为FALSE），去掉2022-06-13（和前一行状态相同）
• 对于ID=36：保留2022-08-01（第一行）、2022-08-02（状态从TRUE变为FALSE）、2022-08-15（状态从FALSE变为TRUE），去掉2022-08-14（和前一行状态相同）
• 对于ID=14：只保留2022-03-25（第一行），后续两行状态都是TRUE，全部被过滤

为什么你当前的GROUP BY不行？ #

你写的SELECT ID, ACTIVE_STATUS, DATE FROM MY_TABLE GROUP BY ID, ACTIVE_STATUS, DATE ORDER BY DATE，本质上是把每一行都保留下来（因为每个行的DATE都是唯一的），完全没法实现“去掉连续相同状态行”的需求，必须用窗口函数来对比前后行的状态差异。

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