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

Essentially, you can just join the table to itself to find the given % change. Then, order by change descending to get the largest changers on top. You could even order by abs(change) if you want the largest swings.

select
   p_today.ticker,
   p_today.date,
   p_yest.price as open,
   p_today.price as close,
   --Don't have to worry about 0 division here
   (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(date_add(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

Answer 2

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

Answer 3

Scott brings up a great point about consecutive market days. I recommend handling this with a connector table like:

CREATE TABLE `market_days` ( 
  `market_day` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY USING BTREE (`market_day`), 
  UNIQUE KEY USING BTREE (`date`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 
;

As more market days elapse, just INSERT new date values in the table. market_day will increment accordingly.

When inserting prices data, lookup the LAST_INSERT_ID() or corresponding value to a given date for past values.

As for the prices table itself, you can make storage, SELECT and INSERT operations much more efficient with a useful PRIMARY KEY and no AUTO_INCREMENT column. In the schema below, your PRIMARY KEY contains intrinsically useful information and isn't just a convention to identify unique rows. Using MEDIUMINT (3 bytes) instead of INT (4 bytes) saves an extra byte per row and more importantly 2 bytes per row in the PRIMARY KEY - all while still affording over 16 million possible dates and ticker symbols (each).

CREATE TABLE `prices` ( 
  `market_day` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
  `ticker_id` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0', 
  `price` decimal (7,2) NOT NULL DEFAULT '00000.00', 
  PRIMARY KEY USING BTREE (`market_day`,`ticker_id`), 
  KEY `ticker_id` USING BTREE (`ticker_id`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1
;

In this schema each row is unique across each pair of market_day and ticker_id. Here ticker_id corresponds to a list of ticker symbols in a tickers table with a similar schema to the market_days table:

CREATE TABLE `tickers` ( 
  `ticker_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticker_symbol` VARCHAR(5),
  `company_name` VARCHAR(50), 
  /* etc */
  PRIMARY KEY USING BTREE (`ticker_id`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 
;

This yields a similar query to others proposed, but with two important differences: 1) There's no functional transformation on the date column, which destroys MySQL's ability to use keys on the join; in the query below MySQL will use part of the PRIMARY KEY to join on market_day. 2) MySQL can only use one key per JOIN or WHERE clause. In this query MySQL will use the full width of the PRIMARY KEY (market_day and ticker_id) whereas in the previous query it could only use one (MySQL will usually pick the more selective of the two).

SELECT 
  `market_days`.`date`, 
  `tickers`.`ticker_symbol`, 
  `yesterday`.`price` AS `close_yesterday`, 
  `today`.`price` AS `close_today`, 
  (`today`.`price` - `yesterday`.`price`) / (`yesterday`.`price`)  AS `pct_change`
FROM 
  `prices` AS `today`
LEFT JOIN 
  `prices` AS `yesterday` 
  ON /* uses PRIMARY KEY */
    `yesterday`.`market_day` = `today`.`market_day` - 1 /* this will join NULL for `today`.`market_day` = 0 */
    AND
    `yesterday`.`ticker_id` = `today`.`ticker_id` 
INNER JOIN 
  `market_days` /* uses first 3 bytes of PRIMARY KEY */
  ON 
  `market_days`.`market_day` = `today`.`market_day`
INNER JOIN 
  `tickers` /* uses KEY (`ticker_id`) */
  ON 
  `tickers`.`ticker_id` = `today`.`ticker_id`
WHERE 
  `today`.`price` > 0 
  AND 
  `yesterday`.`price` > 0 
;

A finer point is the need to also join against tickers and market_days in order to display the actual ticker_symbol and date, but these operations are very fast since they make use of keys.