如何结合SYSDATE获取近两周YYYY+MM+周格式的时间数据？

问题背景 #

之前成功用以下SQL获取YYYYMMDD格式的日维度数据：

SELECT dates, trunc(calendar_date, 'DD') calendar_dates, weekday_nbr
FROM db.date
WHERE dates BETWEEN to_char(TRUNC(SYSDATE)-2, 'YYYYMMDD') AND to_char(TRUNC(SYSDATE)-1, 'YYYYMMDD')

现在需要查询7位数字格式的周维度数据（time字段为7位，格式为YYYY+MM+周序号，period和fiscal_week为2位数字），尝试的SQL未生效：

SELECT T time, period, fiscal_week
FROM db.time
WHERE time BETWEEN to_char(TRUNC(SYSDATE)-2, 'W') AND to_char(TRUNC(SYSDATE)-1,'W')

需求：通过TRUNC(SYSDATE)高效获取近两周数据，避免全量查询后过滤的性能损耗。

核心解决方案 #

问题出在直接用to_char(..., 'W')生成的周序号无法匹配7位的time字段，且字符串比较逻辑错误。正确思路是先计算近两周的时间范围，再转换为对应周的time格式，或者通过关联日期字段匹配周维度数据。

方案1：基于当月周序号生成time匹配值 #

假设time字段格式为YYYYMMW（4位年+2位月+1位当月周序号），以下SQL直接生成近两周的time标识并匹配：

WITH recent_weeks AS (
    SELECT 
        -- 当前ISO周（周一为起始）对应的time值
        TO_NUMBER(TO_CHAR(TRUNC(SYSDATE, 'IW'), 'YYYYMM') || TO_CHAR(TRUNC(SYSDATE, 'IW'), 'W')) AS current_week_time,
        -- 上一周对应的time值
        TO_NUMBER(TO_CHAR(TRUNC(SYSDATE, 'IW') - 7, 'YYYYMM') || TO_CHAR(TRUNC(SYSDATE, 'IW') - 7, 'W')) AS last_week_time
    FROM DUAL
)
SELECT t.time, t.period, t.fiscal_week
FROM db.time t
WHERE t.time IN (recent_weeks.current_week_time, recent_weeks.last_week_time);

• 若业务中周起始为周日，将'IW'替换为'W'即可。
• 用TO_NUMBER()确保与time字段的数字类型匹配，避免隐式转换导致索引失效。

方案2：通过日期范围关联周维度表 #

如果db.time表包含对应日期字段（如calendar_date），可以先计算近两周的日期范围，再通过日期关联查询：

WITH date_bounds AS (
    SELECT 
        TRUNC(SYSDATE, 'IW') - 13 AS two_weeks_ago_start, -- 近两周的起始日期（两周前的周一）
        TRUNC(SYSDATE, 'IW') - 1 AS last_week_end        -- 上一周的结束日期（上周日）
    FROM DUAL
)
SELECT t.time, t.period, t.fiscal_week
FROM db.time t
JOIN date_bounds db
    ON t.calendar_date BETWEEN db.two_weeks_ago_start AND db.last_week_end;

性能优化建议 #

• 为db.time表的time字段或calendar_date字段创建索引，避免全表扫描。
• 若使用财年周（fiscal_week），需结合财年规则调整日期截断逻辑（比如财年起始月份），确保生成的周范围符合业务定义。

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