【PostgreSQL】グループ内での順位付けを行うSQL
目的
一つのテーブルに複数のグループがごちゃ混ぜに入っている場合に、グループごとに順位付けを行うSQLをメモる。
テストデータ
以下のようなデータを例にする。中学生のテスト結果のようなイメージ。
classがクラス、socreに点数、nameが氏名としたとき、クラス毎に順位を出したいとする。
class | score | name
-------+-------+--------------
D | 58 | 大宮正春
E | 83 | 富永秀雄
C | 56 | 宮原里菜
A | 81 | 秋本早百合
A | 55 | 小澤栄作
B | 47 | 平本桃香
B | 80 | 前島千穂
C | 60 | 吉野美紀子
D | 68 | 岩本芳子
E | 96 | 大平裕美
A | 75 | 吉岡肇
E | 32 | 増井公一
E | 67 | 細井幸樹
D | 78 | 長谷部章太郎
B | 68 | 池本尚
C | 94 | 室井竹雄順位付けするSQL
以下のSQLで順位付けができる。OVER句を使ってclass毎に並び替えたうえでROW_NUMBER()で順位を振ることができる。
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS ranking
FROM results結果
右端が順位です。
class | score | name | ranking
-------+-------+--------------+--------
A | 81 | 秋本早百合 | 1
A | 75 | 吉岡肇 | 2
A | 55 | 小澤栄作 | 3
B | 80 | 前島千穂 | 1
B | 68 | 池本尚 | 2
B | 47 | 平本桃香 | 3
C | 94 | 室井竹雄 | 1
C | 60 | 吉野美紀子 | 2
C | 56 | 宮原里菜 | 3
D | 78 | 長谷部章太郎 | 1
D | 68 | 岩本芳子 | 2
D | 58 | 大宮正春 | 3
E | 96 | 大平裕美 | 1
E | 83 | 富永秀雄 | 2
E | 67 | 細井幸樹 | 3
E | 32 | 増井公一 | 4特定の順位だけを抜き出すSQL
サブクエリを使用することで、各クラスの特定の順位の人だけを抜き出すこともできます。以下は各クラス1番の人だけを抜き出すコードです。
SELECT
*
FROM
(
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS ranking
FROM results
) as T
WHERE seqnum = 1結果
class | score | name | ranking
-------+-------+--------------+--------
A | 81 | 秋本早百合 | 1
B | 80 | 前島千穂 | 1
C | 94 | 室井竹雄 | 1
D | 78 | 長谷部章太郎 | 1
E | 96 | 大平裕美 | 1短いコードですが、結構使い勝手がいいです。
