基于6月1日起始财年的SQL财年/季度查询逻辑修正方案

核心计算逻辑：财年从每年6月1日开始、次年5月31日结束，财季从6月起每3个月为一个周期（6-8月为Q1、9-11月为Q2、12月-次年2月为Q3、3-5月为Q4）。所有日期过滤统一采用月份偏移法实现：将业务日期、系统日期同时向前偏移5个月，原本6月会对齐到自然年1月，5月对齐到自然年12月，可直接复用数据库原生的年、季度截断函数，避免字符串转换带来的性能问题和逻辑漏洞。

1. 当前财年查询修正 #

替换原自然年匹配逻辑，通过偏移计算得到当前财年的准确时间边界：

select
 dgl.LABEL                as goLiveName  
,dgl.GOLIVE_DATE_ACTUAL   as planningCurrent
,dgl.GOLIVE_DATE_PLANNED  as planningBaseline
,dgl.EFFECTIVE_START_DATE as effectiveStartDate
,dgl.EFFECTIVE_END_DATE   as effectiveEndDate
from   DATALAKE.DWL_GOLIVE dgl
      ,DATALAKE.DWB_PROJECT dp
where dgl.PROJECT_ID = dp.PROJECT_ID
-- 偏移5个月后按年截断，得到当前财年起始日（当年6月1日）
and   dgl.GOLIVE_DATE_PLANNED >= trunc(add_months(SYSDATE, -5), 'YYYY')
-- 小于财年起始+12个月（次年6月1日），完整覆盖到次年5月31日的所有数据
and   dgl.GOLIVE_DATE_PLANNED < add_months(trunc(add_months(SYSDATE, -5), 'YYYY'), 12)
AND   ( :year = 'true')

逻辑验证：2022年10月发起请求时，偏移5个月为2022年5月，按年截断得到2022-01-01，对应实际查询范围为2022-06-01至2023-05-31，完全匹配2022-2023财年要求。

2. 下一财年查询修正 #

在当前财年计算逻辑基础上，将时间范围整体向后偏移12个月即可：

select
 dgl.LABEL                as goLiveName  
,dgl.GOLIVE_DATE_ACTUAL   as planningCurrent
,dgl.GOLIVE_DATE_PLANNED  as planningBaseline
,dgl.EFFECTIVE_START_DATE as effectiveStartDate
,dgl.EFFECTIVE_END_DATE   as effectiveEndDate
from   DATALAKE.DWL_GOLIVE dgl
      ,DATALAKE.DWB_PROJECT dp
where dgl.PROJECT_ID = dp.PROJECT_ID
-- 下一财年起始为当前财年起始+12个月
and   dgl.GOLIVE_DATE_PLANNED >= add_months(trunc(add_months(SYSDATE, -5), 'YYYY'), 12)
-- 下一财年结束为当前财年起始+24个月
and   dgl.GOLIVE_DATE_PLANNED < add_months(trunc(add_months(SYSDATE, -5), 'YYYY'), 24)
AND   ( :nyear = 'true')

逻辑验证：2022年10月发起请求时，查询范围为2023-06-01至2024-05-31，匹配2023-2024财年要求。

3. 当前财季查询修正 #

沿用月份偏移逻辑，直接调用数据库原生季度截断函数即可对齐财季规则：

select
 dgl.LABEL                as goLiveName  
,dgl.GOLIVE_DATE_ACTUAL   as planningCurrent
,dgl.GOLIVE_DATE_PLANNED  as planningBaseline
,dgl.EFFECTIVE_START_DATE as effectiveStartDate
,dgl.EFFECTIVE_END_DATE   as effectiveEndDate
from   DATALAKE.DWL_GOLIVE dgl
      ,DATALAKE.DWB_PROJECT dp
where dgl.PROJECT_ID = dp.PROJECT_ID
-- 偏移5个月后按季度截断，得到当前财季起始日期
and   dgl.GOLIVE_DATE_PLANNED >= trunc(add_months(SYSDATE, -5), 'Q')
-- 小于财季起始+3个月，完整覆盖整个财季周期
and   dgl.GOLIVE_DATE_PLANNED < add_months(trunc(add_months(SYSDATE, -5), 'Q'), 3)
AND   ( :quarter = 'true')

逻辑验证：自然年2022年8月（属于财年Q1）发起请求时，偏移5个月为2022年3月，按季度截断得到2022-01-01，对应实际查询范围为2022-06-01至2022-09-01，完全匹配财年Q1范围；自然年2022年10月发起请求时，查询范围为2022-09-01至2022-12-01，对应财年Q2，符合规则。

4. 下一财季查询修正 #

在当前财季计算逻辑基础上，将时间范围整体向后偏移3个月即可：

select
 dgl.LABEL                as goLiveName  
,dgl.GOLIVE_DATE_ACTUAL   as planningCurrent
,dgl.GOLIVE_DATE_PLANNED  as planningBaseline
,dgl.EFFECTIVE_START_DATE as effectiveStartDate
,dgl.EFFECTIVE_END_DATE   as effectiveEndDate
from   DATALAKE.DWL_GOLIVE dgl
      ,DATALAKE.DWB_PROJECT dp
where dgl.PROJECT_ID = dp.PROJECT_ID
-- 下一财季起始为当前财季起始+3个月
and   dgl.GOLIVE_DATE_PLANNED >= add_months(trunc(add_months(SYSDATE, -5), 'Q'), 3)
-- 下一财季结束为当前财季起始+6个月
and   dgl.GOLIVE_DATE_PLANNED < add_months(trunc(add_months(SYSDATE, -5), 'Q'), 6)
AND   ( :nquarter = 'true')

逻辑验证：自然年2022年8月发起请求时，查询范围为2022-09-01至2022-12-01，对应财年Q2，符合下一财季要求。

优化说明：所有过滤条件均采用>= 起始时间 and < 结束时间的范围判断写法，不会遗漏结束日期当天带时分秒的时间戳类数据，兼容性远高于to_char转字符串的匹配方式，同时可以有效利用日期字段上的索引，查询性能更优。

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