ランキングを作成する、効率の良い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ベストアンサー
- 回答日時:
//元データ
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個データがないのに5位ってどういうこと?しかもkbnまでnullじゃ何を示しているか?
yambejp 様
ご回答ありがとうございます。
説明が不足していたり悪かったりでしたが
ほぼ、欲しかった状態でございます。
(5)は、仕様を検討してみます。
(7)は確かに意味不明でした。
サンプルデータで、そのまま確認もでき
本当に助かりました。
誠にありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) RANK.EQとCOUNTIFSの組み合わせで同ポイントの場合、違う条件を加えて順位を付けたい。 1 2022/08/30 19:49
- 統計学 テストの順位についてです 今回のテストの順位を知りたいのですが、できますでしょうか?今回は僕の学年は 4 2022/11/30 19:15
- その他(プログラミング・Web制作) python コードについて(初学者です) 3 2023/07/20 14:44
- 統計学 投票のジレンマ。 3 2023/05/13 22:16
- 父親・母親 学年2位ってあんま頭良くないんですか? 6 2023/07/03 22:09
- Excel(エクセル) Excelで全クラスのランキング表を作成したい 4 2022/05/24 15:28
- 数学 以下の問題が分かりません。 8ビット浮動小数点数が、最上位ビットから順に符号1ビット、指数部3ビット 4 2023/07/22 16:06
- C言語・C++・C# C言語 プログラミング 4 2022/05/22 11:53
- C言語・C++・C# C言語プログラム変更 2 2022/12/21 15:03
- 高校受験 中学や高校で、成績上位者の名前を廊下に張り出していましたか? 5 2022/09/25 23:21
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
SQLサーバから、項目の属性(型...
-
SQL Left Join で重複を排除す...
-
副問合せの書き方について
-
[MySQL] UNIQUE制約の値を更新...
-
select文のwhere句に配列を入れ...
-
SQLにて特定の文字を除いた検索...
-
selectした大量データをinsert...
-
エクセルの関数について教えて...
-
sqlで、600行あるテーブルを100...
-
MySQLのint型で001と表示する方...
-
VIEWの元のテーブルのindexって...
-
複数テーブルのGROUP BY の使い...
-
Unionした最後にGROUP BYを追加...
-
Access パラメータクエリをcsv...
-
LAST_INSERT_IDで同時にアクセ...
-
PL/SQLの変数について
-
[SQLServer] テーブル名からカ...
-
VMwareがCDドライブを認識する...
-
1対多結合で多を絞り込み条件と...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
SQL Left Join で重複を排除す...
-
副問合せの書き方について
-
SQLサーバから、項目の属性(型...
-
VIEWの元のテーブルのindexって...
-
select文のwhere句に配列を入れ...
-
selectした大量データをinsert...
-
センノシド異性体構造式
-
Unionした最後にGROUP BYを追加...
-
insertを高速化させたい
-
SQLにて特定の文字を除いた検索...
-
マイクラPC版のコマンドで効率...
-
ある条件の最大値+1を初番する...
-
inner joinをすると数がおかし...
-
sqlで、600行あるテーブルを100...
-
エクセルの関数について教えて...
-
Access パラメータクエリをcsv...
-
URL と行番号の指定
-
複数テーブルのGROUP BY の使い...
-
PL/SQLの変数について
おすすめ情報