MySQL self-join optimization while calculating move averages

Question

I have created a mysql query to calculate moving averages of data by using multiple self-joins as shown below. This is consuming lot of time and the data rows are in 100k per query. Any way to further optimize it to reduce time please?

select a.rownum,a.ma_small_price, b.ma_medium_price from
    (SELECT t3.rownum, AVG(t.last_price) as 'ma_small_price' 
FROM temp_data t3
    left JOIN temp_data t ON t.rownum BETWEEN ifnull(t3.rownum,0) - @psmall AND t3.rownum
GROUP BY t3.rownum) 
inner join
    (SELECT t3.rownum, AVG(t.last_price) as 'ma_medium_price' 
FROM temp_data t3
    left JOIN temp_data t ON t.rownum BETWEEN ifnull(t3.rownum,0) - @pmedium AND t3.rownum
GROUP BY t3.rownum) b on a.rownum = b.rownum

Answer 1

OVER ( ... ) is disappointingly slow -- in both MySQL 8.0 and MariaDB 10.x.

I like "exponential moving average" as being easier to compute than "moving average". The following is roughly equivalent to what Nick proposed. This runs faster, but has slightly different results:

SELECT  rownum,
        @small := @small + 0.5 * (last_price - @small) AS mae_small_price,
        @med   := @med   + 0.2 * (last_price - @med)   AS mae_med_price
    FROM ( SELECT @small := 10, @med := 10 ) AS init
    JOIN temp_data
    ORDER BY rownum;

The coefficient controls how fast the exponential moving average adapts to changes in the data. It should be greater than 0 and less than 1.

The "10" that I initialized the EPA to was a rough guess of the average -- it biases the first few values but is gradually swamped as more values are folded in.