CODEWITHSUNDEEP
mysql
Merlin
Merlin

Reputation: 25639

How to calculated multiple moving average in MySQL

Using table below, How would get a column for 5 period moving average, 10 period moving average, 5 period exponential moving average. 

+--------+------------+
| 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 |
+--------+------------+

Upvotes: 3

Views: 6665

Answers (3)

Richard Liang
Richard Liang

Reputation: 1

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;

Change 6 to 13 for 10-day MA

Upvotes: 0

ssn
ssn

Reputation: 439

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

Replace ? with the date whose MA you need.

Upvotes: 0

Gruff
Gruff

Reputation: 669

The 5-row moving average in your example won't work. The LIMIT operator applies to the return set, not the rows being considered for the aggregates, so changing it makes no difference to the aggregate values.

Upvotes: 2

Related Questions