在mysql中运行平均

Question

我有下面的表格

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

起初看起来很简单，但是当表上的数据膨胀时，它太慢了

有更快的方法吗？

Answer 1

您的查询是进行移动平均的一种方法：

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)应进一步提高性能。