Oracle SQL跨年度按周统计问题：仅返回当年数据的修复求助

现有一段Oracle SQL用于按周统计ITEM_ID的数量并返回每周的日期范围，原SQL在2023年运行时可正常返回当年各周统计结果，但2024年查询近3个月（跨2023和2024年）的数据时，仅返回2024年的统计结果。经排查，问题出在周数转日期的语句TO_DATE(WEEK *7, 'DDD')，该语句默认使用当前年份，无法正确处理跨年度的周数转换。

原SQL如下：

WITH DAT AS
  (
  SELECT 
  TO_CHAR(ISSUE_DATE - 7/24,'IYYY') YEAR, 
  TO_CHAR(ISSUE_DATE - 7/24,'IW') WEEK, 
  COUNT(ITEM_ID) ITEM_COUNT
FROM 
  MY_TABLE  
WHERE
  TO_CHAR(ISSUE_DATE, 'YYYY-MM-DD') >= TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -3)), 'YYYY-MM-DD')
GROUP BY
  TO_CHAR(ISSUE_DATE - 7/24,'IYYY'), 
  TO_CHAR(ISSUE_DATE - 7/24,'IW')
)

  SELECT
    NEXT_DAY(TO_DATE(WEEK *7, 'DDD')-8, 'mon') AS START_DATE, 
    NEXT_DAY(TO_DATE(WEEK *7, 'DDD'), 'sun') AS END_DATE,
    ITEM_COUNT CNT
  FROM 
    DAT

WITH DAT AS
  (
  SELECT 
    TO_CHAR(ISSUE_DATE - 7/24, 'IYYY') AS YEAR, 
    TO_CHAR(ISSUE_DATE - 7/24, 'IW') AS WEEK, 
    COUNT(ITEM_ID) AS ITEM_COUNT
  FROM 
    MY_TABLE  
  WHERE
    -- 优化日期比较：直接用日期类型运算，避免字符转换，提升性能
    ISSUE_DATE >= TRUNC(ADD_MONTHS(SYSDATE, -3))
  GROUP BY
    TO_CHAR(ISSUE_DATE - 7/24, 'IYYY'), 
    TO_CHAR(ISSUE_DATE - 7/24, 'IW')
)
SELECT
    -- 结合年份和周数生成对应ISO周的基准日期，再计算周起始（周一）
    TRUNC(TO_DATE(YEAR || 'W' || WEEK, 'IYYY"IW"'), 'IW') AS START_DATE,
    -- 周结束为起始日期加6天（周一+6天=周日）
    TRUNC(TO_DATE(YEAR || 'W' || WEEK, 'IYYY"IW"'), 'IW') + 6 AS END_DATE,
    ITEM_COUNT AS CNT
FROM 
    DAT
ORDER BY 
    START_DATE; -- 按周起始日期排序，确保结果有序

1. 跨年度周数转换修复：使用TO_DATE(YEAR || 'W' || WEEK, 'IYYY"IW"')直接将年份和ISO周数转换为对应年份的周基准日期，彻底解决默认年份导致的跨年度数据丢失问题。IYYY是ISO标准的年份格式，和IW（ISO周）完全匹配，确保周数和年份对应正确。
2. 日期比较优化：原WHERE子句通过TO_CHAR转换日期后比较，会导致索引失效（如果ISSUE_DATE有索引），修改为直接用日期类型ISSUE_DATE >= TRUNC(ADD_MONTHS(SYSDATE, -3))，既简洁又能利用索引提升查询效率。
3. 起止日期计算简化：利用TRUNC(..., 'IW')直接获取ISO周的周一作为起始日期，加6天得到周日作为结束日期，比原NEXT_DAY的写法更直观且不易出错（避免依赖会话的语言设置，NEXT_DAY的星期参数可能因语言不同失效）。

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