如何使用MySQL计算最高的每日价格变化百分比? [英] How can I calculate the top % daily price changes using MySQL?

问题描述

我有一个名为prices的表，其中包含我每天跟踪的股票的收盘价.

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

以下是架构:

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 ;

我试图计算今天和昨天价格大于0的任何商品的价格下跌百分比.随着时间的流逝，这张桌子会很大，我担心性能.我认为这必须在MySQL方面而不是PHP上完成，因为这里需要LIMIT.

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.

如何获取最近2个日期，但是如何在MySQL中进行％跌落计算呢?

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

任何建议将不胜感激.

推荐答案

我马上看到的一个问题是使用时间戳数据类型作为日期，这将使您的sql查询复杂化，原因有两个-您将不得不使用范围或转换为where子句中的实际日期，但更重要的是，由于您声明对今天的收盘价和昨天的收盘价感兴趣，因此您必须跟踪市场开盘的日期-因此星期一的查询与星期二-星期五不同，并且也必须考虑假期休市的任何一天.

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.

我将添加一个列，例如mktDay，并在每天开放市场时增加它.另一种方法可能是包括一个"previousClose"列，该列使您的计算变得微不足道.我意识到这违反了正常形式，但是在您的查询中节省了昂贵的自我联接.

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.

下面是Eric的代码，对它在运行mysql 5.0.27的服务器上执行的代码进行了清理

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

请注意，因为某些列名和Eric的别名是保留字.

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

还请注意，对第一个表使用where子句将是一个开销较小的查询-首先执行where get，并且只需要尝试对大于零且具有今天日期的行进行自我联接

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

这篇关于如何使用MySQL计算最高的每日价格变化百分比?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！