在Snowflake中实现滚动60日分组求和遇报错的解决咨询

问题背景 #

你现有一段实现滚动60行求和的SQL代码可正常运行：

WITH CTE AS (
  SELECT
    EVENT_DATE
    , CUSTOMERID
    , COUNT(*) AS NumberOfViews
  FROM TABLE
  AND EVENT_DATE >= DATEADD(DAY, -360, GETDATE()) -- 注：此处语法有误，应将AND改为WHERE
  GROUP BY EVENT_DATE, CUSTOMERID
)

SELECT
  EVENT_DATE
  , CUSTOMERID
  , SUM(NumberOfViews) OVER (PARTITION BY CUSTOMERID ORDER BY EVENT_DATE ASC ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS NumberOfViews60D
FROM CTE
ORDER BY
    CUSTOMERID, EVENT_DATE;

但改为滚动60日求和时，使用RANGE BETWEEN INTERVAL '60 DAY' PRECEDING语法触发了Invalid window frame错误：

WITH CTE AS (
  SELECT
    EVENT_DATE
    , CUSTOMERID
    , COUNT(*) AS NumberOfViews
  FROM TABLE
  AND EVENT_DATE >= DATEADD(DAY, -360, GETDATE()) -- 同样需将AND改为WHERE
  GROUP BY EVENT_DATE, CUSTOMERID
)

SELECT
  EVENT_DATE
  , CUSTOMERID
  , SUM(NumberOfViews) OVER (PARTITION BY CUSTOMERID ORDER BY EVENT_DATE ASC RANGE BETWEEN INTERVAL '60 DAY' PRECEDING AND CURRENT ROW) AS NumberOfViews60D
FROM CTE
ORDER BY
    CUSTOMERID, EVENT_DATE;

报错原因 #

不同SQL数据库对RANGE INTERVAL窗口框架的支持存在差异：
你的代码使用了GETDATE()，大概率是SQL Server环境——SQL Server不支持RANGE BETWEEN INTERVAL的语法，这是报错的核心原因。而PostgreSQL、BigQuery等部分数据库支持该语法。

解决方案 #

针对SQL Server环境

方案1：窗口函数结合条件聚合

通过计算日期差筛选60天范围内的数据，实现滚动求和：

WITH CTE AS (
  SELECT
    EVENT_DATE
    , CUSTOMERID
    , COUNT(*) AS NumberOfViews
  FROM TABLE
  WHERE EVENT_DATE >= DATEADD(DAY, -360, GETDATE()) -- 修正语法错误：AND改为WHERE
  GROUP BY EVENT_DATE, CUSTOMERID
)
SELECT
  EVENT_DATE
  , CUSTOMERID
  , SUM(CASE 
      WHEN DATEDIFF(DAY, c2.EVENT_DATE, c1.EVENT_DATE) BETWEEN 0 AND 60 
      THEN c2.NumberOfViews 
      ELSE 0 
    END) OVER (
      PARTITION BY c1.CUSTOMERID 
      ORDER BY c1.EVENT_DATE ASC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS NumberOfViews60D
FROM CTE c1
ORDER BY CUSTOMERID, EVENT_DATE;

方案2：关联子查询（逻辑更直观）

直接通过子查询匹配当前行60天范围内的同用户数据并求和：

WITH CTE AS (
  SELECT
    EVENT_DATE
    , CUSTOMERID
    , COUNT(*) AS NumberOfViews
  FROM TABLE
  WHERE EVENT_DATE >= DATEADD(DAY, -360, GETDATE())
  GROUP BY EVENT_DATE, CUSTOMERID
)
SELECT
  c1.EVENT_DATE
  , c1.CUSTOMERID
  , (
      SELECT SUM(c2.NumberOfViews) 
      FROM CTE c2 
      WHERE c2.CUSTOMERID = c1.CUSTOMERID 
        AND c2.EVENT_DATE BETWEEN DATEADD(DAY, -60, c1.EVENT_DATE) AND c1.EVENT_DATE
    ) AS NumberOfViews60D
FROM CTE c1
ORDER BY c1.CUSTOMERID, c1.EVENT_DATE;

针对支持RANGE INTERVAL的数据库（如PostgreSQL）

只需修正原代码的语法细节即可：

WITH CTE AS (
  SELECT
    EVENT_DATE
    , CUSTOMERID
    , COUNT(*) AS NumberOfViews
  FROM TABLE
  WHERE EVENT_DATE >= CURRENT_DATE - INTERVAL '360 days'
  GROUP BY EVENT_DATE, CUSTOMERID
)
SELECT
  EVENT_DATE
  , CUSTOMERID
  , SUM(NumberOfViews) OVER (
      PARTITION BY CUSTOMERID 
      ORDER BY EVENT_DATE ASC
      RANGE BETWEEN INTERVAL '60 days' PRECEDING AND CURRENT ROW
    ) AS NumberOfViews60D
FROM CTE
ORDER BY CUSTOMERID, EVENT_DATE;

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