如何为ID分组的状态连续实例生成Rank并合并时间区间？

你的问题核心是识别同一ID下连续出现的相同状态组，之前用dense_rank(partition by id order by status, from_date)失效，是因为它会把所有相同状态的行归为同一rank，不管是否连续。正确的做法是先识别连续状态的分组，再聚合生成结果，步骤如下：

实现思路 #

1. 标记状态变化点：用窗口函数LAG()获取当前行的前一行状态，对比当前行状态，若不同则标记为1，否则为0
2. 生成连续状态组ID：对标记的变化点做累加求和，得到每个连续状态组的唯一标识
3. 合并时间区间：按ID和状态组ID分组，取组内最小的from_date和最大的to_date
4. 生成正确Rank：对每个ID下的状态组按时间顺序生成dense_rank

完整SQL代码 #

WITH status_groups AS (
    SELECT
        id,
        status,
        from_date,
        to_date,
        -- 标记状态变化：当前状态与前一行不同则为1，否则0
        CASE 
            WHEN LAG(status) OVER (PARTITION BY id ORDER BY from_date) = status 
            THEN 0 
            ELSE 1 
        END AS is_status_change,
        -- 累加变化点得到连续状态组的ID
        SUM(CASE 
            WHEN LAG(status) OVER (PARTITION BY id ORDER BY from_date) = status 
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY id ORDER BY from_date) AS group_id
    FROM your_table_name
),
merged_intervals AS (
    SELECT
        id,
        status,
        MIN(from_date) AS from_date,
        MAX(to_date) AS to_date,
        group_id
    FROM status_groups
    GROUP BY id, status, group_id
)
SELECT
    id,
    status,
    from_date,
    to_date,
    DENSE_RANK() OVER (PARTITION BY id ORDER BY from_date) AS rank
FROM merged_intervals
ORDER BY id, from_date;

代码说明 #

• LAG(status) OVER (...)：获取同一ID下按from_date排序的前一行状态，用来判断当前行是否属于新的状态组
• SUM(...) OVER (...)：累加状态变化标记，生成连续状态组的唯一ID，同一连续状态组的行group_id相同
• 分组聚合：按id、status、group_id分组，合并时间区间
• 生成Rank：最后按ID和合并后的from_date排序生成Rank，确保连续状态组的Rank按时间顺序递增

测试验证 #

用你提供的输入数据测试，会得到完全符合期望的输出：

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