如何在MySQL中计算多个移动平均值

Question

使用下面的表格，如何得到5期移动平均线、10期移动平均线和5期指数移动平均线的列。

+--------+------------+
| price  | data_date  |
+--------+------------+
| 122.29 | 2009-10-08 |
| 122.78 | 2009-10-07 |
| 121.35 | 2009-10-06 |
| 119.75 | 2009-10-05 |
| 119.02 | 2009-10-02 |
| 117.90 | 2009-10-01 |
| 119.61 | 2009-09-30 |
| 118.81 | 2009-09-29 |
| 119.33 | 2009-09-28 |
| 121.08 | 2009-09-25 |
+--------+------------+

- Merlin

Answer 1

在你的例子中，5行移动平均值是不起作用的。LIMIT操作符适用于返回集，而不是考虑进行聚合的行，因此更改它对聚合值没有影响。

- Gruff

Answer 2

SELECT AVG(a.price) FROM (SELECT price FROM t1 WHERE data_date <= ? ORDER BY data_date DESC LIMIT 5) AS a;

用实际需要的 MA 日期替换 ?。

- ssn

Answer 3

SELECT   t1.data_date, 
( SELECT  SUM(t2.price) / COUNT(t2.price) as MA5 FROM mytable AS t2    WHERE DATEDIFF(t1.data_date, t2.data_date) BETWEEN 0 AND 6 ) 
FROM mytable AS t1   ORDER BY t1.data_date;

将10日移动平均线的6改为13。

- Richard Liang