Oracle查询需求：将跨月周归为新月份第1周而非旧月份末周

要实现跨月周归属到新月份第1周的需求，我们需要修改原查询的月份和月份周数判定逻辑，核心是检查每周是否跨月，再调整归属：

WITH RCTE (THE_DATE) AS (
  -- 生成当年第一个周一作为起始周
  SELECT TRUNC(TRUNC(SYSDATE, 'YYYY'), 'IW')
  FROM DUAL
  UNION ALL
  -- 递归生成后续每周，覆盖全年
  SELECT THE_DATE + 7
  FROM RCTE
  WHERE THE_DATE < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12)
)
SELECT 
  THE_DATE AS WEEK_START,
  TO_CHAR(THE_DATE, 'YYYY') AS YEAR,
  -- 判断是否跨月：周起始日和周末日分属不同月份则为跨月周
  CASE 
    WHEN TRUNC(THE_DATE, 'MM') != TRUNC(THE_DATE + 6, 'MM') 
    THEN TO_CHAR(THE_DATE + 6, 'MM') 
    ELSE TO_CHAR(THE_DATE, 'MM') 
  END AS MONTH,
  -- 跨月周标记为新月份第1周，否则按原逻辑取当月周数
  CASE 
    WHEN TRUNC(THE_DATE, 'MM') != TRUNC(THE_DATE + 6, 'MM') 
    THEN 1 
    ELSE TO_NUMBER(TO_CHAR(THE_DATE, 'W')) 
  END AS MONTH_WEEK,
  TO_CHAR(THE_DATE, 'IYYY') AS ISO_YEAR,
  TO_CHAR(THE_DATE, 'IW') AS ISO_WEEK
FROM RCTE
ORDER BY THE_DATE;

逻辑说明 #

1. 周范围生成：从当年的第一个周一开始，递归生成每周的起始日期，覆盖整个自然年。
2. 跨月判定：通过对比周起始日（周一）和周末日（周日，THE_DATE+6）的月份，判断是否为跨月周。
3. 归属调整：
   • 跨月周：将月份设为周末日所在的月份，月份周数固定为1。
   • 非跨月周：保持原有的月份和当月周数计算逻辑。

以2025年3月30日的周为例：

• 周起始日为2025-03-30，周末日为2025-04-05，分属不同月份
• 最终该周会被标记为4月（04）第1周，符合需求。

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