順位のRANK、DENSE_RANK、NTILEと連番のROW_NUMBER [SQLServer]

SQLでデータを取得する際に、例えばセールスマンの売り上げ金額や学生の試験の得点に順位を付けたり、顧客ID順に連番を振りたい時があります。
SQLServerには上記のように順位を付けたり、連番を振るために順位付け関数が用意されています。
今回はその関数を紹介します。

順位付けに使用するテーブル

順位付けを行うサンプルデータを作成します。
ここでは、従業員IDと部門と金額を列に持つテーブルの「売上テーブル」を作成します。

売上テーブルの定義

各列の列名、データ型は次のようにします。

列名	データ型
従業員ID	INT
部門	NVARCHAR(1)
金額	INT

テーブルのデータ

テーブルには以下のデータを登録します。

従業員ID	部門	金額
101	A	1030
102	A	1591
103	A	1040
104	B	1759
105	B	1200
106	B	1546
107	B	2538
108	B	1382
109	C	2240
110	C	1853
111	C	2985
112	C	2240

（金額の単位は千円）

CREATEとINSERTのSQL

上記の売上テーブルへのデータのインサートは、複数のレコードを1回のSQLで一括挿入する形式で記述しています。
1回のINSERTで複数行のレコードをテーブルに一括で挿入する方法については、以下の記事を参照してください。

1回のINSERT（インサートSQL）で複数行のレコードを一括挿入（追加）する

SQLServerやMySQLなどのデータベースで、テーブルにレコードをINSERT文使用して追加するには、通常は以下のように記述します。 ...

johobase.com

順位を付けるRANK関数

RANK関数は指定した（列の）値に順位をつけて返します。
RANK関数は次のようにして使います。

先ず「RANK() 」と記述します。
続けて「OVER(」と記述します。
そして「OVER(」の中に「ORDER BY」句を記述し、順位を比較する列名を指定します。
最後に「)」を閉じます。

RANK関数を使って売上テーブルの金額に順位をつけて取得します。
順位は金額の高い従業員を上位とします。

RANK関数はORDER BY句に指定した順に順位を付けますので、ここでは金額の降順になるようにDESCキーワードを指定しています。

ID	金額	順位
111	2985	1
107	2538	2
112	2240	3
109	2240	3
110	1853	5
104	1759	6
102	1591	7
106	1546	8
108	1382	9
105	1200	10
103	1040	11
101	1030	12

取得した結果の順位を見てみると3位が2人いることがわかります。（3～4行目の従業員IDが「112」と「109」の従業員）また、次の順位は3位が2人いるために5位になっています。（5行目の従業員IDが「110」の従業員）
RANK関数では同じ順位のレコードが複数ある場合は、その次にくる順位が同一人数分加算され、結果として順位が飛ぶことになります。

順位が飛ばないDENSE_RANK関数

DENSE_RANK関数はRANK関数と似ていますが、同じ順位のレコードがある場合に、その次のレコードの順位がRANK関数と異なります。
RANK関数では同じ順位のレコードが複数ある場合は、次のレコードの順位が飛びますが、DENSE_RANK関数は順位の番号が飛びません。
以下にDENSE_RANK関数で従業員全体での順位を取得するSQLを記載します。（構文はRANK関数と同じです。）

ID	金額	順位
111	2985	1
107	2538	2
112	2240	3
109	2240	3
110	1853	4
104	1759	5
102	1591	6
106	1546	7
108	1382	8
105	1200	9
103	1040	10
101	1030	11

RANK関数と同じ条件でDENSE_RANK関数を使って順位を付けると、2人いる3位の次の順位が5位ではなく4位になっています。（5行目の従業員IDが「110」の従業員）

連番を振るROW_NUMBER関数

ROW_NUMBER関数もRANK関数に似ていますが、ROW_NUMBER関数は厳密にいうと順位を付ける関数ではなく、連番を振る関数になります。
RANK関数、DENSE_RANK関数では順位を比較する値が同一の場合は同じ順位が付けられましたが、ROW_NUMBER関数では値が同じ場合でも違う順位（連番）が付けられます。

ID	金額	順位
111	2985	1
107	2538	2
112	2240	3
109	2240	4
110	1853	5
104	1759	6
102	1591	7
106	1546	8
108	1382	9
105	1200	10
103	1040	11
101	1030	12

ROW_NUMBER関数では順位を比較する金額が同じ従業員（3～4行目の従業員IDが「112」と「109」の従業員）のレコードに違う順位が付けられています。

グループごとに順位を付ける

ここまでの例では、売上テーブルのデータ全体に対して順位を取得してきましたが、RANK関数、DENSE_RANK関数、ROW_NUMBER関数では、データをカテゴリーや区分ごとにグループ化して順位を付けることができます。
グループ化して順位を付ける場合は、関数のOVER()内のORDER BY句の前にPARTITION BY句を記述し、続けてグループ化に使用する列を記述します。

売上テーブルのデータを部門ごとにグループ化して順位を付ける例を以下に示します。

102	A	1591	1
103	A	1040	2
101	A	1030	3
107	B	2538	1
104	B	1759	2
106	B	1546	3
108	B	1382	4
105	B	1200	5
111	C	2985	1
112	C	2240	2
109	C	2240	2
110	C	1853	4

NTILE関数

順位付け関数にはNTILE関数という関数もあります。
NTILE関数は単純に順位を付ける関数ではなく、順位を付けたレコードをグループごとに均等な人数で分割します。
例えば15人を対象に3つのグループに分ける場合は、順位が1位～5位をグループ1、6位～10位をグループ2、11位～15位をグループ3に分けます。

売上テーブルの12のレコードのデータを4つに分ける場合は次のようになります。

順位が高い順に3人ごとに4つのグループに分割されます。

今度は5つに分けてみます。

5では割り切れないので上位のグループ2つが3人ずつ、それ以下のグループ3つが2人ずつで、合計5つのグループに分割されます。

各関数の挙動についてのまとめ

RANK関数、DENSE_RANK関数、ROW_NUMBER関数を使うことで順位付けができる。
順位はデータ全体、および特定の条件で分けたグループごとに付けることができる。
順付け関数には順位付けした結果をグループ化できるNTILE関数もある。

RANK関数

順位を付ける関数。
比較対象の値が同じ場合は同じ順位になり、その次は順位を飛ばします。
【例】1位、2位、2位、4位・・・

DENSE_RANK関数

順位を付ける関数。
比較対象の値が同じ場合は同じ順位になり、その次は順位を飛ばしません。
【例】1位、2位、2位、3位・・・

ROW_NUMBER関数

連番ををつける関数。
比較対象の値が同じ場合であっても同じ順位にはならず、順位をカウントアップします。
【例】1位、2位、3位、4位・・・
比較対象の値が同じ場合はORDER BY句で取得される順番に順位が付きます。

NTILE関数

指定した数のグループに順位付けした結果を分ける関数。