Databases
StackOverflow2013
Postsdbo
POCalculating and updating table with simple moving average of closing stock prices in MYSQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCalculating and updating table with simple moving average of closing stock prices in MYSQL
    text
    copied!<p>I could use some help (preferably a dummy's guide) to updating the following table:</p> <pre><code>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; </code></pre> <p>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.</p> <p>I have tried by performing the steps here to the best of my ability:</p> <p><a href="https://stackoverflow.com/questions/878473/how-do-i-calculate-a-moving-average-using-mysql">How do I calculate a moving average using MySQL?</a></p> <p>My query is this:</p> <pre><code>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 </code></pre> <p>Have I modified the query correctly? What needs to be done to write the results to the moving_average column?</p> <p>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.</p> <p>I also looked at this answer: <a href="https://stackoverflow.com/questions/7059693/how-to-calculated-multiple-moving-average-in-mysql">How to calculated multiple moving average in MySQL</a></p> <p>However, I'm unsure what I need to change to the reply for my table.</p> <p>Any help is appreciated.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload