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.day AND T1.day
) BETWEEN 1 AND 20
)
FROM
SYMBOL T1
Have I modified the query correctly? What needs to be done to write the results to the moving_average column?
When I run the above, nothing happens (it says its running, no errors, after letting it run for a long time I just stopped it). The column moving_average still has NULL values.
I also looked at this answer: How to calculated multiple moving average in MySQL
However, I'm unsure what I need to change to the reply for my table.
Any help is appreciated.
There are two ways of doing this:
update
query that updates every row in your tableI 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 ;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With