google-bigquery - 有没有办法在 Big Query 中进行滚动平均？

Question

我知道 Big Query 中有一个 AVG 函数，并且有窗口函数可以将前一个或下一个值向上或向下移动一行，但是是否有任何函数可以让您在指定的时间间隔内求平均值？例如，我想像下面这样:

SELECT
    city
    AVG(temperature) OVER(PARTITION BY city, INTERVAL day,14, ORDER BY day) as rolling_avg_14_days,
    AVG(temperature) OVER(PARTITION BY city, INTERVAL day,30, ORDER BY day) as rolling_avg_30_days,
WHERE
    city IN ("Los Angeles","Chicago","Sun Prairie","Sunnyvale")
    AND year BETWEEN 1900 AND 2013

我想做一个滚动平均计算，它允许我指定一个值范围来执行聚合函数，以及排序的值。平均函数将采用当天温度和前 13 天(或前 29 天)来计算和平均。今天这可能吗？我知道如果我在 SELECT 语句中放置 13 个 LAG/OVER 字段，然后对所有这些字段的结果求平均值，我可以做这样的事情，但这是很多开销。

Answer 1

我想 OVER with RANGE施工Window Functions最适合这里

假设该天字段表示为 'YYYY-MM-DD' 格式，下面的查询是滚动平均值

SELECT
  city,
  day,
  AVG(temperature) OVER(PARTITION BY city ORDER BY ts 
                RANGE BETWEEN 14*24*3600 PRECEDING AND CURRENT ROW) AS rolling_avg_14_days,
  AVG(temperature) OVER(PARTITION BY city ORDER BY ts 
                RANGE BETWEEN 30*24*3600 PRECEDING AND CURRENT ROW) AS rolling_avg_30_days
FROM (
  SELECT day, city, temperature, TIMESTAMP_TO_SEC(TIMESTAMP(day)) AS ts 
  FROM temperatures
)

您很可能很久以前就已经找到了这个解决方案，但仍然想在这里为这个问题提供我认为更好的答案(截至今天)