Add column moving average in mysql

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

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;