28天滑动窗口聚合的BigQuery SQL（无需编写28行SQL）

Question

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

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代码就能完成这个任务！

- alan

Answer 1

BigQuery文档对其支持的窗口函数的复杂性没有进行很好的解释，因为它没有指定可以在“ROWS”或“RANGE”之后出现的表达式。实际上，它支持SQL 2003标准的窗口函数，您可以在Web上的其他位置找到文档，例如这里。

这意味着您可以使用单个窗口函数获得所需的效果。范围为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;

- sprocket

---

文档可能已经更新，与原始帖子的时间不同：提示：如果您想使用日期范围，请使用UNIX_DATE()函数和ORDER BY。如果您想使用时间戳范围，请使用UNIX_SECONDS()、UNIX_MILLIS()或UNIX_MICROS()函数。参考：https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts#def_window_frame - Ivan P.

Answer 2

Bigquery: 如何在窗口子句中获取滚动时间范围...

虽然这是一篇旧文章，但我花了很长时间搜索解决方案，找到了这篇文章，也许会对其他人有所帮助。

如果你的窗口子句的分区（partition）没有每天的记录，则需要使用RANGE子句准确获取滚动时间范围（ROWS将搜索记录数，由于您的PARTITION BY中并非每天都有记录，因此会回溯太远）。问题是，在Bigquery中RANGE子句不支持日期。

从BigQuery的文档中可以看出：

numeric_expression 必须具有数字类型。DATE和TIMESTAMP目前不受支持。另外，numeric_expression必须是一个恒定的、非负整数或参数。

我找到的解决方法是在ORDER BY子句中使用UNIX_DATE(date_expression)以及RANGE子句：

SUM(value) OVER (PARTITION BY Column1 ORDER BY UNIX_DATE(Date) RANGE BETWEEN 5 PRECEDING AND CURRENT ROW

- ChrisL

---

非常有帮助，谢谢！我没有想到可以使用UNIX_DATE()函数在日期字段上进行范围查询。我希望BigQuery原生支持在RANGE子句中使用日期！我想回溯3个月，但必须使用UNIX_RANGE来近似90天。 - Albert Casademont

UNIX_DATE()是在BigQuery中解决此问题的方法，谢谢！ - Sami Navesi

Answer 3

我发现以下方法既灵活又有效：

WITH users AS
 (SELECT 'Isabella' as user, 1 as spend, DATE(2020, 03, 28) as date
  UNION ALL SELECT 'Isabella', 2, DATE(2020, 03, 29)
  UNION ALL SELECT 'Daniel', 3, DATE(2020, 03, 24)
  UNION ALL SELECT 'Andrew', 4, DATE(2020, 03, 23)
  UNION ALL SELECT 'Daniel', 5, DATE(2020, 03, 11)
  UNION ALL SELECT 'Jose', 6, DATE(2020, 03, 17))
SELECT 
user,
max(sum(case date_diff(date(2020,04,15), date, day) between 0 and 28
        when true then spend else 0 end)) over(partition by user) as spend_28_day_sum
FROM users
group by user

+------------------------------+
| user      | spend_28_day_sum |
+------------------------------+
| Andrew    | 4                |
| Daniel    | 3                |
| Isabella  | 3                |
| Jose      | 0                |
+------------------------------+

您可以更改“窗口函数”的指定日期为current_date()，或者使用生成的日期数组进行交叉连接，以查看用户随时间的变化。

- Mitchell Tracy

Answer 4

我找到了一种干净而优雅的方法来做到这一点，即使你在最后几天有缺失数据。

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

UNIX_DATE() 返回自 1970-01-01 以来的天数，因此我们可以将其与 RANGE() 函数结合使用，轻松计算要返回多少天。

- Manrique