为SQL中连续相同letter的分段分配唯一section_number

原始数据表 #

"letter"    "time"
"a" "2024-08-14 16:57:56.563112"
"b" "2024-08-14 17:57:56.563112"
"b" "2024-08-14 18:57:56.563112"
"b" "2024-08-14 19:57:56.563112"
"a" "2024-08-14 20:57:56.563112"
"a" "2024-08-14 21:57:56.563112"
"b" "2024-08-14 22:57:56.563112"
"b" "2024-08-14 23:57:56.563112"

需求说明 #

需要基于连续相同的letter为每一段分配唯一的section_number，期望结果如下：

"letter"    "time"  "section_number"
"a" "2024-08-14 16:57:56.563112"    0
"b" "2024-08-14 17:57:56.563112"    1
"b" "2024-08-14 18:57:56.563112"    1
"b" "2024-08-14 19:57:56.563112"    1
"a" "2024-08-14 20:57:56.563112"    2
"a" "2024-08-14 21:57:56.563112"    2
"b" "2024-08-14 22:57:56.563112"    3
"b" "2024-08-14 23:57:56.563112"    3

尝试的SQL及问题 #

之前尝试的SQL语句：

SELECT 
    letter
    ,time
    ,ROW_NUMBER() OVER(ORDER BY  time)
    - ROW_NUMBER() OVER(Partition by letter ORDER BY  time) as section_number
FROM test;

该语句生成的section_number存在重复（如编号3出现多次），实际结果如下：

"letter"    "time"  "section_number"
"a" "2024-08-14 16:57:56.563112"    0
"b" "2024-08-14 17:57:56.563112"    1
"b" "2024-08-14 18:57:56.563112"    1
"b" "2024-08-14 19:57:56.563112"    1
"a" "2024-08-14 20:57:56.563112"    3
"a" "2024-08-14 21:57:56.563112"    3
"b" "2024-08-14 22:57:56.563112"    3
"b" "2024-08-14 23:57:56.563112"    3

正确的SQL实现方案 #

核心思路是通过LAG()函数判断当前行与前一行的letter是否相同，标记新分段的起始点，再通过累加标记值生成唯一的分段编号：

WITH section_markers AS (
    SELECT 
        letter,
        time,
        -- 当前行与前一行letter不同时，标记为新分段的起始
        CASE 
            WHEN LAG(letter) OVER(ORDER BY time) != letter THEN 1 
            ELSE 0 
        END AS new_section_flag
    FROM test
)
SELECT 
    letter,
    time,
    -- 累加标记值，生成从0开始的连续分段编号
    SUM(COALESCE(new_section_flag, 0)) OVER(ORDER BY time) AS section_number
FROM section_markers;

逻辑说明 #

1. LAG(letter) OVER(ORDER BY time)：获取当前行按时间排序后的前一行letter值。
2. new_section_flag：当当前行letter与前一行不同时，标记为1（新分段开始），否则为0。
3. SUM(COALESCE(new_section_flag, 0)) OVER(ORDER BY time)：累加标记值，第一行无前置行，用COALESCE将NULL转为0，最终得到连续唯一的分段编号。

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