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.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
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 ...
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 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))
source to share