PHP,MySQLで３つのランキング

質問

前提：
seisekiテーブルに
id(Primary), student_id（生徒ID）, kamoku_id（科目ID）, score, time, created　のフィールドがあります。
ランキングの順位条件は、同じ科目でscoreがより高く、timeがより短く、createdがより古い方が高位となります。
同じ科目を何度でも受講できる為、student_idはユニークではありません。

前提条件から、以下３つのランキングを取得したいと考えています。
ランキング１：科目毎のランキング（同じstudent_idが１～10位独占等可）
ランキング２：自分（student_id)の全受講履歴と履歴毎のランキング
ランキング３：id指定時のランキング情報

各ランキングは、１SQLである必要はなく、PHPでの加工も考慮に入れております。
試行錯誤しているのですが、ORDER BY区にどうやって順位を付ければ良いのか分からず、
SELECT * FROM seiseki ORDER BY kamoku_id DESC, score DESC, time ASC, created ASC
ここから進みません・・・

何卒、よろしくお願いいたします。

回答No.1

mpro-gram · ベストアンサー

mysql はランク関数がないので、必要データを選択して並べ替えて、変数を使ってカウントアップまたは、自己結合で、ランク用のデータ見比べという作業が必要でしょう。

ランキング１：科目毎のランキング（同じstudent_idが１～10位独占等可）
１科目ごとなら whereで科目を選択指定して並べ替える。以下だと、全部同じ数値でも、見つけた順に並びますので、３つ同値なら同順とするには、自己結合が必要。プログラム側で取得値を前行値と見比べながら順位をつけていく方が楽かも。

select * , (select @a:= @a+1 from (select @a:=0) dummy) as rank from seiseki
where kamoku_id = 1
order by score desc , time asc , created asc ;

ランキング２と３は、ランキング１の全科目データがそろってないと取り出せないので、
まずは、ランキングに使う３カラムの値を連結して、並べ替えを単純化する。
desc と asc が混じっているので、一つだけ逆順になってる score を 最高点-scrore としておき、数値の先頭に '0'文字を加えて、文字列の桁数をそろえるには、CONCAT と RIGHT 関数を使う。
以下とりあえずの設定 scrore は 100点満点、time は４桁以内、create は、datetime型とする,createカラムが最初から datetime や date型ならcast不要だけど
-- 全科目いっぺんに 並べ替え順のチェック
select * , CONCAT(RIGHT(CONCAT('000' , 100-score ), 3 ),'_' , RIGHT(CONCAT('0000', time), 4),'_' ,CAST(created as datetime ) ) as rank_data
from seiseki
order by kamoku_id , rank_data ;

-- rank 付け 自己結合により、同値は上位の同順
select s1.kamoku_id, s1.id, count(s2.id) + 1 as rank
from
( select *, CONCAT(RIGHT(CONCAT('000' , 100-score ), 3 ),'_' , RIGHT(CONCAT('0000', time), 4),'_' ,CAST(created as datetime ) ) as rank_data
from seiseki
) as s1
left join
( select id,kamoku_id , CONCAT(RIGHT(CONCAT('000' , 100-score ), 3 ),'_' , RIGHT(CONCAT('0000', time), 4),'_' ,CAST(created as datetime ) ) as rank_data
from seiseki
) as s2
on s1.rank_data > s2.rank_data and s1.kamoku_id = s2.kamoku_id
group by s1.kamoku_id , s1.id
order by s1.kamoku_id , rank ;

ランキング２：自分（student_id)の全受講履歴と履歴毎のランキング
上記テーブルをサブクエリテーブルとして、取り出す. mysqlは、group by に指定して無くても、連動して一意に決まるとわかっているカラム値の取り出しが可能なので、s1.* で取り出しておく
select *
from ( select s1.*, count(s2.id) + 1 as rank
from
( select *, CONCAT(RIGHT(CONCAT('000' , 100-score ), 3 ),'_' , RIGHT(CONCAT('0000', time), 4),'_' ,CAST(created as datetime ) ) as rank_data
from seiseki
) as s1
left join
( select id,kamoku_id , CONCAT(RIGHT(CONCAT('000' , 100-score ), 3 ),'_' , RIGHT(CONCAT('0000', time), 4),'_' ,CAST(created as datetime ) ) as rank_data
from seiseki
) as s2
on s1.rank_data > s2.rank_data and s1.kamoku_id = s2.kamoku_id
group by s1.kamoku_id , s1.id
) as rank_tbl
where student_id = 1
order by created ;

ランキング３：id指定時のランキング情報　も　同様に
select *
from ( select s1.kamoku_id, s1.id, count(s2.id) + 1 as rank
from
( select *, CONCAT(RIGHT(CONCAT('000' , 100-score ), 3 ),'_' , RIGHT(CONCAT('0000', time), 4),'_' ,CAST(created as datetime ) ) as rank_data
from seiseki
) as s1
left join
( select id,kamoku_id , CONCAT(RIGHT(CONCAT('000' , 100-score ), 3 ),'_' , RIGHT(CONCAT('0000', time), 4),'_' ,CAST(created as datetime ) ) as rank_data
from seiseki
) as s2
on s1.rank_data > s2.rank_data and s1.kamoku_id = s2.kamoku_id
group by s1.kamoku_id , s1.id
) as rank_tbl
where id = 1 ;

件数が大量の場合の実行時間は、不明なので、もし文字連結で、実行時間が大幅にかかるようなら、最初に提示の科ごと選択した並べ替えデータから、プログラム側でさらなる選択をかけることになるかもしれない。

お礼

mpro-gramさん
回答ありがとうございます！
＞ランキングに使う３カラムの値を連結して、並べ替えを単純化する。
この発想がすごい。

非常に助かりました。