基于周六起始周创建Snowflake DIM_DATE表YEAR_SAT_FRI列问题

你的问题出在原有逻辑依赖DAYOFWEEK的默认取值，若会话的周起始参数设置不同（比如周一为1），会导致计算的年度切换起始日期错误，从而遗漏2024/12/28这类日期。

推荐方案一：基于周结束日的年份直接计算 #

这种方式更通用，不受会话参数影响，直接通过日期所在周的结束日（周六起始的周，结束日为周五）的年份来确定归属：

SELECT 
    DATE,
    YEAR(DATEADD(DAY, 6, DATE_TRUNC('WEEK', DATE, 'SATURDAY'))) AS YEAR_SAT_FRI
FROM REPORTING.DIM_DATE

逻辑说明：

1. DATE_TRUNC('WEEK', DATE, 'SATURDAY')：获取当前日期所在周的起始日（明确指定周六为周起始）
2. DATEADD(DAY, 6, ...)：周起始日加6天，得到该周的结束日（周五）
3. YEAR(...)：取周结束日的年份，即为当前日期应归属的年度

推荐方案二：修正原有CASE逻辑（明确周起始） #

如果你偏好使用CASE语句，可改用DATE_TRUNC计算当年最后一个周六，避免DAYOFWEEK的参数依赖：

SELECT 
    DATE,
    CASE 
        WHEN DATE >= DATE_TRUNC('WEEK', LAST_DAY(DATE, 'YEAR'), 'SATURDAY')
        THEN YEAR(DATE) + 1
        ELSE YEAR(DATE)
    END AS YEAR_SAT_FRI
FROM REPORTING.DIM_DATE

逻辑说明：

1. LAST_DAY(DATE, 'YEAR')：获取当前日期所属年份的最后一天
2. DATE_TRUNC('WEEK', ..., 'SATURDAY')：获取当年最后一天所在周的起始日（即当年最后一个周六）
3. 若当前日期大于等于该周六，则归属下一年，否则归属当年

测试验证 #

• 2024/12/28：两个方案均返回2025
• 2024/12/27：两个方案均返回2024
• 2024/12/31：两个方案均返回2025
• 2025/01/01：两个方案均返回2025

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