如何按状态变化生成STATUS序列（忽略中间重复项）

假设你的表名为customer_status，我们可以通过窗口函数组合来实现需求：

1. 用LAG()函数获取同一客户的上一条记录的CUT_STATUS
2. 对比当前行和上一行的状态，标记是否发生变化（变化则记1，否则记0）
3. 用SUM()窗口函数按客户分组累加变化标记，得到最终的REQUIRED_SEQUENCE

通用SQL实现 #

SELECT
    CUSTOMER_ID,
    CUT_STATUS,
    SUM(CASE 
        WHEN LAG(CUT_STATUS) OVER (PARTITION BY CUSTOMER_ID ORDER BY <排序字段>) != CUT_STATUS 
        THEN 1 ELSE 0 
    END) OVER (PARTITION BY CUSTOMER_ID ORDER BY <排序字段>) + 1 AS REQUIRED_SEQUENCE
FROM customer_status;

关键说明 #

• 替换<排序字段>：必须指定一个能确定记录顺序的字段（比如状态变更时间戳status_change_time、自增ID等），状态变化是有先后顺序的，明确排序逻辑才能正确对比前后行状态。
• 初始值+1：第一条记录没有上一行，LAG()返回NULL，CASE判断为0，累加后加1保证第一个序列从1开始。

适配MySQL的示例（假设存在自增id作为排序字段） #

SELECT
    CUSTOMER_ID,
    CUT_STATUS,
    SUM(CASE 
        WHEN LAG(CUT_STATUS) OVER (PARTITION BY CUSTOMER_ID ORDER BY id) != CUT_STATUS 
        THEN 1 ELSE 0 
    END) OVER (PARTITION BY CUSTOMER_ID ORDER BY id) + 1 AS REQUIRED_SEQUENCE
FROM customer_status;

兼容无窗口函数的老版本数据库（比如MySQL 5.x） #

SELECT
    CUSTOMER_ID,
    CUT_STATUS,
    @seq := CASE
        WHEN @prev_cust = CUSTOMER_ID AND @prev_status = CUT_STATUS THEN @seq
        WHEN @prev_cust = CUSTOMER_ID THEN @seq + 1
        ELSE 1
    END AS REQUIRED_SEQUENCE,
    @prev_cust := CUSTOMER_ID,
    @prev_status := CUT_STATUS
FROM customer_status,
     (SELECT @prev_cust := NULL, @prev_status := NULL, @seq := 0) AS init
ORDER BY CUSTOMER_ID, id;

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