Bigquery 移动平均线 [英] Bigquery moving average

问题描述

谁能告诉我如何在 bigquery 中计算移动平均值.

Can anybody tell me how to calculate moving average in bigquery.

这是我需要的 mysql 风格.

Here is what I need in mysql style.

SELECT T1.id, T1.value_column1, avg(T2.value_column1)
FROM table1 T1
INNER JOIN table1 T2 ON T2.Id BETWEEN T1.Id-19 AND T1.Id

推荐答案

有关更新和更有效的答案，https://stackoverflow.com/a/24943950/132438.

检查新的 LAG() 和 LEAD() 窗口函数.它们允许您遍历结果集，而无需自联接.

Check the new LAG() and LEAD() window functions. They allow you to traverse the result set, without the need for a self join.

https://developers.google.com/bigquery/docs/query-reference#windowfunctions

JOIN EACH 的不同选项(这可能会变得太慢，因为在中间步骤中可能会生成大量数据):

A different option with JOIN EACH (this can get too slow as an extremely large amount of data can get generated in the intermediate steps):

SELECT a.SensorId SensorId, a.Timestamp, AVG(b.Data) AS avg_prev_hour_load
FROM (
  SELECT * FROM [io_sensor_data.moscone_io13]
  WHERE SensorId = 'XBee_40670EB0/mic') a
JOIN EACH [io_sensor_data.moscone_io13] b
ON a.SensorId = b.SensorId
WHERE b.Timestamp BETWEEN (a.Timestamp - 3600000) AND a.Timestamp
GROUP BY SensorId, a.Timestamp;

(基于 Joe Celko 的 SQL 问题)

(based on Joe Celko's SQL problems)

这篇关于Bigquery 移动平均线的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！