Snowflake中非均匀采样数据如何实现60分钟滑动窗口聚合

针对非均匀时间采样数据、Snowflake环境下无法直接用时间类型RANGE窗口的问题，以下两种方案均可直接落地：

方案1：ASOF关联聚合（生产环境首选，性能最优） #

核心逻辑是通过自关联，匹配每条记录对应城市下、时间落在「当前记录时间点往前60分钟到当前时间点」区间内的所有记录，再分组计算聚合值，完全不受采样频率不均匀的影响。

WITH base_data AS (
    SELECT
        CITY,
        TIMESTAMP,
        VALUE
    FROM SAMPLE_DATA
)
SELECT
    t1.CITY,
    t1.TIMESTAMP,
    t1.VALUE AS CURRENT_RECORD_VALUE,
    AVG(t2.VALUE) AS ROLLING_60MIN_AVG
    -- 可按需替换/新增其他聚合逻辑：SUM(t2.VALUE)、MAX(t2.VALUE)、COUNT(t2.VALUE)等
FROM base_data t1
LEFT JOIN base_data t2
    ON t1.CITY = t2.CITY
    AND t2.TIMESTAMP > DATEADD('MINUTE', -60, t1.TIMESTAMP)
    AND t2.TIMESTAMP <= t1.TIMESTAMP
-- 若需要窗口不含当前记录本身，把<=改为<、>改为>=即可
GROUP BY t1.CITY, t1.TIMESTAMP, t1.VALUE
ORDER BY t1.CITY, t1.TIMESTAMP;

优化提示：给CITY、TIMESTAMP字段建联合聚簇键，可大幅降低大表关联的计算量。

方案2：时间戳转数值后用RANGE窗口（写法最简洁，中小数据集首选） #

Snowflake窗口函数的RANGE模式仅支持数值类型排序键，不支持直接对时间戳类型做间隔范围划定。将时间戳转换为Unix秒级时间戳后，60分钟对应固定数值3600，即可直接用标准窗口语法实现滚动计算。

SELECT
    CITY,
    TIMESTAMP,
    VALUE,
    AVG(VALUE) OVER (
        PARTITION BY CITY
        ORDER BY UNIX_TIMESTAMP(TIMESTAMP)
        RANGE BETWEEN 3600 PRECEDING AND CURRENT ROW
    ) AS ROLLING_60MIN_AVG
FROM SAMPLE_DATA
ORDER BY CITY, TIMESTAMP;

注意事项：

• 若使用毫秒级时间戳，把窗口范围的3600替换为3600000即可
• 单表数据量超过千万级时，该方案性能弱于ASOF关联方案

原有代码的问题说明 #

你之前通过date_trunc('HOUR', TIMESTAMP)截断时间做分区的逻辑，窗口边界是固定的自然整点（比如13:00-13:59所有记录共享同一个窗口），不是以每条记录自身时间为起点往前推60分钟的动态滚动边界，因此不符合需求。

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