Oracle中按指定允许时段拆分跨天时间间隙的SQL实现方法

实现思路 #

• 首先将每条原始时段记录，拆分生成该时段覆盖的所有自然日对应的独立行
• 对每个自然日，计算当日的允许时段边界：当日7:00（TRUNC(当日日期) + 7/24）、当日20:00（TRUNC(当日日期) + 20/24）
• 取原始时段和当日允许时段的交集：有效开始时间取原始开始时间和当日允许开始时间的较大值，有效结束时间取原始结束时间和当日允许结束时间的较小值
• 过滤掉有效开始时间大于等于有效结束时间的无效行，得到最终结果

示例代码 #

假设你的表名为calendar_gap，字段为begindate、enddate，SQL如下：

WITH date_split AS (
    -- 拆分每条记录为覆盖的所有自然日
    SELECT 
        t.begindate,
        t.enddate,
        TRUNC(t.begindate) + LEVEL - 1 AS curr_date
    FROM calendar_gap t
    CONNECT BY LEVEL <= TRUNC(t.enddate) - TRUNC(t.begindate) + 1
        AND PRIOR t.begindate = t.begindate
        AND PRIOR t.enddate = t.enddate
        AND PRIOR SYS_GUID() IS NOT NULL
)
SELECT 
    GREATEST(begindate, curr_date + 7/24) AS begindate,
    LEAST(enddate, curr_date + 20/24) AS enddate
FROM date_split
WHERE GREATEST(begindate, curr_date + 7/24) < LEAST(enddate, curr_date + 20/24)
ORDER BY begindate;

如果是Oracle 11g R2及以上版本，也可以用递归CTE替代CONNECT BY实现日期拆分，逻辑完全一致。

代码说明 #

• 拆分部分用CONNECT BY生成连续日期，PRIOR SYS_GUID() IS NOT NULL是为了避免多行记录递归时出现循环报错的问题
• GREATEST、LEAST函数分别用来取两个时间的最大值、最小值，快速计算交集边界
• 最后的WHERE条件过滤掉没有有效交集的日期行

测试验证 #

用你给出的测试数据执行上述SQL，输出结果和预期完全一致：

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