3

I've got a table with stock information and am looking to calculate a few data points for percentage change for each stock. The formula for calculating this is pretty simple

(Today's Close Price - Yesterday's Close Price) / Yesterday's Close Price * 100

So a stock that closed today at $12 and closed yesterday at $10 would be

($12 - $10) / $10 *100 = 2/10*100 = +20%

Calculating the daily percent change is easy, but I need to also calculate the weekly, monthly, 6 month, yearly etc percent change.

So One complicating factor in figuring out the previous close price is 7 days ago may have been a holiday, or weekend, or the stock did not trade that day, so I have to select the most recent trading day prior to 1 week ago.

Option 1 - use a cursor.

I can select a list of stocks, then loop through them and for each stock, get the most recent trading day from a week ago and get the closing price for that day. Do the calculations and update my table. Move to the next stock. This seems doable but slower. If possible, I'd like to try option 2.

Option 2 - do it in a query with a subquery

A single query 

UPDATE todaysdata a, historicaldata b 
   SET a.1weekpercentchange = 
          ((a.closetrade - .closetrade) / b.closetrade) * 100) 
 WHERE a.stockid = b.stockid
   AND b.tradedate = (
       SELECT max(tradedate) 
         FROM historicaldatatable 
        WHERE tradedate <= date_sub(now(), interval 7 day) 
        WHERE stockid=a.stockid);

The todaysData table holds about 15000 records. One for each stock. The historical data holds about a half million records with multiple records for each stock. For example, Apple would have 200 rows per year * x years of data in the historical table.

I'm finding option 2 to be really slow in operation. Is there a faster way of doing this query?

Steffen Roller
  • 3,464
  • 25
  • 43
user3916107
  • 31
  • 1
  • have you tried to tune you my.cnf? http://stackoverflow.com/questions/10905226/mysql-my-cnf-performance-tuning-recommendations – Michael D. Aug 06 '14 at 23:34
  • Solution 2 uses a corellated subquery. An uncorellated one will be orders of magnitude faster. (Although it actually looks like you have a syntax error there so I'm surprised that it works) – Strawberry Apr 09 '16 at 04:17

0 Answers0