如何在Snowflake中计算含迭代ID的每日滚动平均值

核心思路 #

要解决这个问题，关键是确保任意时间点只保留每个ID的最新迭代评级，再基于去重后的有效数据集计算滚动累积平均。具体分两步：

1. 为每个ID的迭代标记生效周期，确定每个日期下该ID的有效评级；
2. 基于有效数据集计算每日滚动累积平均值。

步骤1：生成每个ID的有效评级时间范围 #

假设你的表名为rating_tracking，包含字段：id（ID）、iteration（迭代次数）、rating_date（评级日期）、rating（评级值）。首先用窗口函数标记每个迭代的生效区间：

WITH ranked_ratings AS (
    SELECT 
        id,
        rating_date,
        rating,
        -- 获取每个ID下一次迭代的日期，确定当前评级的截止生效日
        LEAD(rating_date) OVER (PARTITION BY id ORDER BY rating_date) AS next_rating_date
    FROM rating_tracking
),
daily_valid_ratings AS (
    SELECT 
        d.date AS current_date,
        r.id,
        r.rating
    FROM 
        -- 生成需要统计的连续日期序列（替换为你的实际日期范围）
        (SELECT DATEADD(day, seq4(), '2023-01-01') AS date FROM TABLE(GENERATOR(ROWCOUNT => 365))) d
    LEFT JOIN ranked_ratings r 
        ON d.date >= r.rating_date 
        AND (d.date < r.next_rating_date OR r.next_rating_date IS NULL)
)

步骤2：计算每日滚动累积平均值 #

基于去重后的有效数据集，用滚动窗口函数计算累积平均：

SELECT 
    current_date,
    -- 计算截至当前日期所有有效评级的累积平均值
    AVG(rating) OVER (ORDER BY current_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rolling_avg
FROM daily_valid_ratings
GROUP BY current_date
ORDER BY current_date;

关键说明 #

• LEAD函数：精准标记每个ID的迭代更替节点，确保旧评级在新迭代生效后自动退出统计；
• 连续日期生成：用GENERATOR补全所有统计日期，避免因无更新记录导致的日期断层；
• 滚动窗口计算：按日期排序后，用UNBOUNDED PRECEDING实现从起始日到当前日的累积平均。

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