実現したいこと
いわゆる音楽番組のヒットチャートみたく、前回からランクアップ・ダウンといったランキングを
DBから取得したいと思っております。
具体的に申しますと、
DBに対して、ゲームでいうところのユーザごとの経験値を
毎日データベースに登録を行っており、経験値の増加が多い順のランキング(以下、「急上昇ランキング」という。)と、
前々日の急上昇ランキングを取得したく思っております。
mysqlの8系は分析関数である、RANK()、DENSE_RANK()があったため、
求めることができましたが、mysql5.7は対応しておらず、
それらを使わずに取得する方法に苦戦をしております。
■キャラクタテーブル
id | name | published_fg |
---|---|---|
1 | リサーナ | 1 |
2 | ユーリ | 1 |
3 | シエル | 1 |
4 | コーガ | 1 |
... | ... | ... |
※published_fgが1が「公開」
■キャラクタ詳細テーブル
id | character_id | ex_point | search_at |
---|---|---|---|
1 | 1 | 2539109 | 2019-04-21 |
2 | 2 | 1343196 | 2019-04-21 |
3 | 3 | 170488 | 2019-04-21 |
4 | 4 | 33297 | 2019-04-21 |
5 | 1 | 2537066 | 2019-04-20 |
6 | 2 | 1341719 | 2019-04-20 |
7 | 3 | 169369 | 2019-04-20 |
8 | 4 | 32321 | 2019-04-20 |
... | ... | ... | ... |
試したこと
以下の問い合わせ文で、
前日との経験値の差分と、経験値の差分の多い順に(以下、「急上昇」という。)並び替えて、
取得を行いましたが、下記、急上昇のランキングが正しく表すことができておりません。
mysql
1SET @td_rank=0, @td_diff=0,@yd_rank=0, @yd_diff=0; 2SELECT 3 CASE 4 WHEN @td_diff = (A.ex_point - B.ex_point) THEN @td_rank 5 ELSE 6 @td_rank:=@td_rank+1 7 END AS today_ranking, 8 9 CASE 10 WHEN @yd_diff = (B.ex_point - C.ex_point) THEN @yd_rank 11 ELSE 12 @yd_rank:=@yd_rank+1 13 END AS yd_ranking, 14 V.name, 15 A.ex_point AS td_ex_point, 16 B.ex_point AS yd_ex_point, 17 (A.ex_point - B.ex_point) AS td_diff, 18 (B.ex_point - C.ex_point) AS yd _diff 19FROM 20 characters_datadetail AS A 21INNER JOIN 22 characters_datadetail AS B 23ON 24 A.character_id = B.character_id AND A.searchAt = DATE(B.searchAt + INTERVAL 1 DAY) 25LEFT OUTER JOIN 26 characters_datadetail AS C 27ON 28 B.character_id = C.character_id AND B.searchAt = DATE(C.searchAt + INTERVAL 1 DAY) 29INNER JOIN 30 characters_data AS V 31ON 32 A.character_id = V.id 33WHERE A.search_at = '2019-04-21' AND V.published_fg = 1 34ORDER BY diff DESC, A.ex_point DESC;
※td_ranking:今日のランキング、yd_ranking:昨日のランキング
td_ex_point:今日の経験値、yd_ex_point:昨日の経験値
td_diff:今日-昨日の経験値差分、yd_diff:昨日-一昨日の経験値差分
---取得された結果(急上昇ランキング)---
|search_at|td_ranking|yd_ranking|name|td_ex_point|yd_ex_point|td_diff|yd_diff|
|:--|:--:|--:|--:|--:|--:|--:|
|2019-04-21|1107|902|リサーナ|2539109|2537066|2043|2156|
|2019-04-21|329|265|ユーリ|1343196|1341719|1477|1387|
|2019-04-21|879|714|シエル|170488|169369|1119|956|
|2019-04-21|720|586|コーガ|33297|32321|976|131|
上から順にtd_rankingが1位、2位となるのが、求めたい結果となります。
恐れ入りますが、どのようなSQL文によれば、望む形で取得ができるかについて、
ご教示頂けますと幸いです。
もし可能ではございましたら、ユーザ定義変数を使わない方法がありましたら、
その点ご教示頂けますと幸いでございます。
なお、並び替え順は第1がdiff(前日差分)、第2にex_point(今日の経験値)
回答いただくに当たりまして、その他必要な情報等ございましたら、
ご教示いただきましたら、追加させていただきます。
以上、よろしくお願いいたします。
参考にしたサイト
https://zuumie.de/?p=468
https://blog.phalusamil.com/entry/2015/09/23/094536
バージョン
mysql 5.7
ubuntu 18.04
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2019/04/30 06:07
2019/05/01 01:30