基于UserId和DayId合并重叠/连续时间区间的SQL视图实现需求

假设你的源表名为TimePeriods，以下是修正后的CTE方案，既能处理重叠区间，也能合并相邻区间（如EndTimePeriodId=24与StartTimePeriodId=25的连续行）：

CREATE VIEW MergedTimePeriods AS
WITH OrderedPeriods AS (
    SELECT
        UserId,
        DayId,
        StartTimePeriodId,
        EndTimePeriodId,
        -- 获取同用户同天的上一个区间的结束ID
        LAG(EndTimePeriodId) OVER (
            PARTITION BY UserId, DayId
            ORDER BY StartTimePeriodId
        ) AS PrevEndId
    FROM TimePeriods
),
GroupedPeriods AS (
    SELECT
        UserId,
        DayId,
        StartTimePeriodId,
        EndTimePeriodId,
        -- 生成分组ID：当前区间与上一个不连续/不重叠时，分组ID+1
        SUM(
            CASE
                WHEN StartTimePeriodId > COALESCE(PrevEndId, 0) + 1 THEN 1
                ELSE 0
            END
        ) OVER (
            PARTITION BY UserId, DayId
            ORDER BY StartTimePeriodId
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GroupId
    FROM OrderedPeriods
)
SELECT
    UserId,
    DayId,
    MIN(StartTimePeriodId) AS StartTimePeriodId,
    MAX(EndTimePeriodId) AS EndTimePeriodId
FROM GroupedPeriods
GROUP BY UserId, DayId, GroupId
ORDER BY UserId, DayId, StartTimePeriodId;

关键逻辑说明 #

1. OrderedPeriods CTE：

   • 按UserId和DayId分组，对每个用户每天的时间区间按StartTimePeriodId排序
   • 使用LAG()窗口函数获取当前区间的上一个区间的EndTimePeriodId，用于后续判断连续性

2. GroupedPeriods CTE：

   • 核心判断条件：StartTimePeriodId > COALESCE(PrevEndId, 0) + 1
     • 如果当前区间的开始ID大于上一个区间结束ID+1，说明两个区间不连续也不重叠，需要新建分组
     • 反之（包括重叠：StartTime <= PrevEndId，或相邻：StartTime = PrevEndId +1），则归为同一分组
   • 用SUM()窗口函数累计生成分组ID，确保同组的区间拥有相同的GroupId

3. 最终聚合：

   • 按UserId、DayId和GroupId分组，取每组的最小开始ID和最大结束ID，得到合并后的区间

示例验证 #

输入数据

输出结果

这个方案完全基于窗口函数和CTE实现，不需要游标或其他不支持的语法，符合视图的创建要求。

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