Snowflake SQL实现周五为起始日的YYYY-WK格式周提取方法

要实现以周五为周起始日的YYYY-WK格式输出，且仅通过SELECT语句完成，可以通过计算当年的首尾周五，结合日期归属判断来生成目标格式：

1. 定位当年的第一个周五：作为当年第1周的起始点
2. 定位当年的最后一个周五：若日期在该周五之后（周六及以后），则归为次年的第1周
3. 对于当年最后一个周五及之前的日期，计算其与当年第一个周五的周数差，得到对应周数

完整SQL代码 #

SELECT
    date_col,
    CONCAT(
        CASE
            WHEN date_col > last_friday_of_year THEN YEAR(date_col) + 1
            ELSE YEAR(date_col)
        END,
        '-',
        LPAD(
            CASE
                WHEN date_col > last_friday_of_year THEN 1
                ELSE DATE_DIFF(WEEK, first_friday_of_year, date_col) + 1
            END,
            2,
            '0'
        )
    ) AS yyyy_wk
FROM (
    SELECT
        date_col,
        -- 计算当年的第一个周五
        DATEADD(DAY, (6 - DAYOFWEEK(DATE_TRUNC(YEAR, date_col))) % 7, DATE_TRUNC(YEAR, date_col)) AS first_friday_of_year,
        -- 计算当年的最后一个周五
        DATEADD(
            DAY,
            -(DAYOFWEEK(DATEADD(DAY, -1, DATE_TRUNC(YEAR, DATEADD(YEAR, 1, date_col)))) - 6) % 7,
            DATEADD(DAY, -1, DATE_TRUNC(YEAR, DATEADD(YEAR, 1, date_col)))
        ) AS last_friday_of_year
    FROM your_table -- 替换为你的实际表名
) t;

代码说明 #

• DAYOFWEEK函数返回值规则：1=周日，2=周一，…，6=周五，7=周六，通过偏移计算精准定位当年的首尾周五
• DATE_TRUNC(YEAR, date_col)获取当年1月1日，DATE_TRUNC(YEAR, DATEADD(YEAR, 1, date_col))获取次年1月1日，减1天得到当年最后一天
• LPAD函数确保周数为两位数字格式（如01、02）
• 子查询预计算首尾周五，主查询通过CASE分支判断日期所属的年份和周数

测试验证 #

• 输入2023-12-29（当年最后一个周五），输出2023-51
• 输入2023-12-31（当年最后一个周五之后的日期），输出2024-01

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