如何动态提取当前周前4个财周及上年同期财周？

数据集 #

DATE_VALUE  FISCAL_WEEK FISCAL_YEAR_VALUE
14-Dec-20   51          2020
15-Dec-20   51          2020
16-Dec-20   51          2020
17-Dec-20   51          2020
18-Dec-20   51          2020
19-Dec-20   51          2020
20-Dec-20   51          2020
21-Dec-20   52          2020
22-Dec-20   52          2020
23-Dec-20   52          2020
24-Dec-20   52          2020
25-Dec-20   52          2020
26-Dec-20   52          2020
27-Dec-20   52          2020
28-Dec-20   1           2021
29-Dec-20   1           2021
30-Dec-20   1           2021
31-Dec-20   1           2021
1-Jan-21    1           2021
2-Jan-21    1           2021
3-Jan-21    1           2021
4-Jan-21    2           2021
5-Jan-21    2           2021
6-Jan-21    2           2021
7-Jan-21    2           2021
8-Jan-21    2           2021
9-Jan-21    2           2021
10-Jan-21   2           2021
11-Jan-21   3           2021
12-Jan-21   3           2021
13-Jan-21   3           2021
14-Jan-21   3           2021
15-Jan-21   3           2021
16-Jan-21   3           2021
17-Jan-21   3           2021
18-Jan-21   4           2021
19-Jan-21   4           2021
20-Jan-21   4           2021
21-Jan-21   4           2021
22-Jan-21   4           2021
23-Jan-21   4           2021
24-Jan-21   4           2021
20-Dec-21   52          2021
21-Dec-21   52          2021
22-Dec-21   52          2021
23-Dec-21   52          2021
24-Dec-21   52          2021
25-Dec-21   52          2021
26-Dec-21   52          2021
27-Dec-21   53          2021
28-Dec-21   53          2021
29-Dec-21   53          2021
30-Dec-21   53          2021
31-Dec-21   53          2021
1-Jan-22    53          2021
2-Jan-22    53          2021
3-Jan-22    1           2022
4-Jan-22    1           2022
5-Jan-22    1           2022
6-Jan-22    1           2022
7-Jan-22    1           2022
8-Jan-22    1           2022
9-Jan-22    1           2022
10-Jan-22   2           2022
11-Jan-22   2           2022
12-Jan-22   2           2022
13-Jan-22   2           2022
14-Jan-22   2           2022
15-Jan-22   2           2022
16-Jan-22   2           2022
17-Jan-22   3           2022
18-Jan-22   3           2022
19-Jan-22   3           2022
20-Jan-22   3           2022
21-Jan-22   3           2022
22-Jan-22   3           2022
23-Jan-22   3           2022
24-Jan-22   4           2022
25-Jan-22   4           2022
26-Jan-22   4           2022
27-Jan-22   4           2022
28-Jan-22   4           2022
29-Jan-22   4           2022
30-Jan-22   4           2022

需求 #

提取当前测试日期（21-JAN-22）对应财周之前的4个财周，以及上一年度的对应4个财周，期望输出如下：

FISCAL_YEAR_VALUE   FISCAL_WEEK
2020                51
2020                52
2021                2
2021                1
2021                52
2021                53
2022                1
2022                2

现有代码问题 #

当前使用的SQL代码仅能处理4个财周同属一年的场景，在跨年度时会遗漏上一年的对应财周（如测试场景中缺少2020年的51、52财周），现有代码如下：

SELECT 
  FISCAL_YEAR_VALUE,
  FISCAL_WEEK
FROM TABLE_NAME  

WHERE FISCAL_YEAR_VALUE IN (SELECT * 
FROM (WITH T AS (
    SELECT DISTINCT FISCAL_YEAR_VALUE
    FROM TABLE_NAME
    WHERE TRUNC(DATE_VALUE) <= TRUNC(TO_DATE('21-JAN-22'))--TEST DATE
    ORDER BY FISCAL_YEAR_VALUE DESC
    FETCH NEXT 2 ROWS ONLY
               )
SELECT FISCAL_YEAR_VALUE
FROM T ORDER BY FISCAL_YEAR_VALUE
    )   
                                )                             
AND FISCAL_WEEK IN (SELECT *
FROM (WITH T AS (
    SELECT DISTINCT FISCAL_WEEK, FISCAL_YEAR_VALUE
    FROM TABLE_NAME 
    WHERE TRUNC(DATE_VALUE) <= TRUNC(TO_DATE('21-JAN-22'))--TEST DATE
    ORDER BY FISCAL_YEAR_VALUE DESC, FISCAL_WEEK DESC
    OFFSET 1 ROWS
    FETCH NEXT 4 ROWS ONLY
                )
SELECT FISCAL_WEEK
FROM T ORDER BY FISCAL_YEAR_VALUE, FISCAL_WEEK
    )    
                    )
GROUP BY FISCAL_YEAR_VALUE, FISCAL_WEEK
ORDER BY FISCAL_YEAR_VALUE, FISCAL_WEEK

修改后的解决方案 #

思路 #

1. 先确定测试日期对应的财周和财年，作为基准点
2. 为每个唯一的（财年+财周）组合计算逻辑排序值，处理跨年财周的顺序问题（如2020年52周 > 2021年1周）
3. 筛选出基准财周之前的4个财周
4. 匹配上一年度的对应财周编号
5. 合并两组结果并去重排序

适配后的SQL代码 #

WITH base_week AS (
    -- 获取测试日期对应的财年和财周
    SELECT DISTINCT FISCAL_YEAR_VALUE AS base_year, FISCAL_WEEK AS base_week
    FROM TABLE_NAME
    WHERE TRUNC(DATE_VALUE) <= TRUNC(TO_DATE('21-JAN-22')) -- 测试日期
    AND TRUNC(DATE_VALUE) >= TRUNC(TO_DATE('21-JAN-22')) - 6 -- 确保取到当前财周
),
unique_weeks AS (
    -- 生成所有唯一的财年+财周组合，并计算逻辑排序值
    SELECT DISTINCT 
        FISCAL_YEAR_VALUE, 
        FISCAL_WEEK,
        -- 计算财周的逻辑顺序：财年*100 + 财周，处理跨年场景
        CASE 
            WHEN FISCAL_WEEK <= 10 AND FISCAL_YEAR_VALUE = (SELECT base_year FROM base_week) 
            THEN (FISCAL_YEAR_VALUE - 1)*100 + FISCAL_WEEK + 52 -- 年初财周归到上一年的顺序末尾
            ELSE FISCAL_YEAR_VALUE*100 + FISCAL_WEEK 
        END AS week_order
    FROM TABLE_NAME
),
target_weeks AS (
    -- 筛选当前基准财周之前的4个财周
    SELECT FISCAL_YEAR_VALUE, FISCAL_WEEK
    FROM unique_weeks
    WHERE week_order < (SELECT 
                            CASE 
                                WHEN base_week <=10 THEN (base_year -1)*100 + base_week +52 
                                ELSE base_year*100 + base_week 
                            END 
                        FROM base_week)
    ORDER BY week_order DESC
    FETCH NEXT 4 ROWS ONLY
),
prev_year_weeks AS (
    -- 匹配上一年度的对应财周
    SELECT 
        uw.FISCAL_YEAR_VALUE, 
        uw.FISCAL_WEEK
    FROM unique_weeks uw
    JOIN target_weeks tw ON uw.FISCAL_WEEK = tw.FISCAL_WEEK
    WHERE uw.FISCAL_YEAR_VALUE = (SELECT base_year -1 FROM base_week)
)
-- 合并当前4个财周和上一年对应4个财周，去重排序
SELECT * FROM target_weeks
UNION ALL
SELECT * FROM prev_year_weeks
ORDER BY FISCAL_YEAR_VALUE, FISCAL_WEEK;

说明 #

• base_week CTE：精准定位测试日期所在的财年和财周，避免日期边界问题
• unique_weeks CTE：通过week_order字段将跨年的财周按实际时间顺序排序（比如2020年52周的排序值大于2021年1周）
• target_weeks CTE：基于逻辑排序值筛选出基准财周之前的4个财周，自动适配跨年场景
• prev_year_weeks CTE：直接匹配上一年度的相同财周编号，确保获取对应周期
• 最后通过UNION ALL合并结果并排序，得到期望输出

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