How can I calculate the top % daily price changes using MySQL?

Question

I have a table called prices which includes the closing price of stocks that I am tracking daily.

Here is the schema:

CREATE TABLE `prices` (
  `id` int(21) NOT NULL auto_increment,
  `ticker` varchar(21) NOT NULL,
  `price` decimal(7,2) NOT NULL,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `ticker` (`ticker`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2200 ;

I am trying to calculate the % price drop for anything that has a price value greater than 0 for today and yesterday. Over time, this table will be huge and I am worried about performance. I assume this will have to be done on the MySQL side rather than PHP because LIMIT will be needed here.

How do I take the last 2 dates and do the % drop calculation in MySQL though?

Any advice would be greatly appreciated.

Answer 1

One problem I see right off the bat is using a timestamp data type for the date, this will complicate your sql query for two reasons - you will have to use a range or convert to an actual date in your where clause, but, more importantly, since you state that you are interested in today's closing price and yesterday's closing price, you will have to keep track of the days when the market is open - so Monday's query is different than tue - fri, and any day the market is closed for a holiday will have to be accounted for as well.

I would add a column like mktDay and increment it each day the market is open for business. Another approach might be to include a 'previousClose' column which makes your calculation trivial. I realize this violates normal form, but it saves an expensive self join in your query.

If you cannot change the structure, then you will do a self join to get yesterday's close and you can calculate the % change and order by that % change if you wish.

Below is Eric's code, cleaned up a bit it executed on my server running mysql 5.0.27

select
   p_today.`ticker`,
   p_today.`date`,
   p_yest.price as `open`,
   p_today.price as `close`,
   ((p_today.price - p_yest.price)/p_yest.price) as `change`
from
   prices p_today
   inner join prices p_yest on
       p_today.ticker = p_yest.ticker
       and date(p_today.`date`) = date(p_yest.`date`) + INTERVAL 1 DAY
       and p_today.price > 0
       and p_yest.price > 0
       and date(p_today.`date`) = CURRENT_DATE
order by `change` desc
limit 10

Note the back-ticks as some of your column names and Eric's aliases were reserved words.

Also note that using a where clause for the first table would be a less expensive query - the where get's executed first and only has to attempt to self join on the rows that are greater than zero and have today's date

select
   p_today.`ticker`,
   p_today.`date`,
   p_yest.price as `open`,
   p_today.price as `close`,
   ((p_today.price - p_yest.price)/p_yest.price) as `change`
from
   prices p_today
   inner join prices p_yest on
       p_today.ticker = p_yest.ticker
       and date(p_today.`date`) = date(p_yest.`date`) + INTERVAL 1 DAY

       and p_yest.price > 0
where p_today.price > 0
    and date(p_today.`date`) = CURRENT_DATE
order by `change` desc
limit 10