google-bigquery - 如何在 Google BigQuery 上计算移动总和/平均值？

Question

分析波动性太大的数据序列的趋势是很困难的。在许多情况下，使用平滑技术(例如移动平均线或移动总和)很有用。有很多工具可以执行此类操作，但是当我们谈论数百万行时，直接在云环境(例如 Google Big Query)中执行此操作很有用。

我的问题是: 如何计算 Google Big Query 上的移动总和/平均？

下面是我想要实现的移动平均线的数字:

Answer 1

下面是 BigQuery 标准 SQL

#standardSQL
SELECT
    pickup_date,
    number_of_trip,
    AVG(number_of_trip) OVER (ORDER BY day RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) AS mov_avg_7d,
    AVG(number_of_trip) OVER (ORDER BY day RANGE BETWEEN 27 PRECEDING AND CURRENT ROW) AS mov_avg_28d
FROM (
  SELECT 
    DATE(pickup_datetime) AS pickup_date,
    UNIX_DATE(DATE(pickup_datetime)) AS day,
    COUNT(*) AS number_of_trip
  FROM `nyc-tlc.yellow.trips` 
  GROUP BY 1, 2
)
WHERE pickup_date>'2013-01-01'

乍一看 - 这个答案看起来与 OP 的答案非常相似，所以只有一些关于这个答案的不同之处的评论:

首先(也是最不重要的) - 它是 BigQuery 团队强烈推荐使用的 BigQuery Standard SQL - 除非有很好的理由使用 Legacy SQL - 例如因为范围快照或一些非常特定于旧版 sql 的东西

其次，也是最重要的 - 在这种情况下使用 OVER 和 ROWS 不是最好的选择，因为它计算行数而不是天数，所以如果 - 偶然 - 任何给定的一天被错过 - 计算将分别使用过去 8 天和 29 天(而不是7 和 28)
在这种情况下，应该将 OVER 与 RANGE 一起使用