Add column moving average in mysql

Question

I am currently adding crypto market data to my mysql database. I have the following data per minute :

Open time 
Close time
Highest price
Lowest price 
Number of trades

I would like to add the simple moving average of different time periods to the mysql database. I tried to do this calculation in python but this took to much time. Therefore, I calculated the SMA of a 50 day time period with the following query.

mycursor.execute(
    """select 
           open_date,
           avg(close) over(rows between 49 preceding and current row) 
       from {}""".format(use_table)
)

However, besides making the query I would like to directly update the outcome for the SMA in the database. Preferably, I would like to have a query that is able to update all the SMA's that are not yet calculated. Since I updated my database once a day.

Since I am new with using SQL any advise about how to update a column with the SMA would be very helpful.

Answer 1

It is possible to turn your SELECT query into an UPDATE. The trick is to invoke the window function (AVG(...) OVER(...)) within a suquery. You can use a WHERE clause to update only the records that were not yet calculated.

UPDATE mytable trg
INNER JOIN (
    SELECT
        open_date,
        AVG(close) OVER(ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) sma
    FROM mytable
) src ON trg.id = src.id
SET trg.sma = src.sma
WHERE trg.sma IS NULL;