Oracle中如何通过SQL按财年拆分两个日期区间
Oracle 按财年规则拆分跨区间日期实现方案
需求说明
财年规则为每年4月1日至次年3月31日,需要将数据表中跨多个财年的连续日期区间,拆分为对齐单个财年边界的独立子区间。
原始测试数据
NAME From_Date To_Date ABC 01-MAR-2020 30-APR-2022
预期输出结果
NAME From_Date To_Date ABC 01-MAR-2020 31-MAR-2020 ABC 01-APR-2020 31-MAR-2021 ABC 01-APR-2021 31-MAR-2022 ABC 01-APR-2022 30-APR-2022
实现核心逻辑
- 递归定位原始日期区间覆盖的所有财年的起始、结束边界
- 对每个覆盖到的财年,取「原始起始日期与财年起始日期的较大值」作为拆分段的起始日期
- 对每个覆盖到的财年,取「原始结束日期与财年结束日期的较小值」作为拆分段的结束日期
- 过滤掉两个区间无交叉的无效记录,得到最终拆分结果
可运行SQL
WITH base_data AS ( -- 替换为实际业务表的查询逻辑 SELECT 'ABC' AS name, DATE '2020-03-01' AS from_date, DATE '2022-04-30' AS to_date FROM DUAL ), fy_boundaries AS ( -- 生成覆盖原始区间的所有财年边界 SELECT name, to_date, -- 计算财年起始日(每年4月1日) ADD_MONTHS(TRUNC(ADD_MONTHS(from_date, -3), 'YEAR'), 3) AS fy_start, -- 计算财年结束日(次年3月31日) ADD_MONTHS(TRUNC(ADD_MONTHS(from_date, -3), 'YEAR'), 15) - 1 AS fy_end FROM base_data -- 递归遍历直到覆盖原始结束日期所在的完整财年 CONNECT BY ADD_MONTHS(fy_start, 12) <= ADD_MONTHS(TRUNC(ADD_MONTHS(to_date, -3), 'YEAR'), 15) - 1 AND PRIOR name = name AND PRIOR SYS_GUID() IS NOT NULL ) SELECT name, GREATEST(from_date, fy_start) AS from_date, LEAST(to_date, fy_end) AS to_date FROM fy_boundaries -- 过滤无交叉的无效区间 WHERE GREATEST(from_date, fy_start) <= LEAST(to_date, fy_end) ORDER BY from_date;
注意事项
- 财年边界的计算通过日期偏移3个月后对齐自然年实现,无需硬编码财年年份,适配任意时长的跨年度区间
- 所有拆分段通过
GREATEST/LEAST计算交集,不会出现超出原始区间的错误日期值 - 递归生成边界时通过
SYS_GUID()避免循环递归,保证长区间下的执行稳定性
内容的提问来源于stack exchange,提问作者M10
相关产品推荐
相关产品推荐

