SQL中按特殊企业规则从日期转换周数的实现方案

需求背景 #

需要在DB2、Redshift（支持DBeaver/SAS查询）中实现符合企业特定规则的日期转周数功能，现有数据库内置周函数均不满足要求，且禁止创建 lookup table。此前团队采用逐年编写大量CASE语句（每年52个WHEN子句）的方式，现需一套无需逐年维护的通用解决方案。

企业周数规则 #

• 每周始于周五，结束于下周四
• 周数上限为52，若计算出第53周则全部并入第52周
• 第52周始终以当年12月31日为结束日
• 第1周始终以当年1月1日为起始日
• 若第1周的天数≤3，则将第2周全部并入第1周，且当年后续所有周数减1

通用实现方案 #

以下针对不同数据库编写适配的SQL/SAS代码，核心逻辑无需硬编码年份，可自动适配所有年份：

DB2 实现 #

WITH date_params AS (
    SELECT
        DATE(YEAR(date_col) || '-01-01') AS year_start,
        DATE(YEAR(date_col) || '-12-31') AS year_end,
        date_col
    FROM your_table
),
week1_calc AS (
    SELECT
        year_start,
        year_end,
        date_col,
        -- 计算1月1日所在企业周的自然结束日（周四）
        -- DAYOFWEEK: 周日=1，周一=2...周五=6，周六=7
        (year_start + (11 - DAYOFWEEK(year_start)) % 7 DAYS) AS week1_natural_end,
        -- 计算第1周的天数
        ((11 - DAYOFWEEK(year_start)) % 7) + 1 AS week1_days
    FROM date_params
),
adjusted_week1 AS (
    SELECT
        year_start,
        year_end,
        date_col,
        -- 根据规则5调整第1周结束日
        CASE WHEN week1_days <=3 THEN week1_natural_end + 7 DAYS ELSE week1_natural_end END AS week1_final_end,
        week1_days
    FROM week1_calc
)
SELECT
    date_col,
    CASE
        -- 12月31日所在周强制为52
        WHEN date_col >= year_end - (DAYOFWEEK(year_end) - 5) % 7 DAYS THEN 52
        WHEN date_col <= week1_final_end THEN 1
        ELSE
            MIN(
                INT(DAYS_BETWEEN(date_col, week1_final_end) / 7) + CASE WHEN week1_days <=3 THEN 1 ELSE 2 END,
                51 -- 最后一周已强制为52，此处最多取51
            )
    END AS enterprise_week
FROM adjusted_week1;

Redshift 实现 #

WITH date_params AS (
    SELECT
        DATE_TRUNC('year', date_col)::DATE AS year_start,
        DATE_TRUNC('year', date_col)::DATE + INTERVAL '1 year' - INTERVAL '1 day' AS year_end,
        date_col
    FROM your_table
),
week1_calc AS (
    SELECT
        year_start,
        year_end,
        date_col,
        -- 计算1月1日所在企业周的自然结束日（周四）
        -- EXTRACT(DOW): 周日=0，周一=1...周五=5，周六=6
        year_start + ((9 - EXTRACT(DOW FROM year_start)) % 7)::INT AS week1_natural_end,
        -- 计算第1周的天数
        ((9 - EXTRACT(DOW FROM year_start)) % 7)::INT + 1 AS week1_days
    FROM date_params
),
adjusted_week1 AS (
    SELECT
        year_start,
        year_end,
        date_col,
        -- 根据规则5调整第1周结束日
        CASE WHEN week1_days <=3 THEN week1_natural_end + 7 ELSE week1_natural_end END AS week1_final_end,
        week1_days
    FROM week1_calc
)
SELECT
    date_col,
    CASE
        -- 12月31日所在周强制为52
        WHEN date_col >= year_end - ((EXTRACT(DOW FROM year_end) - 4) % 7)::INT THEN 52
        WHEN date_col <= week1_final_end THEN 1
        ELSE
            MIN(
                FLOOR(DATEDIFF(day, week1_final_end, date_col) / 7) + CASE WHEN week1_days <=3 THEN 1 ELSE 2 END,
                51
            )
    END AS enterprise_week
FROM adjusted_week1;

SAS 实现 #

data your_result;
    set your_table;
    /* 计算当年起始和结束日期 */
    year_start = INTNX('year', date_col, 0, 'b');
    year_end = INTNX('year', date_col, 0, 'e');
    
    /* 计算1月1日的星期几，WEEKDAY: 周日=1，周一=2...周五=6，周六=7 */
    dw_year_start = WEEKDAY(year_start);
    /* 计算1月1日所在企业周的自然结束日（周四） */
    week1_natural_end = year_start + (11 - dw_year_start) MOD 7;
    /* 第1周的天数 */
    week1_days = ((11 - dw_year_start) MOD 7) + 1;
    
    /* 根据规则5调整第1周结束日 */
    if week1_days <=3 then week1_final_end = week1_natural_end +7;
    else week1_final_end = week1_natural_end;
    
    /* 计算12月31日所在周的起始日 */
    dw_year_end = WEEKDAY(year_end);
    final_week_start = year_end - ((dw_year_end -5) MOD 7);
    
    /* 计算企业周数 */
    if date_col >= final_week_start then enterprise_week =52;
    else if date_col <= week1_final_end then enterprise_week =1;
    else do;
        days_diff = date_col - week1_final_end;
        base_week = FLOOR(days_diff /7) + (ifn(week1_days <=3, 1, 2));
        enterprise_week = min(base_week, 51);
    end;
run;

方案说明 #

• 所有实现均无需硬编码年份，通过动态计算当年的起始/结束日期、第1周参数自动适配任意年份
• 严格遵循所有企业规则：处理第1周的特殊合并逻辑、强制第52周以12月31日结束、周数上限52
• 避免了逐年编写CASE语句的维护成本，同时相比大量CASE语句，CTE/数据步的逻辑执行效率更高

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