SQL中重叠停机时间计算：排除重叠时段统计分钟数

要处理重叠时段并计算有效停机时长，核心思路是先合并每个服务的重叠/连续停机区间，再计算合并后区间的总时长。以下是通用SQL方案，适配主流数据库（仅日期计算函数可能略有差异）。

步骤说明 #

1. 标记重叠分组：按服务ID分组，对每条停机记录按开始时间排序，通过窗口函数判断当前记录与上一条是否重叠，生成分组标识——重叠/连续的记录会被分到同一组。
2. 合并区间：按服务ID和分组标识聚合，取每组的最早开始时间和最晚结束时间，得到无重叠的独立区间。
3. 计算总时长：对每个合并后的区间计算时长（分钟），再按服务ID汇总总停机时长。

通用SQL代码 #

假设表结构为：Downtime(service_id INT, start_time TIMESTAMP, end_time TIMESTAMP)

WITH ranked_downtime AS (
    SELECT
        service_id,
        start_time,
        end_time,
        -- 生成分组ID：当前记录开始时间 >= 上一条结束时间时，开启新分组
        SUM(CASE WHEN start_time >= LAG(end_time) OVER (PARTITION BY service_id ORDER BY start_time) THEN 1 ELSE 0 END) 
        OVER (PARTITION BY service_id ORDER BY start_time) AS group_id
    FROM Downtime
),
merged_intervals AS (
    SELECT
        service_id,
        MIN(start_time) AS interval_start,
        MAX(end_time) AS interval_end
    FROM ranked_downtime
    GROUP BY service_id, group_id
)
SELECT
    service_id,
    -- 计算总分钟数：将时间差转为秒后除以60
    SUM(EXTRACT(EPOCH FROM (interval_end - interval_start)) / 60) AS total_downtime_minutes
FROM merged_intervals
GROUP BY service_id;

不同数据库适配调整 #

MySQL #

将最终计算时长的部分替换为：

SUM(TIMESTAMPDIFF(MINUTE, interval_start, interval_end)) AS total_downtime_minutes

SQL Server #

将最终计算时长的部分替换为：

SUM(DATEDIFF(MINUTE, interval_start, interval_end)) AS total_downtime_minutes

示例验证 #

针对你提到的示例数据：

• 第1-3行重叠记录会被合并为一个区间，计算出30分钟时长
• 第4、5行无重叠，各自保留为独立区间，分别计算5分钟、10分钟
  最终按服务ID汇总后，总时长为30+5+10=45分钟（若属于同一服务）。

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