如何用Oracle SQL按24小时拆分时长区间并生成明细行

原表结构与数据 #

ID | STARTDATE                | ENDDATE                  | Status
---+--------------------------+--------------------------+--------
1  | 2024-02-01 08:00:00.000  | 2024-02-02 10:30:00.000  | a
1  | 2024-02-02 10:30:00.000  | 2024-02-05 14:20:00.000  | b
1  | 2024-02-05 14:20:00.000  | 2024-02-07 08:15:00.000  | c

期望输出 #

ID | STARTDATE                | ENDDATE                 | Status
---+--------------------------+-------------------------+--------
1  | 2024-02-01 08:00:00.000  | 2024-02-02 00:00:00.000 | a 
1  | 2024-02-02 00:00:00.000  | 2024-02-02 10:30:00.000 | a
1  | 2024-02-02 10:30:00.000  | 2024-02-03 00:00:00.000 | b
1  | 2024-02-03 00:00:00.000  | 2024-02-04 00:00:00.000 | b
1  | 2024-02-04 00:00:00.000  | 2024-02-05 00:00:00.000 | b
1  | 2024-02-05 00:00:00.000  | 2024-02-05 14:20:00.000 | b 
1  | 2024-02-05 14:20:00.000  | 2024-02-06 00:00:00.000 | c
1  | 2024-02-06 00:00:00.000  | 2024-02-07 00:00:00.000 | c
1  | 2024-02-07 00:00:00.000  | 2024-02-07 08:15:00.000 | c

解决方案 #

使用Oracle的**递归CTE（Common Table Expression）**可以实现该需求，核心逻辑是递归生成每个时间区间内的每日分段：

WITH time_segments AS (
    -- 初始段：处理从原起始时间到当日午夜的分段
    SELECT 
        ID,
        STARTDATE AS seg_start,
        TRUNC(STARTDATE) + 1 AS seg_end,
        ENDDATE AS original_end,
        Status
    FROM your_table
    WHERE STARTDATE < TRUNC(STARTDATE) + 1

    UNION ALL

    -- 递归生成后续分段：完整整天或最后一段到原结束时间
    SELECT 
        ID,
        seg_end AS seg_start,
        CASE 
            WHEN TRUNC(seg_end) + 1 < original_end THEN TRUNC(seg_end) + 1
            ELSE original_end
        END AS seg_end,
        original_end,
        Status
    FROM time_segments
    WHERE seg_end < original_end
)
-- 整理输出结果并排序
SELECT 
    ID,
    seg_start AS STARTDATE,
    seg_end AS ENDDATE,
    Status
FROM time_segments
ORDER BY ID, seg_start;

代码说明 #

1. 初始CTE：针对每条记录，生成从原STARTDATE到当日午夜的第一段数据，若原起始时间恰好是午夜，这部分不会生成记录，由递归逻辑处理。
2. 递归逻辑：
   • 以上一段的结束时间作为下一段的起始时间。
   • 若下一段的午夜时间早于原ENDDATE，则生成完整整天的分段；否则生成到原ENDDATE的最后一段。
3. 最终查询：按ID和分段起始时间排序，输出符合要求的拆分结果。

注意事项 #

• 将代码中的your_table替换为实际表名。
• 确保STARTDATE和ENDDATE字段为DATE或TIMESTAMP类型，Oracle的TRUNC函数对这两种类型均兼容。

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