编写SQL查询排除重叠时间段，计算独立有效时长

针对你提出的消除重叠时间段、计算未被占用有效时长的需求，我会提供一个基于窗口函数的SQL解决方案，以下是详细步骤和代码：

问题分析 #

你的数据按日期分为独立批次（比如21号和24号），每个批次内的时间段存在重叠，需要实现：

• 保留同日期内最早开始的完整时段
• 后续时段仅保留未被之前时段覆盖的部分
• 完全被其他时段覆盖的记录直接排除（比如24号的Load shed和breakdown）

SQL解决方案（以MySQL为例） #

假设你的表名为event_schedule，字段分别为name、start_datetime、end_datetime（注意：先将字符串格式的日期转换为datetime类型，才能进行时间计算）：

WITH ranked_events AS (
    SELECT 
        name,
        STR_TO_DATE(start_datetime, '%d-%m-%Y %H:%i') AS start_dt,
        STR_TO_DATE(end_datetime, '%d-%m-%Y %H:%i') AS end_dt,
        -- 按日期分组，按开始时间排序，计算当前行之前所有时段的最大结束时间
        MAX(STR_TO_DATE(end_datetime, '%d-%m-%Y %H:%i')) OVER (
            PARTITION BY DATE(STR_TO_DATE(start_datetime, '%d-%m-%Y %H:%i'))
            ORDER BY STR_TO_DATE(start_datetime, '%d-%m-%Y %H:%i')
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prev_max_end
    FROM event_schedule
),
adjusted_events AS (
    SELECT
        name,
        -- 实际开始时间：取原开始时间和之前所有时段的最大结束时间的较大值
        GREATEST(start_dt, COALESCE(prev_max_end, start_dt)) AS adjusted_start,
        end_dt AS adjusted_end
    FROM ranked_events
    -- 过滤掉完全被覆盖的时段（实际开始时间 >= 结束时间的不保留）
    WHERE GREATEST(start_dt, COALESCE(prev_max_end, start_dt)) < end_dt
)
SELECT
    name,
    DATE_FORMAT(adjusted_start, '%d-%m-%Y %H:%i') AS `Start Date Time`,
    DATE_FORMAT(adjusted_end, '%d-%m-%Y %H:%i') AS `End Date time`,
    TIMEDIFF(adjusted_end, adjusted_start) AS Time_interval
FROM adjusted_events
ORDER BY adjusted_start;

代码逻辑解释 #

1. CTE ranked_events：

   • 将字符串日期转换为datetime类型，消除格式差异方便计算
   • 使用MAX() OVER()窗口函数，按日期分组、按开始时间排序，算出当前行之前所有时段的最晚结束时间（prev_max_end），以此确定当前时段需要避开的重叠边界

2. CTE adjusted_events：

   • 计算每个时段的实际开始时间：如果之前有重叠时段，就从之前的最晚结束时间开始；如果是同日期的第一行，就用原开始时间（COALESCE处理第一行无前置数据的情况）
   • 过滤掉完全被覆盖的记录（实际开始时间大于等于结束时间的记录无有效时长，直接排除）

3. 最终查询：

   • 将调整后的日期时间转回原字符串格式，计算并展示时长间隔Time_interval
   • 按调整后的开始时间排序，得到与你预期完全匹配的结果

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