よく使われる順位付け関数 2 - RANK, DENSE_RANK

前回は「よく使われる順位付け関数 1 - ROW_NUMBER 」で、よく使われる順位付け関数のうち ROW_NUMBER を使ってシーケンス番号を振ってみました。

次は、順位付け関数の RANK と DENSE_RANK について見ていきましょう。

こちらのスクリプトを実行して、テーブルを再生成しておいてください。全てのテーブルは使いませんが、これらのテーブルが存在している前提で進めます。

RANK ファンクション

RANK ファンクションは、結果セットに順位を振ってくれる関数です。

構文は次の通りで、ファンクション名以外は ROW_NUMBER と同じです。

RANK ( ) OVER ( [ PARTITION BY [パティションカラム 1 ], [パティションカラム 2], ... ] 
                  ORDER BY [ソートカラム 1], [ソートカラム 2], ...  )

ROW_NUMBER ファンクションは各行に違う番号を振っていきましたが、RANK ファンクションは指定したパティションやソートカラムの値が同じ場合は、同じ番号が振られます。

以下のように、ROW_NUMBER ファンクションで振るのと同じように番号を振っていき、パティションやソートカラムの値が同じレコードがあれば、最初のレコードに振られた番号を同じグループのレコードにも振る感じです。

ROW_NUMBER	RANK	パティション・ソートカラムの値
1	1
2	2
3	2	<- RowNo 2 と同じ
4	2	<- RowNo 2 と同じ
5	5
6	6
7	6	<- RowNo 6 と同じ
8	7

パティションやソートカラムの値が同じカラムに振られるはずだった番号はスキップされます。

例えば、次のような Student テーブルがあります。

前回 ROW_NUMBER 関数の時と同様に、誕生日の年が古い -> 新しい、男性　-> 女性という順で、今度は RANK 関数を使って順位を取得してみましょう。

SELECT  ROW_NUMBER() OVER (ORDER BY YEAR(Birthday), Gender DESC) AS RowNo,
        RANK() OVER (ORDER BY YEAR(Birthday), Gender DESC) AS RankNo,
        *
FROM    Student;

[ 実行結果 ]

同じ誕生日の年で、同じ性別の学生には、同じ番号が振られていて、3 と 5 はスキップされていますね。

DENSE_RANK ファンクション

DENSE_RANK ファンクションは、構文や順位付けについては RANK ファンクションとほぼ同じで、違いは番号をスキップしないところです。

ROW_NUMBER	RANK	DENSE_RANK	パティション・ソートカラムの値
1	1	1
2	2	2
3	2	2	<- RowNo 2 と同じ
4	2	2	<- RowNo 2 と同じ
5	5	3
6	6	4
7	6	4	<- RowNo 6 と同じ
8	7	5

先ほどと同様の条件で DENSE_RANK ファンクションでも順位を取得してみましょう。

SELECT  ROW_NUMBER() OVER (ORDER BY YEAR(Birthday), Gender DESC) AS RowNo,
        RANK() OVER (ORDER BY YEAR(Birthday), Gender DESC) AS RankNo,
        DENSE_RANK() OVER (ORDER BY YEAR(Birthday), Gender DESC) AS DenseRankNo,
        *
FROM    Student;

[ 実行結果 ]

パティションやソートカラムの値が同じレコードは同じ順位になっていますが、RANK ファンクションとは違って番号がスキップされず、連続した番号が振られていますね。

次は、順位付け関数の NTILE を使ってグループ分けしてみましょう。

次へよく使われる順位付け関数 3 - NTILE