在mysql中运行平均 [英] running average in mysql

问题描述

我有下面的表格

id   timestamp  speed
1    11:00:01   100
2    11:05:01   110
3    11:10:01   90
4    11:15 :01  80

我需要像下面那样计算移动平均线

I need to calculate moving average like below

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

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

At first it looks quite easy but when the data on the table swell, it is too slow

有更快的方法吗?

推荐答案

您的查询是进行均线的一种方法:

Your query is one way to do a running average:

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)上的索引应进一步提高性能.

An index on tbl(timestamp) should further improve performance.

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