mysql对比时间段百分比_如何使用MySQL计算最高的每日价格变化百分比？

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.

解决方案

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