按用户ID筛选状态与上周不同的SQL数据行需求

问题说明 #

现有一张按周更新的用户状态表，数据如下：

user_id     date           status
101         2022-04-01     asleep
101         2022-04-08     asleep
101         2022-04-15     awake
102         2022-04-01     awake
102         2022-04-08     asleep
102         2022-04-15     asleep

需要筛选出每个用户状态与上周不同的记录，保留初始状态行，去掉连续相同状态的中间周记录，期望结果：

user_id     date           status
101         2022-04-01     asleep
101         2022-04-15     awake
102         2022-04-01     awake
102         2022-04-08     asleep

解决方案 #

使用窗口函数LAG()获取每个用户上一条记录的状态，通过对比筛选出状态变更或初始行：

SELECT user_id, date, status
FROM (
    SELECT 
        user_id,
        date,
        status,
        -- 按用户分组、日期排序，获取上一条状态
        LAG(status) OVER (PARTITION BY user_id ORDER BY date) AS prev_status
    FROM your_table_name -- 替换为你的表名
) t
-- 保留初始行（无上周状态）或状态与上周不同的行
WHERE prev_status IS NULL OR status != prev_status
ORDER BY user_id, date;

逻辑说明 #

1. 窗口函数LAG()：按user_id分组，每组内按date升序排序，获取当前行的上一行status值，命名为prev_status
2. 筛选条件：
   • prev_status IS NULL：表示该用户的第一条记录（初始状态），必须保留
   • status != prev_status：当前状态与上周状态不同，属于状态变更，需要保留
3. 排序：最终结果按用户ID和日期排序，保证输出顺序符合预期

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