SQL Server 2008实现同日期连续时间段记录合并

问题核心 #

现有30分钟粒度的时段表，包含calender_date（日期）、timeslot（时段开始时间）、timeslot_end（时段结束时间）三个字段，同日期内可能存在时间断档（示例中2021-12-24的15:30-16:30无记录）。需求为合并同日期内满足「上一条记录timeslot_end = 下一条记录timeslot」的连续时段。
原有基于ROW_NUMBER+自连接的方案分区逻辑存在缺陷，未对断档两侧的时段做隔离，会将不连续的时段错误合并为一条，且需要兼容SQL Server 2008版本（该版本不支持LAG/LEAD窗口函数、不支持窗口内排序累加语法）。

实现代码 #

采用「断档标记-分组编号-聚合取值」的逻辑实现，所有语法均兼容SQL Server 2008：

WITH cte_mark_start AS (
    -- 标记每个连续时段的起始记录
    SELECT
        calender_date,
        timeslot,
        timeslot_end,
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM @tmp_leave t_pre
                WHERE t_pre.calender_date = t_cur.calender_date
                  AND t_pre.timeslot_end = t_cur.timeslot
            ) THEN 0
            ELSE 1
        END AS is_segment_start
    FROM @tmp_leave t_cur
),
cte_assign_group AS (
    -- 为同一段连续时段分配相同的分组ID
    SELECT
        s1.calender_date,
        s1.timeslot,
        s1.timeslot_end,
        (
            SELECT SUM(s2.is_segment_start)
            FROM cte_mark_start s2
            WHERE s2.calender_date = s1.calender_date
              AND s2.timeslot <= s1.timeslot
        ) AS segment_group_id
    FROM cte_mark_start s1
)
-- 按分组聚合得到合并后的连续时段
SELECT
    calender_date,
    MIN(timeslot) AS timeslot,
    MAX(timeslot_end) AS timeslot_end
FROM cte_assign_group
GROUP BY calender_date, segment_group_id
ORDER BY calender_date, timeslot

逻辑说明 #

• 起始标记：同日期下，如果某条记录找不到和自己开始时间完全衔接的上一条记录，就说明这是一个新连续时段的起点，标记为1；连续段内部的记录因为能找到衔接的上一条，标记为0
• 分组编号：同日期内按时间从早到晚累加起始标记值，每遇到一个新的时段起点，累加值加1。这样同一段连续时段内的所有记录会拿到相同的分组ID，断档两侧的时段因为中间多了一次起点标记，分组ID不同，从根本上避免跨断档合并的问题
• 聚合取值：按日期+分组ID分组，取组内最早的开始时间、最晚的结束时间，输出结果和预期完全一致。

针对给出的示例数据验证：2021-12-24下14:00为第一个起点（标记1），后续14:30、15:00为段内记录（标记0），三条记录分组ID均为1，聚合得到14:00-15:30；16:30因找不到衔接的上一条记录（上一段结束为15:30，存在1小时间隙），标记为新起点（1），后续17:00、17:30为段内记录，分组ID为2，聚合得到16:30-18:00；2021-12-30的两条记录同属一个分组，聚合得到09:00-10:00，完全匹配预期输出。

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