Snowflake SQL中基于滑动窗口统计每行过去3个月的产品状态数量

问题 #

现有Snowflake数据库中的表格数据如下：

需要针对每行的Shipping_date，统计该日期过去3个月内对应产品的shipped和not_shipped状态数量，期望结果如下：

由于Snowflake SQL不支持带INTERVAL关键字的滑动窗口，原查询无法运行：

SELECT 
    Product
   , shipping_date
   , COUNT(case when status = 'shipped') OVER (
            ORDER BY record_date
            RANGE BETWEEN INTERVAL '3' MONTH PRECEDING AND CURRENT ROW
       ) AS count_last_three_months_shipped
    , COUNT(case when status = 'not_shipped') OVER (
            ORDER BY record_date
            RANGE BETWEEN INTERVAL '3' MONTH PRECEDING AND CURRENT ROW
       ) AS count_last_three_months_not_shipped
FROM 
    my_table

当前数据量较大，求高效的替代实现方式。

高效实现方案 #

核心思路 #

将日期转换为可用于RANGE窗口的数值类型（如YYYYMM格式整数），结合DATEADD计算每个日期的3个月前边界，再通过分区窗口函数统计范围内的状态数量。这种方式避开了Snowflake对INTERVAL滑动窗口的限制，同时保证执行效率。

实现SQL #

WITH date_transformed AS (
    SELECT 
        Product_name AS Product,
        Shipping_date,
        Status,
        -- 把日期转成YYYYMM格式的整数，用于RANGE窗口的数值范围判断
        DATE_PART('YEAR', Shipping_date) * 100 + DATE_PART('MONTH', Shipping_date) AS date_ym,
        -- 计算当前日期往前推3个月的YYYYMM数值，作为统计的起始边界
        DATE_PART('YEAR', DATEADD(MONTH, -3, Shipping_date)) * 100 + DATE_PART('MONTH', DATEADD(MONTH, -3, Shipping_date)) AS start_ym
    FROM my_table
)
SELECT 
    Product,
    Shipping_date,
    -- 统计过去3个月内的shipped数量（排除当前行，匹配期望结果逻辑）
    SUM(CASE WHEN Status = 'shipped' AND date_ym > start_ym THEN 1 ELSE 0 END) OVER (
        PARTITION BY Product
        ORDER BY Shipping_date DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS shipped_T3Months,
    -- 统计过去3个月内的not_shipped数量（排除当前行）
    SUM(CASE WHEN Status = 'not_shipped' AND date_ym > start_ym THEN 1 ELSE 0 END) OVER (
        PARTITION BY Product
        ORDER BY Shipping_date DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS not_shipped_T3Months
FROM date_transformed
ORDER BY Product, Shipping_date DESC;

性能优化说明 #

1. 分区与排序：通过PARTITION BY Product限定仅统计同一产品的数据，ORDER BY Shipping_date DESC让窗口从当前行向前遍历，减少无效计算。
2. 日期数值化：用YYYYMM整数替代日期类型，解决Snowflake不支持INTERVAL窗口的限制，RANGE窗口可基于数值范围高效过滤。
3. 条件内聚：在SUM的CASE语句中直接完成状态和日期范围的过滤，避免额外子查询开销。
4. 索引优化：若数据量极大，可给Product_name和Shipping_date建立联合索引，进一步提升窗口函数的执行效率。

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