如何计算复杂分组依据后上一行/每天的增加百分比？ [英] How to calculate percentage increase from previous row/day after complex Group By?

问题描述

我有一张IntradayPrices1Minute的表格，我在那里存储 1分钟时间段开盘，高点，低点和收盘价：

I have a table IntradayPrices1Minute where I store 1 minute timeframe open, high, low and close prices for stocks:

  CREATE TABLE `IntradayPrices1Minute` (
  `ticker` varchar(10) NOT NULL DEFAULT '',
  `datetime` datetime NOT NULL,
  `volume` mediumint(11) unsigned NOT NULL,
  `open` decimal(8,4) unsigned NOT NULL,
  `high` decimal(8,4) unsigned NOT NULL,
  `low` decimal(8,4) unsigned NOT NULL,
  `close` decimal(8,4) unsigned NOT NULL,
  PRIMARY KEY (`datetime`,`ticker`),
  UNIQUE KEY `indxTickerDatetime` (`ticker`,`datetime`) USING BTREE
)

我已经成功地构建了一个查询，我可以计算这些股票的每日开盘价，最高价，最低价和收盘价。这是查询：

I have successfully build a query where I can calculate the daily open, high, low and close prices for those stocks. This is the query:

SELECT 
    ticker,
    DATE(datetime) AS 'Date',
    SUBSTRING_INDEX( GROUP_CONCAT(CAST(open AS CHAR) ORDER BY datetime), ',', 1 ) as 'Daily Open',
    max(GREATEST(open, high, low, close)) AS 'Daily High',
    min(LEAST(open, high, low, close)) AS 'Daily Low',
    SUBSTRING_INDEX( GROUP_CONCAT(CAST(close AS CHAR) ORDER BY datetime DESC), ',', 1 ) as 'Daily Close'

FROM 
    IntradayPrices1Minute

GROUP BY
    ticker, date(datetime)

，这是此查询成功返回的结果的一部分：

and this is part of the results that this query successfully returns:

ticker  Date        Open    High    Low     Close
----    ----------  ------  ------  ------  ------ 
AAAE    2012-11-26  0.0100  0.0100  0.0100  0.0100
AAAE    2012-11-27  0.0130  0.0140  0.0083  0.0140
AAAE    2012-11-28  0.0140  0.0175  0.0140  0.0165
AAAE    2012-11-29  0.0175  0.0175  0.0137  0.0137
AAMRQ   2012-11-26  0.4411  0.5300  0.4411  0.5290
AAMRQ   2012-11-27  0.5100  0.5110  0.4610  0.4950
AAMRQ   2012-11-28  0.4820  0.4900  0.4300  0.4640
AAMRQ   2012-11-29  0.4505  0.4590  0.4411  0.4590
AAMRQ   2012-11-30  0.4500  0.4570  0.4455  0.4568

$ b $现在的问题是：我想返回查询中的第七列，计算每天的收盘价和前一日收盘价之间的百分比增加/减少。

Now the problem is: I want to return a seventh column in the query that calculates for each day the percentage increase/decrease between its close price and the previous day close price.

我在StackOverflow上看到过类似的问题，但在每日价格已经在表格中的情况下。我认为这是特别复杂的，因为在几次分组计算后，查询时间可以获得每日价格。

I have seen similar questions asked on StackOverflow but for situations in which the daily prices are already in a table. I think it is specially complex in my case because the daily prices are obtained at query time after several grouping calculations.

任何帮助都将不胜感激。
非常感谢。
Boga

Any help on this would be greatly appreciated. Many Thanks. Boga

推荐答案

请看这个参考资料： SQLFIDDLE

因此，您使用OHLC的当前表用于派生px_change ，px_pct :)
结果是四舍五入以显示更改中的4个小数点以及百分比。
空值用于显示没有价格变动/ pct，因为空值表示没有价格变化的
比零价格变化更好;）

So your current table with OHLC is used to derive the px_change, px_pct :) Results have been rounded to show 4 decimal points in change as well as percentage. Null is used to show no price change/pct as null is a better representation of no price change than having a zero price change ;)

查询：

select ticker, date_format(date,'%m-%d-%Y') as date, open, high,low,close,
pxchange,concat(round(pxpct*100,4),'%') pxpct
from (select case when ticker <> @pxticker 
        then @pxclose := null end, p.*, (close-@pxclose) as pxchange,
    (close-@pxclose)/@pxclose as pxpct, (@pxclose := close),
    (@pxticker := ticker) from pricing p
cross join
    (select @pxclose := null, @pxticker := ticker
     from pricing 
     order by ticker, date limit 1)  as a
  order by ticker, date ) as b
order by ticker, date asc

取消：

TICKER  DATE        OPEN    HIGH    LOW     CLOSE   PXCHANGE  PXPCT
AAAE    11-26-2012  0.01    0.01    0.01    0.01    (null)    (null)
AAAE    11-27-2012  0.013   0.014   0.0083  0.014   0.004     40.0000%
AAAE    11-28-2012  0.014   0.0175  0.014   0.0165  0.0025    17.8571%
AAAE    11-29-2012  0.0175  0.0175  0.0137  0.0137  -0.0028   -16.9697%
AAMRQ   11-26-2012  0.4411  0.53    0.4411  0.529   (null)    (null)
AAMRQ   11-27-2012  0.51    0.511   0.461   0.495   -0.034    -6.4272%
AAMRQ   11-28-2012  0.482   0.49    0.43    0.464   -0.031    -6.2626%
AAMRQ   11-29-2012  0.4505  0.459   0.4411  0.459   -0.005    -1.0776%
AAMRQ   11-30-2012  0.45    0.457   0.4455  0.4568  -0.0022   -0.4793%

**根据OP的要求显示包含在评论中的图片**： ）

** UPDATED WITH A PICTURE TO SHOW THE PARENTHESIS AS PER OP'S REQUEST IN COMMENTS ** :)

这篇关于如何计算复杂分组依据后上一行/每天的增加百分比？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！