sql - 用于 28 天滑动窗口聚合的 BigQuery SQL(无需编写 28 行 SQL)

Question

我正在尝试使用 LAG 函数在 BigQuery 中计算 28 天移动总和。

这个问题的最佳答案

Bigquery SQL for sliding window aggregate

来自 Felipe Hoffa 表示您可以使用 LAG 功能。一个例子是:

SELECT
    spend + spend_lagged_1day + spend_lagged_2day + spend_lagged_3day + ... +  spend_lagged_27day as spend_28_day_sum,
    user,
    date
FROM (
  SELECT spend,
         LAG(spend, 1) OVER (PARTITION BY user ORDER BY date) spend_lagged_1day,
         LAG(spend, 2) OVER (PARTITION BY user ORDER BY date) spend_lagged_2day,
         LAG(spend, 3) OVER (PARTITION BY user ORDER BY date) spend_lagged_3day,
         ...
         LAG(spend, 28) OVER (PARTITION BY user ORDER BY date) spend_lagged_day,
         user,
         date
  FROM user_spend
)

有没有办法不用写28行SQL就能做到这一点!

Answer 1

BigQuery 文档没有很好地解释该工具支持的窗口函数的复杂性，因为它没有指定哪些表达式可以出现在 ROWS 或 RANGE 之后。它实际上支持窗口函数的 SQL 2003 标准，您可以在网络上的其他地方找到该标准的文档，例如 here .

这意味着您可以通过单个窗口函数获得您想要的效果。范围是 27，因为它是当前行之前要包含在总和中的行数。

SELECT spend,
       SUM(spend) OVER (PARTITION BY user ORDER BY date ROWS BETWEEN 27 PRECEDING AND CURRENT ROW),
       user,
       date
FROM user_spend;

范围界限也非常有用。如果您的表缺少某些用户的日期，则 27 PRECEDING 行将返回超过 27 天，但 RANGE 将根据日期值本身生成一个窗口。在以下查询中，日期字段是 BigQuery TIMESTAMP，范围以微秒为单位指定。我建议您每当在 BigQuery 中进行这样的日期数学运算时，都要彻底测试它，以确保它为您提供预期的答案。

SELECT spend,
       SUM(spend) OVER (PARTITION BY user ORDER BY date RANGE BETWEEN 27 * 24 * 60 * 60 * 1000000 PRECEDING AND CURRENT ROW),
       user,
       date
FROM user_spend;