求助：如何用SQL合并重叠的用户访问时间窗口？

需求说明 #

用户提交的访问申请会分配2小时时间窗口，存在多份重叠申请时，需要合并为一个窗口，取最早的TimeWindowStart和最晚的TimeWindowEnd，同时保留用户实际访问时间（DetectionTime）及相关工单信息。

示例数据SQL #

CREATE TABLE #temp (
    Ticketnumber int,
    UserID varchar(20),
    DetectionTime datetime,
    TimeWindowStart datetime,
    TimeWindowEnd datetime)

insert into #temp (Ticketnumber ,UserID, DetectionTime, TimeWindowStart, TimeWindowEnd)
VALUES
('567270', 'mark', '2022-12-15 08:42:28.000', '2022-12-15 08:38:46.000', '2022-12-15 10:53:56.000'),
('567270', 'mark', '2022-12-15 09:34:52.000', '2022-12-15 08:38:46.000', '2022-12-15 10:53:56.000'),
('578596', 'mark', '2022-12-15 09:29:37.000', '2022-12-15 09:09:28.000', '2022-12-15 11:24:33.000'),
('578596', 'mark', '2022-12-15 09:35:37.000', '2022-12-15 09:09:28.000', '2022-12-15 11:24:33.000'),
('578596', 'mark', '2022-12-15 09:34:52.000', '2022-12-15 09:09:28.000', '2022-12-15 11:24:33.000'),
('045611', 'mark', '2023-02-01 16:13:42.000', '2023-02-01 15:56:41.000', '2023-02-01 18:11:43.000'),
('626948', 'anna', '2022-12-20 13:18:20.000', '2022-12-20 11:33:25.000', '2022-12-20 13:48:32.000'),
('626948', 'anna', '2022-12-20 12:06:40.000', '2022-12-20 11:33:25.000', '2022-12-20 13:48:32.000'),
('626948', 'anna', '2022-12-20 13:15:39.000', '2022-12-20 11:33:25.000', '2022-12-20 13:48:32.000'),
('627361', 'anna', '2022-12-20 13:18:20.000', '2022-12-20 11:33:31.000', '2022-12-20 13:48:34.000'),
('627361', 'anna', '2022-12-20 12:17:43.000', '2022-12-20 11:33:31.000', '2022-12-20 13:48:34.000'),
('627361', 'anna', '2022-12-20 13:07:31.000', '2022-12-20 11:33:31.000', '2022-12-20 13:48:34.000'),
('627361', 'anna', '2022-12-20 12:06:40.000', '2022-12-20 11:33:31.000', '2022-12-20 13:48:34.000'),
('627361', 'anna', '2022-12-20 13:15:39.000', '2022-12-20 11:33:31.000', '2022-12-20 13:48:34.000')

解决方案SQL #

单纯使用Lag/Lead无法高效合并重叠区间，需通过分组标记法实现：

1. 按用户分组，对每个用户的时间窗口按TimeWindowStart排序
2. 标记每个窗口是否与前一个窗口重叠，生成分组ID
3. 按用户和分组ID聚合，得到合并后的时间窗口，同时保留关联的工单和访问时间

WITH RankedWindows AS (
    SELECT 
        UserID,
        TimeWindowStart,
        TimeWindowEnd,
        Ticketnumber,
        DetectionTime,
        -- 标记当前窗口是否与上一个窗口重叠，生成分组ID
        SUM(CASE WHEN TimeWindowStart <= LAG(TimeWindowEnd) OVER (PARTITION BY UserID ORDER BY TimeWindowStart) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY UserID ORDER BY TimeWindowStart) AS GroupID
    FROM #temp
),
MergedWindows AS (
    SELECT 
        UserID,
        GroupID,
        MIN(TimeWindowStart) AS MergedStart,
        MAX(TimeWindowEnd) AS MergedEnd,
        -- 合并所有关联工单（去重）
        STRING_AGG(DISTINCT Ticketnumber, ', ') AS AssociatedTickets,
        -- 合并所有实际访问时间
        STRING_AGG(CONVERT(varchar(20), DetectionTime, 120), ', ') AS DetectionTimes
    FROM RankedWindows
    GROUP BY UserID, GroupID
)
SELECT 
    UserID,
    MergedStart,
    MergedEnd,
    AssociatedTickets,
    DetectionTimes
FROM MergedWindows
ORDER BY UserID, MergedStart;

-- 清理临时表
DROP TABLE #temp;

结果说明 #

• Mark在2022-12-15的两个重叠窗口会合并为2022-12-15 08:38:46至2022-12-15 11:24:33
• Anna在2022-12-20的两个近乎完全重叠的窗口会合并为2022-12-20 11:33:25至2022-12-20 13:48:34
• 每个合并窗口会保留所有关联的工单编号和实际访问时间

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