关于按SysLogDate时序分组DueDate并分配区段编号的SQL后续问题

问题分析 #

原解决方案通过Row_Number()的差值生成分组标识，该逻辑仅在相同DueDate的记录非间断出现时有效。当遇到全新的DueDate（或NULL）后，后续不同的DueDate无法被正确拆分到新区段，导致区段编号错误。

正确SQL代码 #

SELECT 
    OrderNo,
    DueDate,
    SysLogDate,
    SUM(flag) OVER (PARTITION BY OrderNo ORDER BY SysLogDate) AS SectionNumber_WithinDueDate
FROM (
    SELECT 
        *,
        -- 当前行与上一行DueDate不同（或为分区首行）时，标记为新区段起点
        CASE 
            WHEN LAG(DueDate) OVER (PARTITION BY OrderNo ORDER BY SysLogDate) != DueDate 
                OR LAG(DueDate) OVER (PARTITION BY OrderNo ORDER BY SysLogDate) IS NULL
            THEN 1
            ELSE 0
        END AS flag
    FROM #DueDates
) t
ORDER BY OrderNo, SysLogDate;

代码说明 #

1. LAG函数：按OrderNo分区、SysLogDate排序，获取当前行的上一行DueDate值，用于判断是否进入新区段。
2. 区段标记：对比当前行与上一行的DueDate，若不同（或当前是分区内第一行），标记为1，表示开启新的连续区段。
3. 累加生成编号：通过SUM() OVER()窗口函数，按OrderNo分区、SysLogDate排序累加标记值，直接得到连续相同DueDate的区段编号，完全匹配期望输出。

验证结果 #

将上述代码应用到更新后的表数据，会得到如下符合预期的结果：

OrderNo DueDate     SysLogDate  SectionNumber_WithinDueDate
1       2022-04-10  2022-01-10  1
1       2022-04-10  2022-01-11  1
1       2022-04-15  2022-01-15  2
1       2022-04-13  2022-01-16  3
1       2022-04-15  2022-01-17  4
1       2022-04-10  2022-01-18  5
1       2022-04-10  2022-01-19  5
1       2022-04-10  2022-01-20  5
2       2022-04-10  2022-02-16  1
2       2022-04-10  2022-02-17  1
2       2022-04-15  2022-02-18  2
2       2022-04-15  2022-02-20  2
2       2022-04-15  2022-02-21  2
2       2022-04-10  2022-02-22  3
2       2022-04-10  2022-02-24  3
2       2022-04-10  2022-02-26  3

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