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

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

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Ubuntu

Ubuntuは、Debian GNU/Linuxを基盤としたフリーのオペレーティングシステムです。

Q&A

1回答

1136閲覧

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

HiroUchi

総合スコア7

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Ubuntu

Ubuntuは、Debian GNU/Linuxを基盤としたフリーのオペレーティングシステムです。

0グッド

1クリップ

投稿2019/04/30 02:55

実現したいこと

いわゆる音楽番組のヒットチャートみたく、前回からランクアップ・ダウンといったランキングを
DBから取得したいと思っております。

具体的に申しますと、
DBに対して、ゲームでいうところのユーザごとの経験値を
毎日データベースに登録を行っており、経験値の増加が多い順のランキング(以下、「急上昇ランキング」という。)と、
前々日の急上昇ランキングを取得したく思っております。

mysqlの8系は分析関数である、RANK()、DENSE_RANK()があったため、
求めることができましたが、mysql5.7は対応しておらず、
それらを使わずに取得する方法に苦戦をしております。

■キャラクタテーブル

idnamepublished_fg
1リサーナ1
2ユーリ1
3シエル1
4コーガ1
.........

※published_fgが1が「公開」

■キャラクタ詳細テーブル

idcharacter_idex_pointsearch_at
1125391092019-04-21
2213431962019-04-21
331704882019-04-21
44332972019-04-21
5125370662019-04-20
6213417192019-04-20
731693692019-04-20
84323212019-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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

HiroUchi

2019/04/30 06:07

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

2019/05/01 01:30

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問