mysql计算均线,如何使用MySQL计算移动平均线？

I need to do something like:

SELECT value_column1

FROM table1

WHERE datetime_column1 >= '2009-01-01 00:00:00'

ORDER BY datetime_column1;

Except in addition to value_column1, I also need to retrieve a moving average of the previous 20 values of value_column1.

Standard SQL is preferred, but I will use MySQL extensions if necessary.

解决方案

This is just off the top of my head, and I'm on the way out the door, so it's untested. I also can't imagine that it would perform very well on any kind of large data set. I did confirm that it at least runs without an error though. :)

SELECT

value_column1,

(

SELECT

AVG(value_column1) AS moving_average

FROM

Table1 T2

WHERE

(

SELECT

COUNT(*)

FROM

Table1 T3

WHERE

date_column1 BETWEEN T2.date_column1 AND T1.date_column1

) BETWEEN 1 AND 20

)

FROM

Table1 T1