質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.12%

mysql5.7 にて、前日差分のランキングの求め方について

受付中

回答 1

投稿

  • 評価
  • クリップ 1
  • VIEW 213

HiroUchi

score 5

実現したいこと

いわゆる音楽番組のヒットチャートみたく、前回からランクアップ・ダウンといったランキングを
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ページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

0

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/04/30 15:07

    ご提示いただきまして、ありがとうございます。
    全体で23万レコードほどあり、ある1日のランキングで1950人いるためか、ご提示のリンクでのやり方を試したところ、時間がかかりすぎまして、タイムアウトしてしまいました。

    キャンセル

  • 2019/05/01 10:30

    実行計画を確認して、インデックスが不足していないか確認してみて下さい。
    それで駄目なら、条件になっている部分が演算であることが理由であると考えられます。
    一時テーブルを利用してみて下さい。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.12%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる