mysql5.7 にて、前日差分のランキングの求め方について
受付中
回答 1
投稿
- 評価
- クリップ 1
- VIEW 762
実現したいこと
いわゆる音楽番組のヒットチャートみたく、前回からランクアップ・ダウンといったランキングを
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 |
... | ... | ... | ... |
試したこと
以下の問い合わせ文で、
前日との経験値の差分と、経験値の差分の多い順に(以下、「急上昇」という。)並び替えて、
取得を行いましたが、下記、急上昇のランキングが正しく表すことができておりません。
SET @td_rank=0, @td_diff=0,@yd_rank=0, @yd_diff=0;
SELECT
CASE
WHEN @td_diff = (A.ex_point - B.ex_point) THEN @td_rank
ELSE
@td_rank:=@td_rank+1
END AS today_ranking,
CASE
WHEN @yd_diff = (B.ex_point - C.ex_point) THEN @yd_rank
ELSE
@yd_rank:=@yd_rank+1
END AS yd_ranking,
V.name,
A.ex_point AS td_ex_point,
B.ex_point AS yd_ex_point,
(A.ex_point - B.ex_point) AS td_diff,
(B.ex_point - C.ex_point) AS yd _diff
FROM
characters_datadetail AS A
INNER JOIN
characters_datadetail AS B
ON
A.character_id = B.character_id AND A.searchAt = DATE(B.searchAt + INTERVAL 1 DAY)
LEFT OUTER JOIN
characters_datadetail AS C
ON
B.character_id = C.character_id AND B.searchAt = DATE(C.searchAt + INTERVAL 1 DAY)
INNER JOIN
characters_data AS V
ON
A.character_id = V.id
WHERE A.search_at = '2019-04-21' AND V.published_fg = 1
ORDER 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
-
気になる質問をクリップする
クリップした質問は、後からいつでもマイページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
クリップを取り消します
-
良い質問の評価を上げる
以下のような質問は評価を上げましょう
- 質問内容が明確
- 自分も答えを知りたい
- 質問者以外のユーザにも役立つ
評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。
質問の評価を上げたことを取り消します
-
評価を下げられる数の上限に達しました
評価を下げることができません
- 1日5回まで評価を下げられます
- 1日に1ユーザに対して2回まで評価を下げられます
質問の評価を下げる
teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。
- プログラミングに関係のない質問
- やってほしいことだけを記載した丸投げの質問
- 問題・課題が含まれていない質問
- 意図的に内容が抹消された質問
- 過去に投稿した質問と同じ内容の質問
- 広告と受け取られるような投稿
評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します
この機能は開放されていません
評価を下げる条件を満たしてません
質問の評価を下げる機能の利用条件
この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
-
メールアドレスの認証
メールアドレスの認証
-
質問評価に関するヘルプページの閲覧
質問評価に関するヘルプページの閲覧
0
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
15分調べてもわからないことは、teratailで質問しよう!
- ただいまの回答率 88.36%
- 質問をまとめることで、思考を整理して素早く解決
- テンプレート機能で、簡単に質問をまとめられる
2019/04/30 15:07
全体で23万レコードほどあり、ある1日のランキングで1950人いるためか、ご提示のリンクでのやり方を試したところ、時間がかかりすぎまして、タイムアウトしてしまいました。
2019/05/01 10:30
それで駄目なら、条件になっている部分が演算であることが理由であると考えられます。
一時テーブルを利用してみて下さい。