SQLで順位を取得するRANK()とDENSE_RANK()

SQLServerで項目の順位付け関数を利用して順位をします。

SQLで順位を取得する

SQLで順位を取得する

SQLServerで項目の順位付け関数を利用して順位をします。

テーブルの準備

学生のテスト結果を保持するテーブルを作成します。

CREATE TABLE 成績テーブル
(
     学生番号  INT
    ,氏名      VARCHAR(20)
    ,クラス    INT
    ,得点      INT
)

データの準備

各クラスのテスト結果を登録します。

INSERT INTO 成績テーブル VALUES (  1, '太郎', 1, 75 )
INSERT INTO 成績テーブル VALUES (  2, '次郎', 1, 70 )
INSERT INTO 成績テーブル VALUES (  3, '三郎', 2, 72 )
INSERT INTO 成績テーブル VALUES (  4, '四郎', 3, 75 )
INSERT INTO 成績テーブル VALUES (  5, '五郎', 1, 73 )
INSERT INTO 成績テーブル VALUES (  6, '六郎', 3, 68 )
INSERT INTO 成績テーブル VALUES (  7, '七郎', 3, 59 )
INSERT INTO 成績テーブル VALUES (  8, '八郎', 2, 79 )
INSERT INTO 成績テーブル VALUES (  9, '九郎', 2, 72 )
INSERT INTO 成績テーブル VALUES ( 10, '十郎', 2, 70 )

学年全部の得点順位を取得

まずは学年全体でのテスト結果順位を取得してみます。

SELECT
     クラス
    ,RANK() OVER(ORDER BY 得点 DESC) AS 順位
    ,学生番号
    ,氏名
    ,得点
FROM
    成績テーブル

以下が実行結果です。

クラス 順位  学生番号  氏名  得点
    2    1          8  八郎    79
    1    2          1  太郎    75
    3    2          4  四郎    75
    1    4          5  五郎    73
    2    5          9  九郎    72
    2    5          3  三郎    72
    2    7         10  十郎    70
    1    7          2  次郎    70
    3    9          6  六郎    68
    3   10          7  七郎    59

得点の高い順に並んでいます。

RANK()関数は同じ順位があると「1,2,2,4…」というように同じ順位は同位となり、次の順位は飛ぶことになります。

クラスごとの得点順位を取得

次にクラスごとテスト結果順位を取得してみます。

SELECT
     クラス
    ,RANK() OVER(PARTITION BY クラス ORDER BY 得点 DESC) AS 順位
    ,学生番号
    ,氏名
    ,得点
FROM
    成績テーブル

クラスごとにする場合は「PARTITION BY」を利用してクラスごとに振り分けます。

以下が実行結果です。

クラス  順位  学生番号 氏名   得点
    1    1          1  太郎    75
    1    2          5  五郎    73
    1    3          2  次郎    70
    2    1          8  八郎    79
    2    2          9  九郎    72
    2    2          3  三郎    72
    2    4         10  十郎    70
    3    1          4  四郎    75
    3    2          6  六郎    68
    3    3          7  七郎    59

順位が飛ばないDENSE_RANK()関数

RANK()関数と同じ順位付け関数にDENSE_RANK()がありますが違いはこちらは同位があっても次の順位は飛びません。

SELECT
     クラス
    ,DENSE_RANK() OVER(ORDER BY 得点 DESC) AS 順位
    ,学生番号
    ,氏名
    ,得点
FROM
    成績テーブル

以下が実行結果です。

クラス  順位  学生番号 氏名   得点
    2    1          8  八郎    79
    1    2          1  太郎    75
    3    2          4  四郎    75
    1    3          5  五郎    73
    2    4          9  九郎    72
    2    4          3  三郎    72
    2    5         10  十郎    70
    1    5          2  次郎    70
    3    6          6  六郎    68
    3    7          7  七郎    59

RANK()とDENSE_RANK()を使えばグルー内での順位が簡単に取得することができます。

以上、SQLで順位を取得するRANK()とDENSE_RANK()でした。