基于层级逻辑在TSQL中移除事件间重叠时间戳

问题场景 #

现有数千用户的每日状态数据，存在三类时间重叠问题，需按优先级规则修正：

• On_Queue 内部：Wrapup 优先级高于 Interacting，需调整 Interacting 的时间戳和时长
• On_Queue 整体优先级高于所有 Off_Queue，重叠时直接调整或移除 Off_Queue 的时间区间
• 单个 Interacting 被多个 Wrapup 打断时，要把 Interacting 拆分成多行独立区间

已知能用 LEAD/LAG 处理时间戳同步，但不知道如何处理完全被覆盖的区间，以及拆分被打断的 Interacting，以下是具体的 TSQL 实现方案。

解决思路 #

1. 给所有状态定义明确的优先级，方便后续判断高低
2. 把每个状态的起止时间拆成「开始/结束」事件点，基于这些点生成最小粒度的时间区间
3. 在每个区间内保留最高优先级的状态，过滤掉被完全覆盖的低优先级区间
4. 利用事件点的自然拆分，自动处理 Interacting 被多个 Wrapup 打断的情况

具体实现代码 #

第一步：定义状态优先级 #

先建一个临时表存储优先级规则，数值越大优先级越高，后续扩展新状态直接加行即可：

DECLARE @StatePriority TABLE (StateName VARCHAR(50), Priority INT);
INSERT INTO @StatePriority VALUES
('Wrapup', 3),     -- 最高优先级
('Interacting', 2),
('Off_Queue', 1);  -- 最低优先级

第二步：拆分状态为事件点，生成基础时间区间 #

把每个状态的开始、结束时间拆成独立事件，再用 LEAD 函数获取下一个事件的时间，生成连续的小时间区间：

WITH StateEvents AS (
    -- 生成状态开始事件
    SELECT
        UserID,
        StateStartTime AS EventTime,
        StateName,
        Priority,
        1 AS EventType
    FROM UserStateData
    JOIN @StatePriority sp ON UserStateData.StateName = sp.StateName
    
    UNION ALL
    
    -- 生成状态结束事件
    SELECT
        UserID,
        StateEndTime AS EventTime,
        StateName,
        Priority,
        -1 AS EventType
    FROM UserStateData
    JOIN @StatePriority sp ON UserStateData.StateName = sp.StateName
),
OrderedEvents AS (
    SELECT
        UserID,
        EventTime,
        -- 获取当前事件的下一个事件时间，形成区间
        LEAD(EventTime) OVER (PARTITION BY UserID ORDER BY EventTime) AS NextEventTime,
        -- 同一时间点取优先级最高的状态
        FIRST_VALUE(StateName) OVER (
            PARTITION BY UserID, EventTime
            ORDER BY Priority DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS TopPriorityState
    FROM StateEvents
)

第三步：过滤无效区间，生成最终修正数据 #

去掉时间无效的区间，再排除完全被高优先级状态覆盖的低优先级区间，自动完成 Interacting 的拆分：

, ValidIntervals AS (
    SELECT
        UserID,
        EventTime AS IntervalStart,
        NextEventTime AS IntervalEnd,
        TopPriorityState AS StateName
    FROM OrderedEvents
    WHERE NextEventTime IS NOT NULL
        AND EventTime < NextEventTime
    -- 去重同一用户同一时间段的重复状态记录
    GROUP BY UserID, EventTime, NextEventTime, TopPriorityState
),
FinalCleanedData AS (
    SELECT
        UserID,
        IntervalStart,
        IntervalEnd,
        StateName,
        DATEDIFF(SECOND, IntervalStart, IntervalEnd) AS DurationInSeconds
    FROM ValidIntervals vid
    -- 过滤掉完全被高优先级状态覆盖的低优先级区间
    WHERE NOT EXISTS (
        SELECT 1
        FROM ValidIntervals vid2
        WHERE vid2.UserID = vid.UserID
            AND vid2.IntervalStart <= vid.IntervalStart
            AND vid2.IntervalEnd >= vid.IntervalEnd
            AND vid2.StateName != vid.StateName
            AND (SELECT Priority FROM @StatePriority WHERE StateName = vid2.StateName) > (SELECT Priority FROM @StatePriority WHERE StateName = vid.StateName)
    )
)
-- 输出最终处理后的结果
SELECT * FROM FinalCleanedData ORDER BY UserID, IntervalStart;

关键逻辑说明 #

• 优先级映射：用临时表@StatePriority统一管理优先级，后续修改或新增状态不用改核心逻辑
• 事件拆分：把每个状态的起止拆成事件点，确保所有重叠的地方都能被拆成最小粒度的区间，自然解决Interacting被多次打断的拆分问题
• 最高优先级选取：在每个时间点取优先级最高的状态，保证每个小区间内只有一个有效状态
• 覆盖区间过滤：通过NOT EXISTS子句检查当前区间是否被更高优先级的区间完全包含，是的话直接过滤掉

性能优化建议 #

• 针对UserID、StateStartTime、StateEndTime字段建立联合索引，处理数千用户的每日数据时能大幅提升速度
• 如果数据量极大，可以分用户分批处理，避免一次性加载过多数据

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