mysql 字段移动平均值_MySQL计算N行的移动平均值

I'm trying to calculate the moving average of N rows, for all rows in a single query. In the example case, I am attempting to calculate the moving average of 50 rows.

SELECT

h1.date,

h1.security_id,

( SELECT

AVG(last50.close)

FROM (

SELECT h.close

FROM history as h

WHERE h.date <= h1.date AND h.security_id = h1.security_id

ORDER BY h.date DESC

LIMIT 50

) as last50

) as avg50

FROM history as h1

However, MySQL gives me an error when running this query:

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

I'm trying this method because the other solutions listed don't really seem to work for my use case. There are solutions for a moving average of N days, but since all dates are not accounted for in my data set, I need the average of N rows.

This solution, shown below, doesn't work because AVG (also SUM and COUNT) doesn't account for LIMIT:

SELECT

t1.data_date

( SELECT SUM(t2.price) / COUNT(t2.price)

FROM t as t2

WHERE t2.data_date <= t1.data_date

ORDER BY t2.data_date DESC

LIMIT 5

) AS 'five_row_moving_average_price'

FROM t AS t1

ORDER BY t1.data_date;

This question looks promising, but is somewhat indecipherable to me.

Any suggestions? Here's an SQLFiddle to play around in.

解决方案

plan

self join history on last 50 days

take average grouping by date and security id ( of current )

query

select curr.date, curr.security_id, avg(prev.close)

from history curr

inner join history prev

on prev.`date` between date_sub(curr.`date`, interval 49 day) and curr.`date`

and curr.security_id = prev.security_id

group by 1, 2

order by 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 |

| .. | .. | .. |

+---------------------------+-------------+--------------------+

reference

modified to use last 50 rows

select

rnk_curr.`date`, rnk_curr.security_id, avg(rnk_prev50.close)

from

(

select `date`, security_id,

@row_num := if(@lag = security_id, @row_num + 1,

if(@lag := security_id, 1, 1)) as row_num

from history

cross join ( select @row_num := 1, @lag := null ) params

order by 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)) as row_num

from history

cross join ( select @row_num := 1, @lag := null ) params

order by security_id, `date`

) rnk_prev50

on rnk_curr.security_id = rnk_prev50.security_id

and rnk_prev50.row_num between rnk_curr.row_num - 49 and rnk_curr.row_num

group by 1,2

order by 2,1

;

note

the if function is to force the correct order of evaluation of variables.