Snowflake中如何实现按每年1月1日为第1周的calendar_week计算？

问题原因 #

你之前使用的WEEK、WEEKISO函数默认遵循ISO周或者系统配置的周起始/周编号规则，不符合你要求的「每年1月1日固定为第01周起始」的自定义规则，所以结果不符合预期。

实现逻辑 #

按照需求，周编号计算逻辑为：

• 取当前日期对应年份的1月1日作为基准日期
• 计算当前日期与基准日期的天数差，向下取整除以7后加1，即为当前日期的周编号
• 如需输出两位带前导零的格式，可通过LPAD函数格式化

完整SQL代码 #

SELECT 
    starting_date,
    fiscal_week,
    -- 计算自定义calendar_week，输出两位带前导零格式
    LPAD(
        FLOOR(DATEDIFF('day', DATE_FROM_PARTS(YEAR(starting_date), 1, 1), starting_date) / 7) + 1,
        2,
        '0'
    ) AS calendar_week
FROM your_table_name;

结果验证 #

将你给出的测试日期代入计算：

• 2020-12-31：2020年1月1日到该日期天数差为365天，FLOOR(365/7)+1 = 52 +1 =53，输出53
• 2021-01-01：2021年1月1日到该日期天数差为0天，FLOOR(0/7)+1 = 0+1=1，输出01
• 2021-01-08：2021年1月1日到该日期天数差为7天，FLOOR(7/7)+1=1+1=2，输出02
  完全匹配你给出的预期输出。

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