我有如下表
id timestamp speed
1 11:00:01 100
2 11:05:01 110
3 11:10:01 90
4 11:15 :01 80
我需要计算移动平均线,如下所示
id timestamp speed average
1 11:00:01 100 100
2 11:05:01 110 105
3 11:10:01 90 100
4 11:15:01 80 95
我尝试过的
SELECT
*,
(select avg(speed) from tbl t where tbl.timestamp<=t.timestamp) as avg
FROM
tbl
乍一看很简单,但是当表上的数据膨胀时,就太慢了
还有更快的方法吗?
您的查询是执行运行平均值的一种方法:
SELECT t.*,
(select avg(speed) from tbl tt where tt.timestamp <= t.timestamp) as avg
FROM tbl t;
另一种方法是使用变量:
select t.*, (sum_speed / cnt) as running_avg_speed
from (select t.*, (@rn := @rn + 1) as cnt, (@s := @s + speed) as sum_speed
from tbl t cross join
(select @rn := 0, @s := 0) params
order by timestamp
) t;
上的索引tbl(timestamp)
应进一步提高性能。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)