みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

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

投稿日時 - 2015-04-02 13:05:33

QNo.8947701

困ってます

質問者が選んだベストアンサー

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

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

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 ;

ランキング2と3は、ランキング1の全科目データがそろってないと取り出せないので、
まずは、ランキングに使う3カラムの値を連結して、並べ替えを単純化する。
desc と asc が混じっているので、一つだけ逆順になってる score を 最高点-scrore としておき、数値の先頭に '0'文字を加えて、文字列の桁数をそろえるには、CONCAT と RIGHT 関数を使う。
以下とりあえずの設定 scrore は 100点満点、time は4桁以内、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 ;

ランキング2:自分(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 ;

ランキング3: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 ;

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

投稿日時 - 2015-04-04 22:37:56

お礼

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

非常に助かりました。

投稿日時 - 2015-04-08 16:10:04

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(1)

あなたにオススメの質問