我知道在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我想做一个滚动平均计算,它允许我指定要对聚合函数执行的值的范围,以及排序依据。average函数将使用当天的温度和前13天(或前29天)来计算和平均。这在今天是可能的吗?我知道,如果我在SELECT语句中放入13个LAG/OVER字段,然后平均所有这些字段的结果,我就可以做这样的事情,但这是很大的开销。
发布于 2016-02-18 10:19:08
我认为Window Functions的OVER with RANGE构造最适合这里
假设日期字段表示为'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
)您很可能很久以前就已经找到了这个解决方案,但仍然希望(到今天为止)这里有一个我认为更好的答案。
发布于 2014-03-08 07:29:25
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 - 36000000) AND a.Timestamp
GROUP BY SensorId, a.Timestamp;(基于Joe Celko的SQL问题)
对于窗口函数,让一个实现更大范围的窗口函数可能很有用,但现在我将自动生成查询。
https://stackoverflow.com/questions/22238101
复制相似问题