在Snowflake中计算过去N小时内的事件滚动计数

问题原因 #

你之前的子查询出错是因为内层子查询的列名和外层重复，导致timeadd('hour', -4, ingestion_timestamp)里的ingestion_timestamp引用的是内层表oc的字段，而非外层主查询的字段。这使得筛选条件变成了每个事件的时间在自身时间减4小时到自身时间，所有行都满足，最终返回全表总计数。

正确解决方案 #

方法1：使用窗口函数（推荐） #

Snowflake支持基于时间间隔的窗口范围，用RANGE BETWEEN INTERVAL可以高效计算滑动窗口内的计数，比子查询关联性能更好：

CREATE TABLE incident_counts AS
SELECT
    ingestion_timestamp AS incident_timestamp,
    COUNT(*) OVER (
        ORDER BY ingestion_timestamp
        RANGE BETWEEN INTERVAL '4 HOURS' PRECEDING AND CURRENT ROW
    ) AS count_incidents_in_previous_hours
FROM outliers_calculated;

方法2：修正子查询的关联条件 #

如果坚持用子查询，需要明确指定外层表的字段别名，避免列名冲突：

CREATE TABLE incident_counts AS
WITH incident_count AS (
    SELECT
        oc_main.ingestion_timestamp AS incident_timestamp,
        (SELECT COUNT(*)
         FROM outliers_calculated oc_sub
         WHERE oc_sub.ingestion_timestamp BETWEEN TIMEADD('hour', -4, oc_main.ingestion_timestamp) 
                                             AND oc_main.ingestion_timestamp) AS count_incidents_in_previous_hours
    FROM outliers_calculated oc_main
)
SELECT * FROM incident_count;

说明 #

• 方法1的窗口函数性能更优，尤其是数据量较大时，因为它不需要多次扫描表。
• 将INTERVAL '4 HOURS'替换为你需要的N小时即可，比如INTERVAL '2 HOURS'。
• 如果你的时间戳包含毫秒级精度，窗口函数依然可以正确处理时间范围。

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