mysql - 当多行具有相同日期时，与前一行相比的百分比变化？

我有一个查询，该查询计算比率并返回给定日期每小时和服务器的比率：

SELECT a.day,
       a.hour,
       Sum(a.gemspurchased),
       Sum(b.gems),
       Sum(b.shadowgems),
       ( Sum(b.gems) / Sum(a.gemspurchased) )       AS GemRatio,
       ( Sum(b.shadowgems) / Sum(a.gemspurchased) ) AS ShadowGemRatio
FROM   (SELECT Date(Date_sub(createddate, INTERVAL 7 hour)) AS day,
               Hour(Date_sub(createddate, INTERVAL 7 hour)) AS hour,
               serverid,
               Sum(gems)                                    AS GemsPurchased
        FROM   dollartransactions
        WHERE  Date(Date_sub(createddate, INTERVAL 7 hour)) BETWEEN
               Curdate() - INTERVAL 14 day AND Curdate()
        GROUP  BY 1,
                  2,
                  3) a,
       /*Gems recorded from DollarTransactions Table after purchasing gem package*/
       (SELECT Date(Date_sub(createddate, INTERVAL 7 hour)) AS day,
               Hour(Date_sub(createddate, INTERVAL 7 hour)) AS hour,
               serverid,
               Sum(acceptedamount)                          AS Gems,
               Sum(acceptedshadowamount)                    AS ShadowGems
        FROM   gemtransactions
        WHERE  Date(Date_sub(createddate, INTERVAL 7 hour)) BETWEEN
                       Curdate() - INTERVAL 14 day AND Curdate()
               AND transactiontype IN ( 990, 2 )
               AND fullfilled = 1
               AND gemtransactionid >= 130000000
        GROUP  BY 1,
                  2,
                  3) b
/*Gems & Shadow Gems spent, recorded from GemTransactions Table */
WHERE  a.day = b.day
       AND a.serverid = b.serverid
GROUP  BY 1,
          2

此代码返回比率的组成部分以及比率本身（有时为空）：

day       hour  sum(a.GemsPurchased)    sum(b.Gems) sum(b.ShadowGems)   GemRatio ShadowGemRatio
9/5/2014    0   472875                   465499      60766              0.9844    0.1285
9/5/2014    1   350960                   371092      45408              1.0574    0.1294
9/5/2014    2   472985                   509618      58329              1.0775    0.1233
9/5/2014    3   1023905                  629310      71017              0.6146    0.0694
9/5/2014    4   1273170                  628697      74896              0.4938    0.0588
9/5/2014    5   998920                   637709      64145              0.6384    0.0642
9/5/2014    6   876470                   651451      68977              0.7433    0.0787
9/5/2014    7   669100                   667217      81599              0.9972    0.122

我想做的是创建一个第8列和第9列，计算GemRatio和ShadowGemRatio与前一行的百分比变化。我在这里看到了其他线程关于如何对特定查询执行此操作，但我无法让它对我的特定MySQL查询执行。。。

最佳答案

确定首先为该查询创建一个视图。我们称之为v1：
创建视图v1，选择您的查询；
现在是查询比率的时候了。我以为一天有24小时。第一个行比率更改将为零。

select now.*,
CASE
  WHEN yesterday.gemRatio is null THEN 0
  ELSE 100*(now.gemRatio-yesterday.gemRatio)/yesterday.gemRatio
END as gemChange,
CASE
  WHEN yesterday.ShadowGemRatio is null THEN 0
  ELSE 100*(now.ShadowGemRatio-yesterday.ShadowGemRatio)/yesterday.ShadowGemRatio
END as shadowGemChange
from v1 now left outer join v1 yesterday on
((now.day = yesterday.day && now.hour = yesterday.hour+1) ||
(DATEDIFF(now.day,yesterday.day) = 1  && now.hour = 0 && yesterday.hour=23))