CODEWITHSUNDEEP
mysqldatetimeaverage
JuanPablo
JuanPablo

Reputation: 24754

mysql avg in neighborhood dates

I have a table with 

datetime (every 15 mins) | value

how I can show the avg value of 

select  avg( value_15mins_before, value, value_15mins_after )

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I would suggest that you put an autoincrementing id in the table. When using timestamps, things can be off by a fraction of a second, which can cause problems.

The following will give you a moving average around each point:

select (value + coalesce(tprev.value, 0.0) + coalesce(tnext.value, 0.0)) /
       ((case when value is not null then 1.0 else 0.0 end) +
        (case when tprev.value is not null then 1.0 else 0.0 end) +
        (case when tnext.value is not null then 1.0 else 0.0 end)
       ) as moving_avg
from (select t.*,
             (select max(t2.dt) from t t2 where t2.dt < t.dt) as dt_prev,
             (select min(t2.dt) from t t2 where t2.dt > t.dt) as dt_next
      from t
     ) t left outer join
     t tprev
     on t.dt_prev = tprev.dt_prev left outer join
     t tnext
     on t.dt_next = tnext.dt_next

It assumes that there are no "holes" in the data. You can fix this by putting in constraints in the subquerys for dt_prev and dt_next . . . for instance, only considering time values that are within 20 minutes of the given value.

This also assumes that value is not null.

Upvotes: 1

Related Questions