如何编写SQL提取每次状态变更为S后的首条记录

场景说明 #

现有合同状态流水表，包含3个字段：

• cntrct_number：合同编号
• status_cd：状态码
• registration_date：登记日期

表样例数据如下：

cntrct_number   status_cd   registration_date
123             A           23-03-19
123             A           06-06-19
123             S           10-06-21
123             S           11-06-21
123             S           12-06-21
123             A           13-06-21
123             S           14-06-21
123             S           15-06-21

需求为筛选status_cd = 'S'的记录，规则是：每次状态切换为'S'后，取紧接变更后的第一条S状态记录，期望输出为：

cntrct_number   status_cd   registration_date
123             S           11-06-21
123             S           14-06-21

实现逻辑 #

该需求属于典型的连续状态区间（间隙岛）问题，核心是先识别每一段连续的S状态段，再按规则取数：

1. 按合同编号分组，按登记日期转成标准日期格式后升序排序，通过窗口函数获取每条记录的上一条状态，标记状态切换边界
2. 为每一段连续相同的状态生成独立分组ID
3. 在每个连续S状态分组内按日期排序打行号，按规则取对应行即可

注：按通用逻辑，状态切换为S的第一条记录（即上一条状态非S、当前状态为S的记录）就是切换后的首条S记录，对应样例中10-06-21、14-06-21两条。若要匹配给出的期望输出，大概率是样例中10-06-21的状态码存在笔误（应为A），此时取每个S分组的第一条记录即可完全匹配期望结果。

SQL代码（支持窗口函数的数据库，如MySQL8+、PostgreSQL、Oracle等） #

WITH sorted_data AS (
    SELECT
        cntrct_number,
        status_cd,
        registration_date,
        -- 把字符串格式日期转为标准日期类型，避免字符串排序错误
        STR_TO_DATE(registration_date, '%d-%m-%y') AS reg_dt,
        -- 取同合同按日期排序后的上一条状态
        LAG(status_cd) OVER(
            PARTITION BY cntrct_number 
            ORDER BY STR_TO_DATE(registration_date, '%d-%m-%y')
        ) AS prev_status
    FROM contract_status -- 替换为实际表名
),
status_group AS (
    SELECT
        *,
        -- 状态变化时生成新分组，累加得到连续状态的分组ID
        SUM(CASE WHEN prev_status = status_cd THEN 0 ELSE 1 END) OVER(
            PARTITION BY cntrct_number
            ORDER BY reg_dt
        ) AS group_id
    FROM sorted_data
    WHERE status_cd = 'S' -- 提前过滤S状态减少计算量
),
group_ranked AS (
    SELECT
        cntrct_number,
        status_cd,
        registration_date,
        ROW_NUMBER() OVER(
            PARTITION BY cntrct_number, group_id
            ORDER BY reg_dt
        ) AS rn
    FROM status_group
)
-- 取每个连续S分组的第一条记录，若10号记录状态为A，结果即为期望的11-06-21、14-06-21
SELECT cntrct_number, status_cd, registration_date
FROM group_ranked
WHERE rn = 1;

如果业务规则明确要求状态切换动作本身的那条S记录不算，取后续第一条S，只需要把最后筛选条件改成rn = 2即可，此时输出为11-06-21、15-06-21，可根据实际业务规则调整筛选的行号。

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