如何为重复的类别分组集生成递增的行号？

你的需求是为连续相同的cat分组分配递增的编号，而非按cat全局分组重置行号。可以通过以下步骤实现：

实现SQL #

SELECT 
    id,
    cat,
    SUM(flag) OVER (ORDER BY id) AS row_number
FROM (
    SELECT 
        id,
        cat,
        CASE 
            WHEN LAG(cat) OVER (ORDER BY id) != cat OR LAG(cat) OVER (ORDER BY id) IS NULL 
            THEN 1 
            ELSE 0 
        END AS flag
    FROM data
) AS sub_query
ORDER BY id;

逻辑说明 #

1. 识别新分组的起始行：
   子查询中用LAG(cat) OVER (ORDER BY id)获取当前行的上一行cat值。通过CASE语句判断：

   • 如果是第一行（上一行cat为NULL），或者当前行cat与上一行不同，标记为1（表示新分组开始）；
   • 否则标记为0（属于当前分组）。

2. 生成递增组编号：
   外层用SUM(flag) OVER (ORDER BY id)对标记值做累加求和：

   • 每遇到新分组（flag=1），累加值加1；
   • 同一连续分组内的flag=0，累加值保持不变，最终得到连续分组的递增编号。

执行结果 #

运行后会得到你期望的输出：

id   |    cat     |   row_number
-----+------------+-------------
1    |    A       |   1
2    |    A       |   1
3    |    B       |   2
4    |    B       |   2
5    |    A       |   3
6    |    A       |   3
7    |    B       |   4
8    |    A       |   5
9    |    B       |   6
10   |    B       |   6

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