PostgreSQL

【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

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

-PostgreSQL

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

【PostgreSQL】縦持ちデータを横持ち変換し前年同月との比較をする

Contents1 目的2 元データ3 横持にするSQL4 結果5 まとめ 目的 システムから取り出したデータは正規化されており縦持ちのデータ構造が多いですが、口座の残高やカードの支払い金額など、前年 …

WindowsにPostgreSQLをインストールしてサーバー化

Contents1 目的2 環境3 PostgreSQLインストール4 外部接続許可設定5 Firewall開放6 接続テスト7 まとめ 目的 自分の身の回りのデータ(特に口座残高などのお金関連)を管 …

VSCodeからPostgreSQLへ接続してSQL発行まで

Contents1 目的2 環境3 Step1 : プラグインのインストール4 Step2 : データベースとの接続5 Step3 : SQLの発行6 補足 目的 VSCodeが優秀なので、VSCod …

PostgreSQL 累積値を計算するSQL

Contents1 目的2 Step1 : テーブル用意3 Step2 : 累積値を計算するSQL 目的 ある値の累積値をSQLで計算する。 Step1 : テーブル用意 以下のようなテーブルを用意し …

PostgreSQL カンマ付き文字列金額データを数値変換

BIツールなどで金額データを扱いたい場合に、金額データがカンマ区切りの文字列データの場合があります。 例:306,327 このような文字列データを数値に変換します。 SQL 以下のようなデータを持つテ …

言語切り替え

カテゴリー