running average in mysql

Question

I have the table like below

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

What I tried

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

Any faster approach?

Answer 1

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;

The alternative is to use variables:

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;

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

Answer 2

Or slotting neatly between GL's two answers (performancewise anyway)...

SELECT x.*, AVG(y.speed) avg
  FROM my_table x
  JOIN my_table y
    ON y.id <= x.id
 GROUP 
    BY x.id;

Answer 3

Does MySQL support windowing functions?

select
  id, timestamp, speed,
  avg (speed) over (order by timestamp) as average
from tbl

If it doesn't this might work, although I doubt it's efficient:

select
  min (t1.id) as id, t1.timestamp, min (t1.speed) as speed,
  avg (t2.speed)
from
  tbl t1
  join tbl t2 on
    t2.id <= t1.id
group by
  t1.timestamp
order by
  t1.timestamp

Answer 4

What about a simple concurrent solution?

SET @summ=0; SET @counter=0;SELECT *,(@counter := @counter +1) as cnt, (@summ := @summ+speed) as spd, (@summ/@counter) AS avg FROM tbl;