mysqlを用いてログからランキングを作成

###やりたいこと
ユーザーID、問題番号、取り組み時間、取り組み時刻、得点が記録されているログを取り出し、TOP5までのランキングを作成する。
ランキングの種類としては、①取り組み回数の多さランキング、②取り組み時間の長さランキング、③総得点の高さランキング(同じ問題が複数回取り組まれた場合は、最高点を採用)、④問題ごとのいち早く解いた人ランキングを考えている。
ユーザーがランキングを表示する際に計算させることで、常に最新の情報を出力させたい。

###データベースの状況
ユーザーID、問題番号、取り組み時間、取り組み時刻、得点というカラムがあるテーブルに、問題が終わるごとにそれらの情報をINSERTすることで蓄積していく。

###現在考えている方針
ユーザーidを格納した行列$idsをforeachで反復し、さらに問題番号を格納した行列$contentsを反復する中でランキングを作る。

MySQL
1$ids=array()//ユーザーIDの格納した行列
2$contents=array()//問題番号を格納した行列
3
4$con=mysqli_connect(host, username, password, database);
5foreach($ids as $i){
6 $data=mysqli_query($con,"select contents,t,score from log where user =$i");
7 $farray=mysqli_fetch_array($data,$mode=MYSQLI_ASSOC);
8 foreach($contents as $j){
9
10  }
11}

###利用環境
MySQL 5.0.95
PHP 7.1.x
phpMyAdmin 2.11.11.3
レンタルサーバー xfree

###最後に

ここまでは作ってみたのですが、そのあとどのような処理をさせればよいか、悩んでいます。
皆様のお力をぜひお貸しください。よろしくお願いいたします。

###加筆

hoshi-takanori様

phpMyAdminを用いてデータベース作成し、CREATE TABLE 文は手元の残っておりません。

ただし、phpMyAdminでは画像のような状態になっています。
stは回答開始時刻、enは回答終了時刻、tは回答時間です。
サンプルとしては、上から順に
1000000,1000,210102030400,210102030430,30,3
というものが考えられます。
210102030400は2021年01月02日03時04分00秒を表します。

また、いち早く解いた人については、10問あれば50人表示する予定でいます。

###サンプルデータ
1000000,1000,210102030400,210102030430,30,1
1000000,1000,210102030900,210102090430,30,2
1000000,1001,210102031100,210102091140,40,3
1000000,1002,210102031000,210102091040,40,2
1000000,1003,210102032000,210102092040,40,3
1000000,1004,210102032050,210102092130,40,3
1000001,1001,210103030400,210102030420,20,2
1000003,1001,210103030400,210102030420,20,2
1000005,1001,210103030400,210102030420,20,2
1000005,1002,210103030500,210102030520,20,2
1000006,1011,210104110223,210104110252,29,2
1000007,1000,210102112555,210102112613,18,1
1000007,1001,210104110224,210104110256,32,0
1000008,1000,210103031200,210102031234,34,1
1000008,1003,210103031400,210102031433,33,2
1000008,1003,210103031500,210102031544,44,2
1000008,1003,210103031633,210102031700,27,3
1000010,1001,210104030400,210104030420,20,2
1000011,1005,210104110200,210104110252,52,2
1000015,1001,210103030400,210102030420,10,0
1000015,1001,210103030600,210102030620,20,2
1000015,1001,210103030700,210102030720,20,3
1000016,1005,210103030900,210102030920,20,3
1000016,1005,210103030900,210102030920,20,3
1000017,1002,210103030900,210102030931,31,0
1000017,1003,210103031100,210102031231,31,1
1000020,1010,210102031000,210102031030,30,3
1000021,1010,210102030430,210102030600,90,0
1000021,1010,210102030730,210102030742,42,1
1000021,1010,210103030730,210103030745,45,2
1000022,1011,210102030401,210102030432,32,3
1000022,1000,210102030400,210102030430,30,2
1000022,1000,210102030500,210102030520,20,3
1000022,1003,210104030500,210104030520,20,3
1000022,1000,210105030500,210105030520,20,3

hoshi-takanori

2021/09/22 00:26

テーブル定義は SQL の CREATE TABLE 文が欲しいです。(できればサンプルデータも。) ランキングは SQL で計算すると良いと思います。また、④　問題ごとのいち早く解いた人ランキングは、問題が　10 問あれば 50 人分すべて表示しますか？

M.I.

2021/09/22 00:59

追記・修正の依頼をありがとうございます。質問を編集いたしましたので、ご覧いただければ幸いです。

yambejp

2021/09/22 00:59

＞MySQL 5.0 バージョンは間違いないですか？せめて5.6とか5.7じゃないと使えない機能が多いですが・・・ (現バージョン8.0系ですし)

hoshi-takanori

2021/09/22 02:16 編集

例えば ① は、こんな感じでしょうか。(同じ人が同じ問題を複数回数えたら重複して数えますが。) select user, count(*) as count from log group by user order by count desc limit 5; あと、ランキングを求めるので、テストデータは数十件は欲しいですね。(自分で作ろうかとも思ったけど、面倒なのでやめました。)

M.I.

2021/09/22 04:14

>yambejp様確認しましたが、5.0で間違いないようです。レンタルサーバー(xfree)上で動いているもので、更新は難しいかもしれません。

M.I.

2021/09/22 05:01 編集

＞hoshi-takanori様 ①につきまして、例をお示しいただきありがとうございます。重複して数える予定なので、その方法を試してみたいと思います。テストデータは30件ほどですが、追記させていただきました。

hoshi-takanori

2021/09/22 05:22

こんな感じで。(④　は問題ごとに SQL を実行する必要があるかと。) https://www.db-fiddle.com/f/eq3Ugcv9FFGhoCChSAcGL5/0

M.I.

2021/09/25 10:53

>hoshi-takanori様リンク先でコードを実行し、所望の結果が得られることを確認いたしました。ありがとうございます。もし、よろしければ、貴方のお答えをベストアンサーにしたいのですが、回答としてお答えいただけますでしょうか。

行動規範の内容に同意します

回答1件

ベストアンサー

ランキングは SQL で計算すると良いと思います。

テーブル定義及びサンプルデータ

sql
1create table log (
2  user int(7),
3  contents int(4),
4  st varchar(20),
5  en varchar(20),
6  t float,
7  score int(4)
8);
9
10insert into log values
11(1000000,1000,210102030400,210102030430,30,1),
12(1000000,1000,210102030900,210102090430,30,2),
13(1000000,1001,210102031100,210102091140,40,3),
14(1000000,1002,210102031000,210102091040,40,2),
15(1000000,1003,210102032000,210102092040,40,3),
16(1000000,1004,210102032050,210102092130,40,3),
17(1000001,1001,210103030400,210102030420,20,2),
18(1000003,1001,210103030400,210102030420,20,2),
19(1000005,1001,210103030400,210102030420,20,2),
20(1000005,1002,210103030500,210102030520,20,2),
21(1000006,1011,210104110223,210104110252,29,2),
22(1000007,1000,210102112555,210102112613,18,1),
23(1000007,1001,210104110224,210104110256,32,0),
24(1000008,1000,210103031200,210102031234,34,1),
25(1000008,1003,210103031400,210102031433,33,2),
26(1000008,1003,210103031500,210102031544,44,2),
27(1000008,1003,210103031633,210102031700,27,3),
28(1000010,1001,210104030400,210104030420,20,2),
29(1000011,1005,210104110200,210104110252,52,2),
30(1000015,1001,210103030400,210102030420,10,0),
31(1000015,1001,210103030600,210102030620,20,2),
32(1000015,1001,210103030700,210102030720,20,3),
33(1000016,1005,210103030900,210102030920,20,3),
34(1000016,1005,210103030900,210102030920,20,3),
35(1000017,1002,210103030900,210102030931,31,0),
36(1000017,1003,210103031100,210102031231,31,1),
37(1000020,1010,210102031000,210102031030,30,3),
38(1000021,1010,210102030430,210102030600,90,0),
39(1000021,1010,210102030730,210102030742,42,1),
40(1000021,1010,210103030730,210103030745,45,2),
41(1000022,1011,210102030401,210102030432,32,3),
42(1000022,1000,210102030400,210102030430,30,2),
43(1000022,1000,210102030500,210102030520,20,3),
44(1000022,1003,210104030500,210104030520,20,3),
45(1000022,1000,210105030500,210105030520,20,3);

①取り組み回数の多さランキング

sql
1select user, count(*) as cnt from log group by user order by cnt desc limit 5;
2
3+---------+-----+
4| user    | cnt |
5+---------+-----+
6| 1000000 |   6 |
7| 1000022 |   5 |
8| 1000008 |   4 |
9| 1000021 |   3 |
10| 1000015 |   3 |
11+---------+-----+

②取り組み時間の長さランキング

sql
1select user, sum(t) as sum_t from log group by user order by sum_t desc limit 5;
2
3+---------+-------+
4| user    | sum_t |
5+---------+-------+
6| 1000000 |   220 |
7| 1000021 |   177 |
8| 1000008 |   138 |
9| 1000022 |   122 |
10| 1000017 |    62 |
11+---------+-------+

③総得点の高さランキング(同じ問題が複数回取り組まれた場合は、最高点を採用)

sql
1select user, sum(max_score) total
2from (select user, contents, max(score) max_score from log group by user, contents) t
3group by user order by total desc limit 5;
4
5+---------+-------+
6| user    | total |
7+---------+-------+
8| 1000000 |    13 |
9| 1000022 |     9 |
10| 1000005 |     4 |
11| 1000008 |     4 |
12| 1000015 |     3 |
13+---------+-------+

④問題ごとのいち早く解いた人ランキング

sql
1select user, min(en) min_en from log where contents = 1000 group by user order by min_en limit 5;
2
3+---------+--------------+
4| user    | min_en       |
5+---------+--------------+
6| 1000000 | 210102030430 |
7| 1000022 | 210102030430 |
8| 1000008 | 210102031234 |
9| 1000007 | 210102112613 |
10+---------+--------------+
11
12select user, min(en) min_en from log where contents = 1001 group by user order by min_en limit 5;
13
14+---------+--------------+
15| user    | min_en       |
16+---------+--------------+
17| 1000001 | 210102030420 |
18| 1000003 | 210102030420 |
19| 1000005 | 210102030420 |
20| 1000015 | 210102030420 |
21| 1000000 | 210102091140 |
22+---------+--------------+