Calculating and updating table with simple moving average of closing stock prices in MYSQL

Question

I could use some help (preferably a dummy's guide) to updating the following table: ``` CREATE TABLE `SYMBOL` ( `day` date NOT NULL, `open` decimal(8,3) DEFAULT NULL, `high` decimal(8,3) DEFAULT NULL, `low` decimal(8,3) DEFAULT NULL, `close` decimal(8,3) DEFAULT NULL, `volume` bigint(20) DEFAULT NULL, `adj_close` decimal(8,3) DEFAULT NULL, `moving_average` decimal(8,3) DEFAULT NULL, PRIMARY KEY (`day`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ``` The moving_average column is empty now. All other columns are populated (for the time being, I'm ok with this being "static", it does not need to update as I add rows - though if this is easy to do, that would be great). It is a 20-day moving average that I hope to calculate. I have tried by performing the steps here to the best of my ability: How do I calculate a moving average using MySQL? My query is this: ``` SELECT `close`, ( SELECT AVG(`close`) AS moving_average FROM SYMBOL T2 WHERE ( SELECT COUNT(*) FROM SYMBOL T3 WHERE `day` BETWEEN T2…

Accepted Answer

There are two ways of doing this:

- Create an `update` query that updates every row in your table

- Create a stored procedure that does the job

I personally prefer option 2:

```
delimiter $$
create procedure movingAvg()
begin
    declare mv double;
    declare t date;
    declare done int default false;
    declare cur_t cursor for
         select distinct day from symbol
         order by day;
    declare cur_mv cursor for
         select avg(close) from symbol
         where day between date_add(t, interval -19 day) and t;
         -- Here you define the interval of your MV.
         -- If you want a 20-day MV, then the interval is between t-19 and t
    declare continue handler for not found set done=true;

open cur_t;
    loop_day: loop
        fetch cur_t into t;
        if not done then
            open cur_mv;
            fetch cur_mv into mv;
            close cur_mv;
            update SYMBOL
                set moving_average = mv
                where day=t;
        else
            leave loop_day;
        end if;
    end loop loop_day;
    close cur_t;
end;
delimiter ;
```

Calculating and updating table with simple moving average of closing stock prices in MYSQL

Solution

Problem

Related problems