如何生成基于分区和时间顺序的阶段式Row Number？

你的需求属于典型的连续相同分组（岛屿问题），原写法的问题在于：ROW_NUMBER() OVER(PARTITION BY PERSON_ID, CATEGORY ORDER BY DATE ASC)会把同一PERSON_ID下所有相同CATEGORY的记录归为一个分区，不管这些记录是否连续，因此会给每条同分类记录分配递增编号，而非连续的同分类组共享同一编号。

解决方案： #

通过识别连续的同分类组，再对这些组分配编号，具体步骤如下：

1. 用LAG()函数获取当前行的上一行CATEGORY，对比判断是否属于新的连续组；
2. 累加新组的标记值，生成每个连续组的唯一ID；
3. 基于PERSON_ID和组ID，生成最终的连续编号。

示例SQL代码： #

WITH grouped_records AS (
    SELECT
        PERSON_ID,
        CATEGORY,
        DATE,
        -- 当当前分类与上一行不同时，标记为新组起始，累加得到组ID
        SUM(CASE 
                WHEN LAG(CATEGORY) OVER(PARTITION BY PERSON_ID ORDER BY DATE ASC) != CATEGORY 
                THEN 1 
                ELSE 0 
            END) OVER(PARTITION BY PERSON_ID ORDER BY DATE ASC) AS group_id
    FROM your_table
)
SELECT
    PERSON_ID,
    CATEGORY,
    DATE,
    -- 对每个PERSON_ID下的组分配连续编号
    DENSE_RANK() OVER(PARTITION BY PERSON_ID ORDER BY group_id ASC) AS desired_row_number
FROM grouped_records
ORDER BY PERSON_ID, DATE ASC;

简化写法（无需CTE）： #

SELECT
    PERSON_ID,
    CATEGORY,
    DATE,
    -- 统计新组的数量，加1得到连续编号
    COUNT(CASE WHEN prev_category != CATEGORY THEN 1 END) OVER(PARTITION BY PERSON_ID ORDER BY DATE ASC) + 1 AS desired_row_number
FROM (
    SELECT
        PERSON_ID,
        CATEGORY,
        DATE,
        LAG(CATEGORY) OVER(PARTITION BY PERSON_ID ORDER BY DATE ASC) AS prev_category
    FROM your_table
) t
ORDER BY PERSON_ID, DATE ASC;

效果对比： #

假设原数据如下：

原写法得到的结果（不符合预期）：

新写法得到的结果（符合需求）：

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