SQLでブービー賞

photo credit: Great Beyond via photopin cc

弊社が今の所に引っ越す前は近くにボーリング場があったため、納会の時とかにボーリング大会が恒例でした。 極めてレベルが低い大会でしたがみんな社長賞を狙って白熱しておりました。 （私はレベルを下げていた一人です）

さて、ここにボーリング大会の成績表があります。 大会情報テーブル

データID	大会名	なまえ	スコア
1	春大会	Aさん	120
2	春大会	Bさん	80
3	春大会	Cさん	100
4	春大会	Dさん	140
5	夏大会	Aさん	100
6	夏大会	Bさん	80
7	夏大会	Cさん	90
8	夏大会	Dさん	110
9	秋大会	Aさん	90
10	秋大会	Bさん	120
11	秋大会	Cさん	130
12	秋大会	Dさん	150
13	冬大会	Aさん	110
14	冬大会	Bさん	120
15	冬大会	Cさん	170
16	冬大会	Dさん	130

ここから各大会のブービー賞を知りたい。となったとき、どうすればいいのでしょうか？

最下位とか最上位ならMINとかMAXとかでGROUP BYすれば出来そうですが、下から2番目だけをとるには？ ORDER BYで並べて2番めだけ取る？いやそれだと各大会ごとにしないといけないから面倒くさいぞ。

そんなときはRANK関数を使います。早速使ってみましょう。 RANKの引数であるover内にスコア昇順で指定しましょう。

SELECT データID
,大会名
,なまえ
,スコア
,RANK() over (order by スコア asc) AS 順位
FROM 大会成績

データID	大会名	なまえ	スコア	順位
2	春大会	Bさん	80	1
6	夏大会	Bさん	80	1
7	夏大会	Cさん	90	3
9	秋大会	Aさん	90	3
3	春大会	Cさん	100	5
5	夏大会	Aさん	100	5
8	夏大会	Dさん	110	7
13	冬大会	Aさん	110	7
14	冬大会	Bさん	120	9
1	春大会	Aさん	120	9
10	秋大会	Bさん	120	9
11	秋大会	Cさん	130	12
16	冬大会	Dさん	130	12
4	春大会	Dさん	140	14
12	秋大会	Dさん	150	15
15	冬大会	Cさん	170	16

全大会通してですがスコア順に順位が付きましたね。 ちなみに同一スコアの場合は同じ順位となり、この表みたいに1位ふたりで次が3位となります。 だから実際は同一スコアの場合は若い人を優遇するとか決めておいたほうがいいでしょう。

このデータは年齢は無いので仮にデータIDも順位判定要素としてみます。

SELECT データID
,大会名
,なまえ
,スコア
,RANK() over (order by スコア,データID asc) AS 順位
FROM 大会成績

データID	大会名	なまえ	スコア	順位
2	春大会	Bさん	80	1
6	夏大会	Bさん	80	2
7	夏大会	Cさん	90	3
9	秋大会	Aさん	90	4
3	春大会	Cさん	100	5
5	夏大会	Aさん	100	6
8	夏大会	Dさん	110	7
13	冬大会	Aさん	110	8
1	春大会	Aさん	120	9
10	秋大会	Bさん	120	10
14	冬大会	Bさん	120	11
11	秋大会	Cさん	130	12
16	冬大会	Dさん	130	13
4	春大会	Dさん	140	14
12	秋大会	Dさん	150	15
15	冬大会	Cさん	170	16

欠番なく順位が付きましたね。

でも欲しいのは大会ごとのブービー賞です。 大会単位で順位を判定するにはPARTITION BYを使います。 RANK() over (PARTITION BY まとめたい項目 order by 並べたい項目 asc) となります。

SELECT データID
,大会名
,なまえ
,スコア
,RANK() over (PARTITION BY 大会名 order by スコア asc) AS 順位
FROM 大会成績

データID	大会名	なまえ	スコア	順位
2	春大会	Bさん	80	1
3	春大会	Cさん	100	2
1	春大会	Aさん	120	3
4	春大会	Dさん	140	4
6	夏大会	Bさん	80	1
7	夏大会	Cさん	90	2
5	夏大会	Aさん	100	3
8	夏大会	Dさん	110	4
9	秋大会	Aさん	90	1
10	秋大会	Bさん	120	2
11	秋大会	Cさん	130	3
12	秋大会	Dさん	150	4
13	冬大会	Aさん	110	1
14	冬大会	Bさん	120	2
16	冬大会	Dさん	130	3
15	冬大会	Cさん	170	4

おお、大会ごとの（下位からの）順位がわかりました！

では今のを副問合せにして順位が2位だけ絞ってみましょう。

SELECT temp.データID
,temp.大会名
,temp.なまえ
,temp.スコア
FROM
(
SELECT データID
,大会名
,なまえ
,スコア
,RANK() over (PARTITION BY 大会名 order by スコア asc) AS 順位
FROM 大会成績
) temp
WHERE
temp.順位 = 2

データID	大会名	なまえ	スコア
3	春大会	Cさん	100
7	夏大会	Cさん	90
10	秋大会	Bさん	120
14	冬大会	Bさん	120

各大会のブービー賞です。 BさんCさんが独占していたんですねｗ

RANKとPARTITION BYが無ければプログラム側で何とかしないといけませんがこれなら一発。 便利ですね。 ちなみにこれが利用できるDBは限られています。 OracleやSQLServerは使えますがMySQLは使えなかったりします。 DBを選択する要素として分析関数の充実もチェックしておいたほうがいいかも知れません。