SQL中如何按非连续值分区并实现自定义排名？

这是典型的间隙分组问题，核心是识别col2中连续值的间隙，以此为依据在同一col1分区内拆分出子分组，再进行排名。下面是具体的实现步骤和代码：

思路拆解 #

要实现需求，我们需要先为每个连续的col2区间生成唯一的分组标识，再结合col1一起作为分区键进行排名：

1. 用LAG()窗口函数获取同一col1下前一行的col2值，判断当前行与前一行是否存在间隙（差值>1）。
2. 为每个间隙起点（或分组第一行）标记为新分组的开始。
3. 通过累加标记值生成每个连续区间的分组ID。
4. 最后在(col1, 分组ID)的分区内计算排名。

完整SQL代码 #

WITH grouped_data AS (
    SELECT 
        col1,
        col2,
        -- 标记新分组的起点：第一行 或 当前col2与前一行差值大于1
        CASE 
            WHEN LAG(col2) OVER (PARTITION BY col1 ORDER BY col2) IS NULL THEN 1
            WHEN col2 - LAG(col2) OVER (PARTITION BY col1 ORDER BY col2) > 1 THEN 1
            ELSE 0
        END AS group_flag
    FROM your_table
),
partitioned_data AS (
    SELECT 
        col1,
        col2,
        -- 累加标记值，得到每个连续区间的唯一分组ID
        SUM(group_flag) OVER (PARTITION BY col1 ORDER BY col2) AS partition_id
    FROM grouped_data
)
SELECT 
    col1,
    col2,
    -- 在(col1, partition_id)分区内生成排名
    ROW_NUMBER() OVER (PARTITION BY col1, partition_id ORDER BY col2) AS rank
FROM partitioned_data
ORDER BY col1, col2;

代码解释 #

• LAG()函数：用于获取同一col1分组中，当前行的前一行col2值，分组的第一行会返回NULL。
• group_flag字段：当是分组第一行或当前col2与前一行的差值大于1时，标记为1，代表新分组的开始；否则为0。
• SUM(group_flag)累加：对group_flag进行累加，这样连续的col2区间会得到相同的partition_id，间隙处会因为group_flag=1让累加值跳变，形成新的分组。
• 排名计算：用ROW_NUMBER()在(col1, partition_id)的分区内按col2排序，生成你需要的排名（如果col2有重复值，可替换为RANK()或DENSE_RANK()）。

验证示例数据 #

将你的示例数据代入上述SQL，会得到完全符合预期的结果：

内容的提问来源于stack exchange，提问作者Ik-Hwan Kim