RANK関数とは？
RANK関数とは、SQLクエリで取得した結果セットの各データに順位をつけて返す関数のことです。
データの順位は、1から順に振られます。
RANK関数とROW_NUMBERは似ていますが、ROW_NUMBERがすべてのデータに一意に番号をつけるのに対し、RANK関数は同順位に対して同じ番号をつけます。
それぞれの違いは次の記事で解説していますので、参考にしてみてください。
【関連記事】
▶︎【SQL】PARTITION BYの基礎から列ごとの計算方法まとめ
RANK関数(RANK OVER)の使い方
RANK関数は次の書式で使用します。
RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )
サンプルで実際の動きも確認してみましょう。
生徒の成績が登録してあるstudentテーブルが次のようになっているとします。
+------+-----------+-------+ | No | name | score | +------+-----------+-------+ | 1 | Suzuki | 78 | | 2 | Itou | 85 | | 3 | Tanaka | 66 | | 4 | Sasaki | 76 | | 5 | Abe | 59 | | 6 | Yamamoto | 92 | | 7 | Kawamura | 78 | | 8 | Nakashima | 59 | | 9 | Taguchi | 91 | | 10 | Uchida | 65 | +------+-----------+-------+
このテーブルをscoreの高い順に並び替え、順位づけするには次のように記述しましょう。
SELECT No, name, score, RANK() OVER(ORDER BY score DESC) AS rank_result FROM student; +------+-----------+-------+-------------+ | No | name | score | rank_result | +------+-----------+-------+-------------+ | 6 | Yamamoto | 92 | 1 | | 9 | Taguchi | 91 | 2 | | 2 | Itou | 85 | 3 | | 1 | Suzuki | 78 | 4 | | 7 | Kawamura | 78 | 4 | | 4 | Sasaki | 76 | 6 | | 3 | Tanaka | 66 | 7 | | 10 | Uchida | 65 | 8 | | 5 | Abe | 59 | 9 | | 8 | Nakashima | 59 | 9 | +------+-----------+-------+-------------+ 10 rows in set (0.00 sec)
同じscoreの場合には同じ順位が振られているのがわかりますね。
RANK関数でWHERE句を使って、TOP3を出力する
例えば、scoreの高い上位3位を取得したい場合などは、RANK関数とWHERE句を組み合わせることで出力できます。
先ほどのテーブルを使って確認してみましょう。
記述方法は次のようになります。
SELECT s.* FROM(SELECT No, name, score, RANK() OVER(ORDER BY score DESC) AS rank_result FROM student) AS s WHERE s.rank_result <= 3;
RANK関数の使い方で紹介したSQLクエリを「SELECT s.* FROM()」に記述しています。
SELECT s.* FROM(‘ここに記述’) AS s WHERE s.rank_result <= 3;
順位づけした状態で、studentテーブルのランクをWHERE句で3以下（1,2,3）だけ取得すればOKです。
表示結果は次のようになります。
+------+----------+-------+-------------+ | No | name | score | rank_result | +------+----------+-------+-------------+ | 6 | Yamamoto | 92 | 1 | | 9 | Taguchi | 91 | 2 | | 2 | Itou | 85 | 3 | +------+----------+-------+-------------+ 3 rows in set (0.01 sec)
ランクづけできる関数まとめ
データに順位をつける方法は、RANK関数以外にもあります。
ここでは、RANK関数以外に次の2つを紹介します。
- DENSE_RANK関数
- ROW_NUMBER関数
DENSE_RANK関数とは？
DENSE_RANK関数とは、RANK関数とは異なり順位づけした番号をスキップしない関数のことです。
書式や順位付けについては RANK関数とほぼ同じになります。
DENSE_RANK関数の使い方
先ほどのテーブルで動作を確認してみましょう。
RANK()の部分をDENSE_RANK()にすればOKです。
SELECT No, name, score, DENSE_RANK() OVER(ORDER BY score DESC) AS rank_result FROM student; +------+-----------+-------+-------------+ | No | name | score | rank_result | +------+-----------+-------+-------------+ | 6 | Yamamoto | 92 | 1 | | 9 | Taguchi | 91 | 2 | | 2 | Itou | 85 | 3 | | 1 | Suzuki | 78 | 4 | | 7 | Kawamura | 78 | 4 | | 4 | Sasaki | 76 | 5 | | 3 | Tanaka | 66 | 6 | | 10 | Uchida | 65 | 7 | | 5 | Abe | 59 | 8 | | 8 | Nakashima | 59 | 8 | +------+-----------+-------+-------------+ 10 rows in set (0.00 sec)
同列の4の次に5がつけられています。
ROW_NUMBER関数とは？
ROW_NUMBER関数は、RANK関数の部分でも少し説明したように、同率順位であっても同じ順位にはならず、順位を個別でカウントする関数です。
書式はRANK関数とほぼ同じになります。
ROW_NUMBER関数の使い方
ROW_NUMBER関数も、先ほどのテーブルを使って動作を確認してみましょう。
RANK()の部分をROW_NUMBER()にすればOKです。
SELECT No, name, score, ROW_NUMBER() OVER(ORDER BY score DESC) AS rank_result FROM student; +------+-----------+-------+-------------+ | No | name | score | rank_result | +------+-----------+-------+-------------+ | 6 | Yamamoto | 92 | 1 | | 9 | Taguchi | 91 | 2 | | 2 | Itou | 85 | 3 | | 1 | Suzuki | 78 | 4 | | 7 | Kawamura | 78 | 5 | | 4 | Sasaki | 76 | 6 | | 3 | Tanaka | 66 | 7 | | 10 | Uchida | 65 | 8 | | 5 | Abe | 59 | 9 | | 8 | Nakashima | 59 | 10 | +------+-----------+-------+-------------+ 10 rows in set (0.00 sec)
同率のscoreがあっても、一意に順位が振られています。
「78」で同率になっている部分はNoが「1→7」の順番になっていますよね。
この部分、Noが大きい「7→1」にしたい場合もあるかもしれません。
このように同率の順位の表示を変えたい場合は、ORDER BY句にNoを追加します。
SELECT No, name, score, ROW_NUMBER() OVER(ORDER BY score DESC, No DESC) AS rank_result FROM student; | No | name | score | rank_result | +------+-----------+-------+-------------+ | 6 | Yamamoto | 92 | 1 | | 9 | Taguchi | 91 | 2 | | 2 | Itou | 85 | 3 | | 7 | Kawamura | 78 | 4 | | 1 | Suzuki | 78 | 5 | | 4 | Sasaki | 76 | 6 | | 3 | Tanaka | 66 | 7 | | 10 | Uchida | 65 | 8 | | 8 | Nakashima | 59 | 9 | | 5 | Abe | 59 | 10 | +------+-----------+-------+-------------+ 10 rows in set (0.00 sec)
これで同率順位があってもNoが降順になるように表示されました。
scoreが「59」のNoも「8→5」の順番になっています。
まとめ
SQLのRANK関数について解説しました。
RANK関数は、各データに順位をつけて返す関数のことです。
RANK関数以外にも、DENSE_RANK関数、ROW_NUMBER関数など順位づけできる関数があります。
ぜひこの記事を参考に、SQLのRANK関数や順位づけについてマスターしてください！
