RANK関数の基本的な使い方

はじめに

あまりRANK関数を記述する機会がないので いざ書こうとなると検索しては試してエラー、、の繰り返し、、 基本的な内容に絞ってまとめておきます

準備

CREATE TABLE students (
  id INT,
  name VARCHAR(255),
  classes VARCHAR(255),
  subject VARCHAR(255),
  score INT
);

INSERT INTO students (id, name, classes, subject, score)
VALUES
  (1, '田中 太郎', 'A組', '数学', 80),
  (2, '鈴木 花子', 'A組', '理科', 90),
  (3, '佐藤 次郎', 'B組', '数学', 70),
  (4, '高橋 さやか', 'B組', '理科', 85),
  (5, '山田 菜々子', 'C組', '数学', 75),
  (6, '中村 美穂', 'C組', '理科', 92),
  (7, '小林 大輔', 'D組', '数学', 68),
  (8, '加藤 美佐子', 'D組', '理科', 87),
  (9, '吉田 智子', 'E組', '数学', 72),
  (10, '山口 恵美', 'E組', '理科', 94);

書き方

SELECT
name
,score
,RANK() OVER (ORDER BY score DESC) AS score_rank
FROM students;

-- 出力結果
  name     | subject | score |  score_rank
-----------+---------+-------+------------
山口 恵美　    理科      94         1
中村 美穂　    理科      92         2
鈴木 花子　    理科      90         3
加藤 美佐子    理科      87         4
高橋 さやか    理科      85         5
田中 太郎　    数学      80         6
山田 菜々子    数学      75         7
吉田 智子　    数学      72         8
佐藤 次郎　    数学      70         9
小林 大輔　    数学      68         10

これで点数が高い順に並べることができました。 ただ実際には、科目（subject）ごとの順位で分けるべきなので 科目ごとのランクを記述するのが以下です。

SELECT
name
,subject
,score
,RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS score_rank
FROM students;

-- 出力結果
  name     | subject | score |  score_rank
-----------+---------+-------+------------
田中 太郎　    数学      80         1
山田 菜々子    数学      75         2
吉田 智子　    数学      72         3
佐藤 次郎　    数学      70         4
小林 大輔　    数学      68         5
山口 恵美　    理科      94         1
中村 美穂　    理科      92         2
鈴木 花子　    理科      90         3
加藤 美佐子    理科      87         4
高橋 さやか    理科      85         5

これで科目ごとの順位をつけることができました。 PARTITION BYでGROUP BYと似たような動きができます。 科目（subject）ごとに、点数（score）を高い順位に並び替え（ORDER BY + DESC）ています。

追記(2023/03/07)

似ている関数にROW_NUMBERがあります。 区分けは以下

• RANK：等しい値があった場合、同一順位をつける
• ROW_NUMBER：等しい値があった場合、別順位をつける

同点の生徒がいるようにstudentsテーブルを以下に修正して挙動を見てみます。

DROP TABLE students;

INSERT INTO students (id, name, classes, subject, score)
VALUES
  (1, '田中 太郎', 'A組', '数学', 80),
  (2, '鈴木 花子', 'A組', '理科', 90),
  (3, '佐藤 次郎', 'B組', '数学', 80),
  (4, '高橋 さやか', 'B組', '理科', 85),
  (5, '山田 菜々子', 'C組', '数学', 75),
  (6, '中村 美穂', 'C組', '理科', 92),
  (7, '小林 大輔', 'D組', '数学', 80),
  (8, '加藤 美佐子', 'D組', '理科', 87),
  (9, '吉田 智子', 'E組', '数学', 72),
  (10, '山口 恵美', 'E組', '理科', 90);

RANK関数の場合

SELECT
name
,subject
,score
,RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS score_rank
FROM students;

-- 出力結果
  name     | subject | score |  score_rank
-----------+---------+-------+------------
田中 太郎　    数学      80         1
佐藤 次郎　    数学      80         1
小林 大輔　    数学      80         1
山田 菜々子    数学      75         4
吉田 智子　    数学      72         5
中村 美穂　    理科      92         1
鈴木 花子　    理科      90         2
山口 恵美　    理科      90         2
加藤 美佐子    理科      87         4
高橋 さやか    理科      85         5

ROW_NUMBER関数の場合

SELECT
name
,subject
,score
,ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS score_rank
FROM students;

-- 出力結果
  name     | subject | score |  score_rank
-----------+---------+-------+------------
田中 太郎　    数学      80         1
佐藤 次郎　    数学      80         2
小林 大輔　    数学      80         3
山田 菜々子    数学      75         4
吉田 智子　    数学      72         5
中村 美穂　    理科      92         1
鈴木 花子　    理科      90         2
山口 恵美　    理科      90         3
加藤 美佐子    理科      87         4
高橋 さやか    理科      85         5