按日期与状态列去重 保留连续同状态最新记录

现有用户状态变更表，每行记录一次用户状态变更事件及对应发生日期，表结构及样例数据如下：

需求为去除同一用户下连续重复的状态记录，仅保留每组连续相同状态中日期最新的一条，期望输出结果如下：

这个需求不能直接用GROUP BY ID, Status去重——同一用户的同一个状态可能被其他状态间隔开、分多次连续出现，比如ID为64的用户Active状态先后出现了三次，中间都被Not Active状态隔断，直接分组会把这三段非连续的Active记录错误合并成一条。

正确逻辑是给连续相同的状态记录打上独立分组标签，再取每个分组的最新日期记录，分三步完成：

• 按用户ID分区，按日期升序排序，获取每条记录上一条的状态值
• 逐行判断：如果当前记录状态和上一条状态不一致，说明进入了新的连续状态段，累加计数生成新的连续段ID；状态一致则和上一条归为同一个连续段
• 按用户ID、连续段ID分组，取每个组内日期最大的记录，就是最终需要的结果

支持窗口函数的引擎（MySQL8.0+、PostgreSQL、Spark SQL、Hive等） #

假设表名为user_status_log，如果存储日期的字段是MM.DD.YYYY格式的字符串，需要先转为标准日期类型再排序，避免字符串排序顺序和实际日期顺序不一致，以下以MySQL语法为例：

WITH status_with_prev AS (
    SELECT
        STR_TO_DATE(`Date`, '%m.%d.%Y') AS log_date,
        `Date`,
        ID,
        Status,
        -- 取同个用户按日期排序后的上一条记录状态
        LAG(Status, 1) OVER (PARTITION BY ID ORDER BY STR_TO_DATE(`Date`, '%m.%d.%Y')) AS prev_status
    FROM user_status_log
),
status_with_group AS (
    SELECT
        `Date`,
        ID,
        Status,
        log_date,
        -- 状态和上一条不同时累加计数，生成连续状态段ID
        SUM(CASE WHEN prev_status IS NULL OR prev_status != Status THEN 1 ELSE 0 END) 
            OVER (PARTITION BY ID ORDER BY log_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS continuous_group_id
    FROM status_with_prev
)
-- 每个连续状态组取日期最大的一条
SELECT
    MAX(`Date`) AS `Date`,
    ID,
    Status
FROM status_with_group
GROUP BY ID, Status, continuous_group_id
ORDER BY ID DESC, log_date;

不支持窗口函数的旧版本MySQL #

可以用用户变量实现相同的连续段打标逻辑：

SELECT
    MAX(`Date`) AS `Date`,
    ID,
    Status
FROM (
    SELECT
        `Date`,
        ID,
        Status,
        @group_id := IF(@prev_id = ID AND @prev_status = Status, @group_id, @group_id + 1) AS continuous_group_id,
        @prev_id := ID,
        @prev_status := Status
    FROM (
        SELECT * FROM user_status_log 
        ORDER BY ID, STR_TO_DATE(`Date`, '%m.%d.%Y')
    ) t,
    (SELECT @group_id := 0, @prev_id := NULL, @prev_status := NULL) init
) t_group
GROUP BY ID, Status, continuous_group_id
ORDER BY ID DESC, STR_TO_DATE(`Date`, '%m.%d.%Y');

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