Oracle SQL：拆分排班时段，排除休息时段计算有效工时

问题场景 #

机构部分用户采用拆分式排班，原始数据结构如下：

需要计算扣除Closed时段后的实际工作时段，期望输出：

解决方案 #

可以通过提取关键时间点、排序后配对的方式实现，以下是通用SQL示例（以SQL Server为例，其他数据库可调整时间函数）：

WITH user_time_points AS (
    -- 提取每个用户的Open时段起始、结束，以及Closed时段的起始、结束
    SELECT 
        user_name,
        start_time AS time_point,
        'open_start' AS point_type
    FROM schedule
    WHERE type = 'Open'
    UNION ALL
    SELECT 
        user_name,
        end_time AS time_point,
        'open_end' AS point_type
    FROM schedule
    WHERE type = 'Open'
    UNION ALL
    SELECT 
        user_name,
        start_time AS time_point,
        'close_start' AS point_type
    FROM schedule
    WHERE type = 'Closed'
    UNION ALL
    SELECT 
        user_name,
        end_time AS time_point,
        'close_end' AS point_type
    FROM schedule
    WHERE type = 'Closed'
),
sorted_points AS (
    -- 对每个用户的时间点按时间排序，并获取下一个时间点
    SELECT 
        user_name,
        time_point AS start_time,
        LEAD(time_point) OVER (PARTITION BY user_name ORDER BY time_point) AS end_time,
        point_type
    FROM user_time_points
)
-- 筛选出有效工作时段：起始点是open_start或close_end，结束点是close_start或open_end
SELECT 
    user_name,
    start_time,
    end_time
FROM sorted_points
WHERE 
    (point_type IN ('open_start', 'close_end'))
    AND end_time IS NOT NULL
    AND start_time < end_time
ORDER BY user_name, start_time;

逻辑说明 #

1. 提取时间点：把每个用户的Open时段的开始/结束、Closed时段的开始/结束都拆成单独的时间点，标记类型。
2. 排序配对：用LEAD()函数按时间顺序给每个时间点匹配下一个时间点，形成时段。
3. 筛选有效时段：只保留从Open开始或Closed结束，到Closed开始或Open结束的时段，这些就是扣除休息后的实际工作时间。

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