如何在Oracle PL/SQL中为每个分组生成日期范围内的月度周期

需求说明 #

针对每个分组的日期范围，拆分出覆盖该范围的所有月度周期：

• 首月周期：起始为分组的开始日期，结束为当月最后一天
• 中间月份周期：完整的当月（1号到当月最后一天）
• 末月周期：起始为当月1号，结束为分组的结束日期

例如：

• 分组A日期范围01/01/2023-03/10/2023，拆分后得到3个月度区间
• 分组B日期范围01/05/2023-05/12/2023，拆分后得到5个月度区间

解决方案（Oracle SQL实现） #

可以通过分层查询结合日期函数实现，无需复杂PL/SQL块：

WITH group_dates AS (
    -- 模拟分组日期范围数据，替换为实际业务表即可
    SELECT 'A' AS group_id, TO_DATE('01/01/2023', 'MM/DD/YYYY') AS start_dt, TO_DATE('03/10/2023', 'MM/DD/YYYY') AS end_dt FROM DUAL
    UNION ALL
    SELECT 'B' AS group_id, TO_DATE('01/05/2023', 'MM/DD/YYYY') AS start_dt, TO_DATE('05/12/2023', 'MM/DD/YYYY') AS end_dt FROM DUAL
)
SELECT
    gd.group_id AS "Group",
    -- 确定每个周期的起始日期：首月用分组开始日期，后续月份取当月1号
    GREATEST(gd.start_dt, TRUNC(ADD_MONTHS(gd.start_dt, LEVEL - 1), 'MM')) AS "Start",
    -- 确定每个周期的结束日期：末月用分组结束日期，其他月份取当月最后一天
    LEAST(gd.end_dt, LAST_DAY(ADD_MONTHS(gd.start_dt, LEVEL - 1))) AS "End"
FROM group_dates gd
CONNECT BY
    -- 按分组分层，避免跨分组关联
    PRIOR group_id = group_id
    AND PRIOR SYS_GUID() IS NOT NULL
    -- 生成的月份不超过结束日期所在月份
    ADD_MONTHS(gd.start_dt, LEVEL - 1) <= gd.end_dt
ORDER BY group_id, "Start";

代码说明 #

1. CTE group_dates：模拟存储分组日期范围的业务表，实际使用时替换为你的数据表即可
2. GREATEST函数：保证首月起始日期不早于分组的开始日期，后续月份自动取当月1号
3. LEAST+LAST_DAY函数：保证末月结束日期不晚于分组的结束日期，中间月份自动取当月最后一天
4. CONNECT BY条件：按分组生成层级数据，PRIOR SYS_GUID() IS NOT NULL用于避免循环，同时限制生成的月份不超出分组的结束日期范围

执行结果 #

Group      Start            End
A          01/01/2023       01/31/2023
A          02/01/2023       02/28/2023
A          03/01/2023       03/10/2023

B          01/05/2023       01/31/2023
B          02/01/2023       02/28/2023
B          03/01/2023       03/31/2023
B          04/01/2023       04/30/2023
B          05/01/2023       05/12/2023

内容的提问来源于stack exchange，提问作者Любовь Пономарева