Oracle数据库按月份拆分日期范围并计算对应自然日天数

工具选型结论 #

CONNECT BY 完全适配该日期拆分场景，是非常合适的实现工具。此前实现失败通常是因为遗漏了行级递归的绑定条件，导致跨请求的递归逻辑串线，生成错误结果。

核心实现逻辑 #

拆分过程严格匹配业务规则，分两步完成：

• 对每一条独立的请求记录，独立生成该请求日期范围覆盖的所有自然月的月初基准日期
• 对每个匹配到的自然月，计算请求日期范围和当月范围的交集长度，得到该月实际覆盖的自然日天数：
  • 取「请求开始日期」和「当月1号」中更晚的值作为交集起点
  • 取「请求结束日期」和「当月最后一天」中更早的值作为交集终点
  • 两个日期的自然日差值加1（包含首尾日期）即为当月的覆盖天数

常见实现踩坑说明 #

直接使用基础CONNECT BY语法拆分时，最容易出现两个问题导致结果不符：

• 遗漏行级递归绑定条件：未通过PRIOR REQUEST_ID = REQUEST_ID将递归范围限制在单条请求内，会导致不同请求的日期被串成连续范围统一拆分，出现大量跨请求的无效记录
• 触发Oracle递归循环误判：未添加PRIOR SYS_GUID() IS NOT NULL判定时，Oracle会将所有满足终止条件的递归路径判定为循环，直接抛出递归报错，无法返回结果

可直接运行的实现SQL #

SELECT
    REQUEST_ID,
    EXTRACT(YEAR FROM month_start) AS year,
    EXTRACT(MONTH FROM month_start) AS month,
    LEAST(END_DATE, LAST_DAY(month_start)) - GREATEST(START_DATE, month_start) + 1 AS days
FROM (
    SELECT
        REQUEST_ID,
        START_DATE,
        END_DATE,
        ADD_MONTHS(TRUNC(START_DATE, 'MONTH'), LEVEL - 1) AS month_start
    FROM REQUEST
    CONNECT BY
        -- 递归终止条件：需要生成的层级等于请求覆盖的自然月数量
        LEVEL <= MONTHS_BETWEEN(TRUNC(END_DATE, 'MONTH'), TRUNC(START_DATE, 'MONTH')) + 1
        -- 递归边界绑定：保证每个请求的递归只在自身行内执行，不跨请求串线
        AND PRIOR REQUEST_ID = REQUEST_ID
        -- 规避Oracle递归循环误判
        AND PRIOR SYS_GUID() IS NOT NULL
) split_result
ORDER BY REQUEST_ID, month_start;

效果验证 #

对于业务示例中日期范围为2020-12-28至2021-02-10的请求，上述SQL的输出完全符合预期：

内容的提问来源于stack exchange，提问作者Álvaro González