Calculating a Moving Average MySQL?

Question

Good Day,

I am using the following code to calculate the 9 Day Moving average.

SELECT SUM(close)
FROM tbl
WHERE date <= '2002-07-05'
AND name_id = 2
ORDER BY date DESC
LIMIT 9

But it does not work because it first calculates all of the returned fields before the limit is called. In other words it will calculate all the closes before or equal to that date, and not just the last 9.

So I need to calculate the SUM from the returned select, rather than calculate it straight.

IE. Select the SUM from the SELECT...

Now how would I go about doing this and is it very costly or is there a better way?

Answer 1

If you want the moving average for each date, then try this:

SELECT date, SUM(close),
       (select avg(close) from tbl t2 where t2.name_id = t.name_id and datediff(t2.date, t.date) <= 9
       ) as mvgAvg
FROM tbl t
WHERE date <= '2002-07-05' and
      name_id = 2
GROUP BY date
ORDER BY date DESC

It uses a correlated subquery to calculate the average of 9 values.

Answer 2

Starting from MySQL 8, you should use window functions for this. Using the window RANGE clause, you can create a logical window over an interval, which is very powerful. Something like this:

SELECT
  date,
  close,
  AVG (close) OVER (ORDER BY date DESC RANGE INTERVAL 9 DAY PRECEDING)
FROM tbl
WHERE date <= DATE '2002-07-05'
AND name_id = 2
ORDER BY date DESC

For example:

WITH t (date, `close`) AS (
  SELECT DATE '2020-01-01', 50 UNION ALL
  SELECT DATE '2020-01-03', 54 UNION ALL
  SELECT DATE '2020-01-05', 51 UNION ALL
  SELECT DATE '2020-01-12', 49 UNION ALL
  SELECT DATE '2020-01-13', 59 UNION ALL
  SELECT DATE '2020-01-15', 30 UNION ALL
  SELECT DATE '2020-01-17', 35 UNION ALL
  SELECT DATE '2020-01-18', 39 UNION ALL
  SELECT DATE '2020-01-19', 47 UNION ALL
  SELECT DATE '2020-01-26', 50
)
SELECT
  date,
  `close`,
  COUNT(*) OVER w AS c,
  SUM(`close`) OVER w AS s,
  AVG(`close`) OVER w AS a
FROM t
WINDOW w AS (ORDER BY date DESC RANGE INTERVAL 9 DAY PRECEDING)
ORDER BY date DESC

Leading to:

date      |close|c|s  |a      |
----------|-----|-|---|-------|
2020-01-26|   50|1| 50|50.0000|
2020-01-19|   47|2| 97|48.5000|
2020-01-18|   39|3|136|45.3333|
2020-01-17|   35|4|171|42.7500|
2020-01-15|   30|4|151|37.7500|
2020-01-13|   59|5|210|42.0000|
2020-01-12|   49|6|259|43.1667|
2020-01-05|   51|3|159|53.0000|
2020-01-03|   54|3|154|51.3333|
2020-01-01|   50|3|155|51.6667|