Snowflake SQL实现周六为起始的周按月份天数规则分配

核心逻辑说明 #

针对每周以周六为起始的规则，判断周归属月份的标准：若该周内属于某月份的天数≥4，则周归属该月份；否则归属下一个月。

完整SQL实现 #

以下示例以date_col作为输入日期列，最终输出周起始/结束日期及对应归属月份：

WITH date_info AS (
    SELECT
        date_col,
        -- 计算当前日期所在周的起始周六
        DATEADD(day, -MOD(DATE_PART(dayofweek, date_col) - 6, 7), date_col) AS week_start_date,
        -- 计算当前周的结束日期（起始+6天，即下周五）
        DATEADD(day, 6, DATEADD(day, -MOD(DATE_PART(dayofweek, date_col) - 6, 7), date_col)) AS week_end_date,
        -- 当前日期所在月份的第一天和最后一天
        DATE_TRUNC('month', date_col) AS current_month_start,
        LAST_DAY(date_col) AS current_month_end
    FROM your_table
)
SELECT
    date_col,
    week_start_date,
    week_end_date,
    -- 判断周归属的月份（返回月份起始日）
    CASE
        WHEN DATEDIFF(day, 
                      GREATEST(week_start_date, current_month_start), 
                      LEAST(week_end_date, current_month_end)) + 1 >= 4
        THEN current_month_start
        ELSE DATEADD(month, 1, current_month_start)
    END AS assigned_month,
    -- 可选：格式化归属月份为YYYY-MM格式
    TO_CHAR(
        CASE
            WHEN DATEDIFF(day, GREATEST(week_start_date, current_month_start), LEAST(week_end_date, current_month_end)) + 1 >= 4
            THEN current_month_start
            ELSE DATEADD(month, 1, current_month_start)
        END, 'YYYY-MM'
    ) AS assigned_month_str
FROM date_info;

关键函数解释 #

• DATE_PART(dayofweek, date_col)：Snowflake中返回日期的星期几（周日=0，周一=1，…，周六=6），通过偏移量计算得到当前日期所在周的起始周六。
• GREATEST/LEAST：取周范围和当前月份的交集区间，用DATEDIFF+1计算该区间的天数。
• CASE分支：根据交集天数是否≥4，决定周归属当前月还是下月。

示例验证 #

• 对于2023-04-29（周六）：周范围为2023-04-29至2023-05-05，周内属于4月的天数为2天，不足4天，归属5月。
• 对于2023-05-23：周范围为2023-05-20（周六）至2023-05-26（周五），周内属于5月的天数为7天，≥4天，归属5月。

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