MySQL如何合并重叠时间区间并计算各时间段有效时长？

原SQL错误原因 #

• 子查询中的区间重叠判断逻辑不完整，仅能覆盖部分重叠场景，无法处理区间完全包含、首尾衔接等情况
• 最终查询按id分组，每一行保留单独的id分组，自然无法将多个重叠区间合并为同一行输出
• 时长计算使用TIMEDIFF存在溢出风险，当时间差超过839小时时会返回错误结果

适用MySQL 8.0+ 版本（支持窗口函数）的正确写法 #

WITH ranked_intervals AS (
    SELECT 
        start_date,
        end_date,
        -- 判断当前区间是否和上一个区间不重叠，不重叠则标记为新分组的起点
        SUM(CASE WHEN start_date > LAG(end_date) OVER (ORDER BY start_date) THEN 1 ELSE 0 END) 
            OVER (ORDER BY start_date) AS group_id
    FROM deneme
)
SELECT 
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date,
    -- 用TIMESTAMPDIFF计算避免溢出，单位秒转换为小时
    TIMESTAMPDIFF(SECOND, MIN(start_date), MAX(end_date)) / 3600 AS total_hours
FROM ranked_intervals
GROUP BY group_id
ORDER BY start_date;

适用MySQL 5.x 版本的兼容写法 #

SELECT 
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date,
    TIMESTAMPDIFF(SECOND, MIN(start_date), MAX(end_date)) / 3600 AS total_hours
FROM (
    SELECT 
        start_date,
        end_date,
        @group_id := IF(start_date > @prev_end, @group_id + 1, @group_id) AS group_id,
        @prev_end := GREATEST(end_date, @prev_end) AS current_max_end
    FROM deneme
    -- 初始化变量
    CROSS JOIN (SELECT @group_id := 0, @prev_end := '1970-01-01 00:00:00') AS vars
    ORDER BY start_date
) AS grouped_intervals
GROUP BY group_id
ORDER BY start_date;

输出示例 #

执行上述SQL后会得到你需要的合并后区间，同时返回每个区间的总时长（单位为小时）：

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