如何计算工单间精确重叠时长？现有SQL存在重复统计问题

我明白你的问题了——原SQL采用两两工单比较后求和的方式，这会让多个工单共同重叠的时间段被重复计算（比如3个工单同时覆盖的区域会被算3次），最终总时长明显超过实际的重叠时长。要得到精确的、去重后的总重叠时长，我们需要换一种思路：统计时间轴上所有被至少2个工单同时覆盖的时间段的总长度。

核心思路 #

1. 把所有工单的「打开时间」和「关闭时间」都提取出来，标记成“激活工单”或“结束工单”的事件；
2. 按时间顺序排列这些事件；
3. 顺着时间轴走，实时统计当前活跃的工单数量；
4. 只要活跃数量≥2，就把当前时间段的时长累加到总重叠时长里——这样每一段重叠区域只会被计算一次，不会重复累加。

修正后的SQL代码 #

WITH tickets AS (
    -- 提取所有工单的打开事件：每打开一个工单，活跃数+1
    SELECT 
        o.SSTID, 
        o.open_Id, 
        o.Createddatetime AS eventTime,
        1 AS delta
    FROM dbo.Openticket o
    UNION ALL
    -- 提取所有工单的关闭事件：每关闭一个工单，活跃数-1
    SELECT 
        c.SSTID, 
        c.open_id, 
        c.Createddatetime AS eventTime,
        -1 AS delta
    FROM dbo.Close_ticket c
),
ordered_events AS (
    SELECT 
        SSTID,
        eventTime,
        delta,
        -- 计算到当前事件为止的累计活跃工单数量
        SUM(delta) OVER (PARTITION BY SSTID ORDER BY eventTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS active_tickets,
        -- 获取上一个事件的时间，用来计算时间段间隔
        LAG(eventTime) OVER (PARTITION BY SSTID ORDER BY eventTime) AS prev_eventTime
    FROM tickets
    ORDER BY SSTID, eventTime
)
SELECT 
    SSTID,
    -- 累加所有活跃工单≥2的时间段时长（按小时计算）
    SUM(
        CASE 
            -- 注意：当前active_tickets是加上本次delta后的数值，所以前一个时间段的活跃数是active_tickets - delta
            WHEN active_tickets - delta >= 2 THEN 
                DATEDIFF(HOUR, prev_eventTime, eventTime)
            ELSE 0
        END
    ) AS TotalOverlappingHours
FROM ordered_events
WHERE prev_eventTime IS NOT NULL -- 跳过第一个事件，因为没有前序时间可以计算间隔
GROUP BY SSTID;

用你的测试数据验证 #

你的测试数据里，各工单的时间范围是：

• 工单1：2020-04-27 06:40 ~ 20:40
• 工单2：12:40 ~ 15:40
• 工单3：14:40 ~ 16:40
• 工单4：15:40 ~ 17:40
• 工单5：18:40 ~ 21:40

按照修正后的逻辑，计算出的总重叠时长是7小时：

• 12:40-14:40：2个工单活跃（1+2）→ 2小时
• 14:40-15:40：3个工单活跃（1+2+3）→1小时
• 15:40-16:40：2个工单活跃（1+3）→1小时
• 16:40-17:40：2个工单活跃（1+4）→1小时
• 18:40-20:40：2个工单活跃（1+5）→2小时

而原SQL的结果是10小时，原因是它把14:40-15:40这段3工单重叠的区域，在1&2、1&3、2&3的比较中各算了一次，重复统计了两次，导致结果偏大。

代码细节解释 #

1. tickets CTE：把打开和关闭事件合并，用delta标记活跃数的变化，方便后续统计；
2. ordered_events CTE：按时间排序事件，用窗口函数计算累计活跃数，同时获取上一个事件的时间；
3. 最终查询：判断每个时间段的活跃数是否≥2，符合条件就计算时长并累加，得到真正的去重后总重叠时长。

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