MySQLでのランキングを作成するSQL

質問

ランキングを作成する、効率の良いSQLを教えて下さい。

MySQLで、以下の状態です。

テーブル名：
hoge

カラム：
id,kbn,name,score,flg,date

id…自動
kbn…1～4
name…string
score…int
flg…bool
date…年月日

(1)dateが1週間以内のデータのみ
(2)flgがtrueのデータのみ
(3)kbn毎に、5レコード取得
(4)順位は、点数が重複している場合は同一の順位をつけSQLで生成しn位のフォーマット
(5)6レコード以降に同一スコアのデータがある場合は日付が古い順
(6)scoreは、n点のフォーマット
(7)該当データが5レコード存在しなかった場合は、順位だけをセットし他をnull

欲しい結果は、以下の状態です。
kbn,順位,name,score
1 ,1位 ,ホゲ,100点
1 ,2位 ,あい, 85点
1 ,2位 ,かき, 85点
1 ,4位 ,くけ, 70点
1 ,5位 ,さし, 65点
2 ,1位 ,すせ, 99点
2 ,1位 ,たち, 99点
2 ,3位 ,つて, 80点
　　・
　　・
　　・
4 ,4位 ,らり, 10点
null ,5位 ,null, null

以上、よろしくお願い致します。

回答No.1

yambejp · ベストアンサー

//元データ
create table hoge(id int not null primary key,kbn int,name varchar(10),score int,flg tinyint,d date);
insert into hoge values(1,1,'a',100,1,'2014-03-10'),(2,1,'b',90,1,'2014-03-11'),(3,1,'c',80,1,'2014-03-12'),(4,1,'d',90,1,'2014-03-13'),(5,1,'e',80,1,'2014-03-10'),(6,1,'f',80,1,'2014-03-11'),(7,2,'g',0,1,'2014-03-12'),(8,2,'h',100,0,'2014-03-13'),(9,2,'i',100,1,'2014-03-10'),(10,2,'j',100,1,'2014-03-11'),(11,2,'k',100,1,'2014-03-12'),(12,2,'l',100,1,'2014-03-13'),(13,3,'m',100,1,'2014-03-10'),(14,3,'n',100,1,'2014-03-11');

とりあえず検索条件がかぶるので一度
(1)(2)をつかってビューを作ります。

//view作成
create view v_hoge as
select * from hoge
where d between curdate() - interval 1 week and curdate() + interval 1 week
and flg = 1;

//結果
select *
,(select count(*)+1 from v_hoge as v2 where v1.kbn=v2.kbn and v1.score<v2.score) as rank1
from v_hoge as v1
where kbn in (select kbn from v_hoge group by kbn having count(*)>=5)
having (select count(*)+1 from v_hoge as v2 where v1.kbn=v2.kbn and (v1.score<v2.score or v1.score=v2.score and v1.d>v2.d) ) <=5
order by kbn asc,rank1 asc;

(1)は「何の」一週間以内なのか不明。たぶん今日の？
また一週間とは前も先もあるので命題が中途半端

(4)と(6)のフォーマットはsql側でやらずに出力側のプログラムでやるべき
（キャストや結合が無駄なため）

(5)はkbnごとに日付がユニークでないと順位が決定できない

(7)は意味不明
５個データがないのに5位ってどういうこと？しかもkbnまでnullじゃ何を示しているか？

お礼

yambejp 様

ご回答ありがとうございます。
説明が不足していたり悪かったりでしたが
ほぼ、欲しかった状態でございます。

(5)は、仕様を検討してみます。
(7)は確かに意味不明でした。

サンプルデータで、そのまま確認もでき
本当に助かりました。

誠にありがとうございました。