如何用SQL获取状态变更各阶段的最早记录？

表结构（status_updates） #

期望结果 #

提取每次状态变更后，对应连续状态阶段的第一条记录：

已尝试的SQL语句 #

select
    `status`,
    `created_at`
from
    `status_updates`
left join
    (select
        `id`,
        row_number() over (partition by status_updates.entity_id, status_updates.status order by status_updates.created_at asc) as sequence
    from
        `status_updates`)
    as `oldest_history`
    on
        `oldest_history`.`id` = `shipper_credit_histories`.`id`
where `sequence` = 1

当前错误结果 #

该SQL仅返回每种状态的全局最早记录，无法捕捉状态切换后的阶段：

修正后的SQL语句 #

要实现需求，需先识别连续相同状态的分组，再提取每个分组的最早记录：

WITH status_groups AS (
    SELECT 
        id,
        entity_id,
        status,
        date,
        -- 标记连续状态组：状态变化时生成新组ID
        SUM(CASE WHEN prev_status != status THEN 1 ELSE 0 END) OVER (PARTITION BY entity_id ORDER BY date) AS group_id
    FROM (
        SELECT 
            *,
            -- 获取同一entity_id下上一条记录的状态
            LAG(status) OVER (PARTITION BY entity_id ORDER BY date) AS prev_status
        FROM status_updates
    ) AS t
)
SELECT 
    id,
    entity_id,
    status,
    date
FROM (
    SELECT 
        *,
        -- 每个组内按日期排序取第一条
        ROW_NUMBER() OVER (PARTITION BY entity_id, group_id ORDER BY date) AS rn
    FROM status_groups
) AS t
WHERE rn = 1
ORDER BY date DESC;

逻辑说明 #

1. 内层子查询用LAG()函数获取同一entity_id下，按date排序的上一条记录的status；
2. 通过SUM()窗口函数生成连续状态组ID：每当当前状态与上一条不同时，累加1，确保连续相同状态的记录属于同一组；
3. 最后在每个状态组内按date排序，取第一条（该阶段的最早记录），再按date倒序排列得到期望结果。

原SQL问题分析 #

原SQL按entity_id和status分区，会把所有相同状态的记录归为一组，忽略了中间的状态变更，因此只能得到每种状态的全局最早记录，无法满足多次状态切换后的阶段记录提取需求。

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