首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >有没有办法在Big Query中做滚动平均?

问有没有办法在Big Query中做滚动平均?
EN

Stack Overflow用户
提问于 2014-03-07 07:05:07
回答 2查看 9.3K关注 0票数 11

我知道在Big Query中有一个AVG函数,还有一些窗口函数可以将上一行或下一行的值向上或向下移动,但是有没有什么函数可以让你在指定的时间间隔内求平均值?例如,我想这样做,如下所示:

代码语言:javascript
复制
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字段,然后平均所有这些字段的结果,我就可以做这样的事情,但这是很大的开销。

EN

回答 2

Stack Overflow用户

发布于 2016-02-18 10:19:08

我认为Window Functions的OVER with RANGE构造最适合这里

假设日期字段表示为'YYYY-MM-DD‘格式,下面的查询执行滚动平均值

代码语言:javascript
复制
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
)

您很可能很久以前就已经找到了这个解决方案,但仍然希望(到今天为止)这里有一个我认为更好的答案。

票数 21
EN

Stack Overflow用户

发布于 2014-03-08 07:29:25

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

代码语言:javascript
复制
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问题)

对于窗口函数,让一个实现更大范围的窗口函数可能很有用,但现在我将自动生成查询。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/22238101

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档