Snowflake按周聚合及同周历史均值对比的实现方案咨询

步骤1：标准化日期并按周聚合 #

先将原始m/d/y格式的日期转换为Snowflake标准DATE类型，同时按周聚合交易数，标记出每个周所属的当月周序号（用于匹配对应周）：

WITH weekly_agg AS (
    SELECT
        "User",
        TO_DATE("Data", 'MM/DD/YY') AS data_date,
        DATE_TRUNC('WEEK', TO_DATE("Data", 'MM/DD/YY')) AS week_start,
        -- 标记该周是当月的第几周（1-5）
        WEEKOFMONTH(TO_DATE("Data", 'MM/DD/YY')) AS month_week_idx,
        YEAR(data_date) AS year,
        MONTH(data_date) AS month,
        SUM("Transaction Count") AS weekly_total
    FROM your_table_name
    GROUP BY "User", week_start, month_week_idx, year, month
),

步骤2：定位最近完整周 #

获取已结束的最近一周数据，同时计算过去4个月的时间范围边界：

latest_complete_week AS (
    SELECT
        "User",
        week_start,
        month_week_idx,
        weekly_total AS current_week_total,
        -- 过去4个月的起始月份（比如当前2月，起始为去年10月）
        DATEADD(MONTH, -4, DATE_TRUNC('MONTH', CURRENT_DATE())) AS range_start
    FROM weekly_agg
    -- 取上一周作为最近完整周（当前周未结束，不算完整周）
    WHERE week_start = DATE_TRUNC('WEEK', CURRENT_DATE()) - INTERVAL '1 WEEK'
)

步骤3：匹配对应周并计算均值 #

关联聚合数据，筛选出和最近完整周当月周序号相同、且在过去4个月范围内的数据，计算平均值：

SELECT
    lcw."User",
    lcw.current_week_total,
    -- 无对应数据时返回0，避免NULL
    COALESCE(AVG(wa.weekly_total), 0) AS past_4months_avg
FROM latest_complete_week lcw
LEFT JOIN weekly_agg wa
    ON lcw."User" = wa."User"
    AND lcw.month_week_idx = wa.month_week_idx
    -- 确保数据落在过去4个月的月份范围内
    AND DATE_FROM_PARTS(wa.year, wa.month, 1) BETWEEN lcw.range_start AND DATE_TRUNC('MONTH', lcw.week_start)
GROUP BY lcw."User", lcw.current_week_total;

注意事项 #

• 周起始日：Snowflake默认周从周日开始，若需改为周一，执行ALTER SESSION SET WEEK_START = 1;即可调整周计算逻辑。
• 跨年场景：DATE_FROM_PARTS会自动处理跨年月份（如2024年2月对应的过去4个月包含2023年10-12月）。
• 空值处理：COALESCE确保用户在对应周无数据时，平均值返回0而非NULL。

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