# Percentage change from the previous line when multiple lines have the same date?

I have a query that calculates the relationship and returns it for every hour and server on a specific day:

SELECT a.day,
a.hour,
Sum(a.gemspurchased),
Sum(b.gems),
( 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,
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

This code returns the constituent parts of the relationship, as well as the relationship itself (which is sometimes null):

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

What I would like to do is create 8th and 9th columns that calculate the% change from the previous row for both GemRatio and ShadowGemRatio. I've seen other threads here on how to do this for specific queries, but I couldn't get it to work for my specific MySQL query ...

+3

source to share

Ok first create a view for this request. Let's call it v1:

CREATE VIEW v1 AS SELECT YOUR QUESTION HERE ;

Now here's a request to have a relationship. I assumed the day has 24 hours. Changing the relationship of the first row will be zero.

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
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))

0

source

All Articles