如何在Microsoft SQL中解决Gaps and Islands问题并计算净停机时长？

我完全理解你在处理重叠停机时间时的困惑——Gaps and Islands问题确实容易在处理重叠区间时绕晕。核心思路其实是把所有重叠或连续的停机时间段合并成一个独立的"无重叠区间"，然后计算这些合并后区间的总时长，这样就自动扣除了重叠部分，不用单独计算重叠时长再做减法。

假设你的查询结果表（我们暂时叫它MachineDowntime）包含以下字段：

• MachineID: 机器的唯一标识（比如'A'、'B'等）
• StartDowntime: 停机开始的datetime类型字段
• EndDowntime: 停机结束的datetime类型字段

完整SQL实现步骤 #

以下是针对Microsoft SQL Server的具体代码，我会一步步拆解逻辑：

1. 标记每条记录是否与上一条重叠

用窗口函数LAG()获取同机器上一条记录的结束时间，判断当前记录的开始时间是否早于等于上一条的结束时间（即重叠或连续）：

WITH RankedDowntime AS (
    SELECT 
        MachineID,
        StartDowntime,
        EndDowntime,
        -- 标记当前记录是否开启新的无重叠区间：如果上一条结束时间 >= 当前开始时间，说明重叠，标记为0，否则标记为1
        CASE 
            WHEN LAG(EndDowntime) OVER (PARTITION BY MachineID ORDER BY StartDowntime) >= StartDowntime 
            THEN 0 
            ELSE 1 
        END AS IsNewIsland
    FROM MachineDowntime
),

2. 给每个"无重叠区间（岛）"分配唯一分组ID

通过累计求和SUM()给每个独立的岛分配分组ID，每次遇到IsNewIsland=1时，分组ID自动递增：

IslandGroups AS (
    SELECT 
        MachineID,
        StartDowntime,
        EndDowntime,
        SUM(IsNewIsland) OVER (PARTITION BY MachineID ORDER BY StartDowntime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS IslandID
    FROM RankedDowntime
),

3. 合并区间并计算单岛时长

对每个分组（IslandID）取最小的开始时间和最大的结束时间，计算该岛的实际停机时长：

IslandDurations AS (
    SELECT 
        MachineID,
        MIN(StartDowntime) AS IslandStart,
        MAX(EndDowntime) AS IslandEnd,
        DATEDIFF(HOUR, MIN(StartDowntime), MAX(EndDowntime)) AS IslandDurationHours
    FROM IslandGroups
    GROUP BY MachineID, IslandID
)

4. 计算总实际停机时长

最后对所有岛的时长求和，就是你要的实际总停机时长：

SELECT SUM(IslandDurationHours) AS TotalActualDowntimeHours
FROM IslandDurations;

验证你的示例数据 #

用你给出的例子测试的话，这个查询会自动合并重叠部分：

• 机器A的14小时是独立岛，时长14
• 机器B的5小时和A重叠4小时，合并后实际新增1小时
• 机器C的1小时独立，时长1
• 机器D的2小时 + 机器E的1小时重叠，合并后时长2
• 机器F的2小时有1小时重叠，合并后新增1小时
  总和：14+1+1+2+1=19，完全符合你给出的结果。

注意事项 #

• 如果你的停机时长需要更精细的单位（比如分钟、秒），把DATEDIFF(HOUR, ...)改成DATEDIFF(MINUTE, ...)或DATEDIFF(SECOND, ...)即可
• 如果你的数据源是查询结果，直接把FROM MachineDowntime替换成你的查询语句就行

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