【PostgreSQL】グループ内での順位付けを行うSQL

by SEBONE · 公開済み 2020年8月20日 · 更新済み 2020年8月20日

Table of Contents

目的

一つのテーブルに複数のグループがごちゃ混ぜに入っている場合に、グループごとに順位付けを行うSQLをメモる。

テストデータ

以下のようなデータを例にする。中学生のテスト結果のようなイメージ。
classがクラス、socreに点数、nameが氏名としたとき、クラス毎に順位を出したいとする。

class | score |     name
-------+-------+--------------
 D     |    58 | &#22823;&#23470;&#27491;&#26149;
 E     |    83 | &#23500;&#27704;&#31168;&#38596;
 C     |    56 | &#23470;&#21407;&#37324;&#33756;
 A     |    81 | &#31179;&#26412;&#26089;&#30334;&#21512;
 A     |    55 | &#23567;&#28580;&#26628;&#20316;
 B     |    47 | &#24179;&#26412;&#26691;&#39321;
 B     |    80 | &#21069;&#23798;&#21315;&#31298;
 C     |    60 | &#21513;&#37326;&#32654;&#32000;&#23376;
 D     |    68 | &#23721;&#26412;&#33459;&#23376;
 E     |    96 | &#22823;&#24179;&#35029;&#32654;
 A     |    75 | &#21513;&#23713;&#32903;
 E     |    32 | &#22679;&#20117;&#20844;&#19968;
 E     |    67 | &#32048;&#20117;&#24184;&#27193;
 D     |    78 | &#38263;&#35895;&#37096;&#31456;&#22826;&#37070;
 B     |    68 | &#27744;&#26412;&#23578;
 C     |    94 | &#23460;&#20117;&#31481;&#38596;

順位付けする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 | &#31179;&#26412;&#26089;&#30334;&#21512;   |      1
 A     |    75 | &#21513;&#23713;&#32903;       |      2
 A     |    55 | &#23567;&#28580;&#26628;&#20316;     |      3
 B     |    80 | &#21069;&#23798;&#21315;&#31298;     |      1
 B     |    68 | &#27744;&#26412;&#23578;       |      2
 B     |    47 | &#24179;&#26412;&#26691;&#39321;     |      3
 C     |    94 | &#23460;&#20117;&#31481;&#38596;     |      1
 C     |    60 | &#21513;&#37326;&#32654;&#32000;&#23376;   |      2
 C     |    56 | &#23470;&#21407;&#37324;&#33756;     |      3
 D     |    78 | &#38263;&#35895;&#37096;&#31456;&#22826;&#37070; |      1
 D     |    68 | &#23721;&#26412;&#33459;&#23376;     |      2
 D     |    58 | &#22823;&#23470;&#27491;&#26149;     |      3
 E     |    96 | &#22823;&#24179;&#35029;&#32654;     |      1
 E     |    83 | &#23500;&#27704;&#31168;&#38596;     |      2
 E     |    67 | &#32048;&#20117;&#24184;&#27193;     |      3
 E     |    32 | &#22679;&#20117;&#20844;&#19968;     |      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 | &#31179;&#26412;&#26089;&#30334;&#21512;   |      1
 B     |    80 | &#21069;&#23798;&#21315;&#31298;     |      1
 C     |    94 | &#23460;&#20117;&#31481;&#38596;     |      1
 D     |    78 | &#38263;&#35895;&#37096;&#31456;&#22826;&#37070; |      1
 E     |    96 | &#22823;&#24179;&#35029;&#32654;     |      1

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

【PostgreSQL】グループ内での順位付けを行うSQL

目的

テストデータ

順位付けするSQL

特定の順位だけを抜き出すSQL

おすすめ

コメントを残すコメントをキャンセル

【PostgreSQL】グループ内での順位付けを行うSQL

目的

テストデータ

順位付けするSQL

特定の順位だけを抜き出すSQL

おすすめ

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

PostgreSQL 累積値を計算するSQL

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

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル