MySQL calculate moving average of N rows -

i'm trying calculate moving average of n rows, for rows in single query. in example case, attempting calculate moving average of 50 rows.

select      h1.date,      h1.security_id,      (   select              avg(last50.close)         (             select h.close             history h             h.date <= h1.date , h.security_id = h1.security_id             order h.date desc             limit 50         ) last50     ) avg50 history h1 

however, mysql gives me error when running query:

unknown column 'h1.date' in 'where clause'

i'm trying method because other solutions listed don't seem work use case. there solutions moving average of n days, since dates not accounted in data set, need average of n rows.

this solution, shown below, doesn't work because avg (also sum , count) doesn't account limit:

select   t1.data_date   ( select sum(t2.price) / count(t2.price)     t t2     t2.data_date <= t1.data_date     order t2.data_date desc     limit 5   ) 'five_row_moving_average_price' t t1 order t1.data_date; 

this question looks promising, indecipherable me.

any suggestions? here's sqlfiddle play around in.

plan

• self join history on last 50 days
• take average grouping date , security id ( of current )

query

select curr.date, curr.security_id, avg(prev.close) history curr inner join history prev  on prev.`date` between date_sub(curr.`date`, interval 49 day) , curr.`date` , curr.security_id = prev.security_id group 1, 2 order 2, 1 ; 

output

+---------------------------+-------------+--------------------+ |           date            | security_id |  avg(prev.close)   | +---------------------------+-------------+--------------------+ | january, 04 2016 00:00:00 | 1           | 10.770000457763672 | | january, 05 2016 00:00:00 | 1           | 10.800000190734863 | | january, 06 2016 00:00:00 | 1           | 10.673333485921225 | | january, 07 2016 00:00:00 | 1           | 10.59250020980835  | | january, 08 2016 00:00:00 | 1           | 10.432000160217285 | | january, 11 2016 00:00:00 | 1           | 10.40166680018107  | | january, 12 2016 00:00:00 | 1           | 10.344285828726631 | | january, 13 2016 00:00:00 | 1           | 10.297500133514404 | | january, 14 2016 00:00:00 | 1           | 10.2877779006958   | | january, 04 2016 00:00:00 | 2           | 56.15999984741211  | | january, 05 2016 00:00:00 | 2           | 56.18499946594238  | | ..                        | ..          | ..                 | +---------------------------+-------------+--------------------+ 

sqlfiddle

reference

• sql rolling averages

---

modified use last 50 rows

select rnk_curr.`date`, rnk_curr.security_id, avg(rnk_prev50.close) ( select `date`, security_id, @row_num := if(@lag = security_id, @row_num + 1,                if(@lag := security_id, 1, 1)) row_num history  cross join ( select @row_num := 1, @lag := null ) params order security_id, `date` ) rnk_curr inner join ( select date, security_id, close, @row_num := if(@lag = security_id, @row_num + 1,                if(@lag := security_id, 1, 1)) row_num history  cross join ( select @row_num := 1, @lag := null ) params order security_id, `date` ) rnk_prev50 on  rnk_curr.security_id = rnk_prev50.security_id , rnk_prev50.row_num between rnk_curr.row_num - 49 , rnk_curr.row_num group 1,2 order 2,1 ; 

sqlfiddle

note

the if function force correct order of evaluation of variables.