【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

短いコードですが、結構使い勝手がいいです。