MySQL calculate moving average of N rows

Question

I'm trying to calculate the moving average of N rows, for all rows in a single query. In the example case, I am attempting to calculate the moving average of 50 rows.

SELECT 
    h1.date, 
    h1.security_id, 
    (   SELECT 
            AVG(last50.close)
        FROM (
            SELECT h.close
            FROM history as h
            WHERE h.date <= h1.date AND h.security_id = h1.security_id
            ORDER BY h.date DESC
            LIMIT 50
        ) as last50
    ) as avg50
FROM history as h1

However, MySQL gives me an error when running this query:

Unknown column 'h1.date' in 'where clause'

I'm trying this method because the other solutions listed don't really seem to work for my use case. There are solutions for a moving average of N days, but since all dates are not accounted for in my data set, I need the average of N rows.

This solution, shown below, doesn't work because AVG (also SUM and COUNT) doesn't account for LIMIT:

SELECT
  t1.data_date
  ( SELECT SUM(t2.price) / COUNT(t2.price)
    FROM t as t2
    WHERE t2.data_date <= t1.data_date
    ORDER BY t2.data_date DESC
    LIMIT 5
  ) AS 'five_row_moving_average_price'
FROM t AS t1
ORDER BY t1.data_date;

This question looks promising, but is somewhat indecipherable to me.

Any suggestions? Here's an SQLFiddle to play around in.

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Jan 16 '16 at 22:40

amdixon · Accepted Answer · 2016-01-19T10:50:06.897

plan

self join history on last 50 days

take average grouping by date and security id ( of current )

query

select curr.date, curr.security_id, avg(prev.close)
from history curr
inner join history prev 
on prev.`date` between date_sub(curr.`date`, interval 49 day) and curr.`date`
and curr.security_id = prev.security_id
group by 1, 2
order by 2, 1
;

output

+---------------------------+-------------+--------------------+
|           date            | security_id |  avg(prev.close)   |
+---------------------------+-------------+--------------------+
| January, 04 2016 00:00:00 | 1           | 10.770000457763672 |
| January, 05 2016 00:00:00 | 1           | 10.800000190734863 |
| January, 06 2016 00:00:00 | 1           | 10.673333485921225 |
| January, 07 2016 00:00:00 | 1           | 10.59250020980835  |
| January, 08 2016 00:00:00 | 1           | 10.432000160217285 |
| January, 11 2016 00:00:00 | 1           | 10.40166680018107  |
| January, 12 2016 00:00:00 | 1           | 10.344285828726631 |
| January, 13 2016 00:00:00 | 1           | 10.297500133514404 |
| January, 14 2016 00:00:00 | 1           | 10.2877779006958   |
| January, 04 2016 00:00:00 | 2           | 56.15999984741211  |
| January, 05 2016 00:00:00 | 2           | 56.18499946594238  |
| ..                        | ..          | ..                 |
+---------------------------+-------------+--------------------+

sqlfiddle

reference

sql rolling averages

modified to use last 50 rows

select
rnk_curr.`date`, rnk_curr.security_id, avg(rnk_prev50.close)
from
(
select `date`, security_id,
@row_num := if(@lag = security_id, @row_num + 1,
               if(@lag := security_id, 1, 1)) as row_num
from history 
cross join ( select @row_num := 1, @lag := null ) params
order by security_id, `date`
) rnk_curr
inner join
(
select date, security_id, close,
@row_num := if(@lag = security_id, @row_num + 1,
               if(@lag := security_id, 1, 1)) as row_num
from history 
cross join ( select @row_num := 1, @lag := null ) params
order by security_id, `date`
) rnk_prev50
on  rnk_curr.security_id = rnk_prev50.security_id
and rnk_prev50.row_num between rnk_curr.row_num - 49 and rnk_curr.row_num
group by 1,2
order by 2,1
;

sqlfiddle

note

the if function is to force the correct order of evaluation of variables.

Good, but note that the last 50 days may not correspond to the last 50 rows — Strawberry, Jan 17 '16 at 10:04
This seems to work (although it should be `row_num - 49` not `50`). This is definitely harder than it should be — Tom Grant, Jan 18 '16 at 13:27
in MySQL it certainly is harder than it should be (window functions make this far easier sadly missing in MySQL so far) — Paul Maxwell, Jan 19 '16 at 10:45
adjusted to 49. true without window functions, have to play with variables. — amdixon, Jan 19 '16 at 10:49

score 0 · Answer 2 · answered Dec 10 '21 at 22:16

In mysql 8 window function frame can be used to obtain the averages.

SELECT date, security_id, AVG(close) OVER (PARTITION BY security_id ORDER BY date ROWS 49 PRECEDING) as ma
FROM history
ORDER BY date DESC

This calculates the average of the current row and 49 preceding rows.

MySQL calculate moving average of N rows

2 Answers2

Linked