MySQL Forums
Forum List  »  Newbie

Re: Moving average
Posted by: Rick James
Date: November 02, 2013 04:15PM

Would you be happy with an "exponential moving average"? That can be easily done using user variables.

SELECT val, @avg := @avg + (val - @avg) / 12 AS ExpMovingAvg
FROM tbl
ORDER BY ...

The "12" can be adjusted to make 'recent' values more or less important in the avg...

SELECT `YEARS`, `MONTHS`,
    F1,  FORMAT(@F1 := @F1 + (F1 - @F1) / 12, 1) AS F1_EMA,
    F2,  FORMAT(@F2 := @F2 + (F2 - @F2) / 12, 1) AS F2_EMA
FROM `page1-1`
JOIN ( SELECT @F1 := F1, @F2 := F2, @F3 := F3
        FROM `page1-1`
        ORDER BY `YEARS`, `MONTHS` LIMIT 1 ) init
ORDER BY `YEARS`, `MONTHS`
LIMIT 48,6;

# Here are the last 5:
+-------+--------+------------+----------+------------+----------+
| YEARS | MONTHS | F1         | F1_EMA   | F2         | F2_EMA   |
+-------+--------+------------+----------+------------+----------+
|  2013 |      5 | 12363.7600 | 50,942.0 |  8034.6400 | 23,671.5 |
|  2013 |      6 | 15822.8800 | 48,015.4 |  8388.8800 | 22,397.9 |
|  2013 |      7 | 21269.0400 | 45,786.6 |  9082.4000 | 21,288.3 |
|  2013 |      8 | 18727.4400 | 43,531.6 | 10274.1600 | 20,370.4 |
|  2013 |      9 | 16470.5600 | 41,276.5 |  8159.7600 | 19,352.9 |
+-------+--------+------------+----------+------------+----------+

Or to get 12-mo moving averages, calculate the running sums and do a simple JOIN.
Note: This _requires_ that `ID` be consecutive, ordered, and dense. It would be safer to compute "month numbers" and use them rather than depending on an AUTO_INCREMENT.
CREATE TABLE foo
    SELECT ID,
            F1, @sum1 := @sum1 + F1 AS sum1,
            F2, @sum2 := @sum2 + F2 AS sum2
        FROM `page1-1`
        JOIN  ( SELECT @sum1 := 0, @sum2 := 0, @sum3 := 0
              ) init;

SELECT b.ID,
        (b.sum1 - a.sum1) / 12 AS MA1,
        (b.sum2 - a.sum2) / 12 AS MA2
    FROM foo a
    JOIN foo b ON a.ID = b.ID - 12;

# Here are the last 5:
| 49 |  50883.22416666662 | 22724.947266666666 |
| 50 | 44565.963908333295 |  20352.18703333333 |
| 51 | 36225.466608333285 |  18153.13705833332 |
| 52 | 29114.920391666627 | 15771.900316666637 |
| 53 |  23217.13373333328 | 13205.380516666628 |
+----+--------------------+--------------------+

Options: ReplyQuote


Subject
Written By
Posted
October 29, 2013 08:56AM
October 29, 2013 11:51AM
October 30, 2013 02:40AM
October 30, 2013 10:06AM
October 30, 2013 10:24AM
October 30, 2013 01:10PM
Re: Moving average
November 02, 2013 04:15PM
November 03, 2013 04:18AM
November 03, 2013 10:36PM
November 04, 2013 04:53AM
November 04, 2013 10:52PM
November 05, 2013 02:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.