Bigquery均线 [英] Bigquery moving average

问题描述

有人可以告诉我如何计算bigquery中的移动平均数。

以下是我需要的mysql样式。

  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（）窗口函数。它们允许您遍历结果集，而无需进行自我连接。

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

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

  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问题）

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

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

解决方案

For an updated and more efficient answer, https://stackoverflow.com/a/24943950/132438.

---

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

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;

(based on Joe Celko's SQL problems)

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