本記事では、Oracleで、
行番号を取得することができるROW_NUMBER関数の使い方について解説していく。
ROW_NUMBER –行番号を取得する–
OracleのROW_NUMBER関数を使えば、
SQLの結果セットに対して行番号を取得することが可能だ。
ROW_NUMBERの構文
ROW_NUMBER関数の構文は以下の様になっている。
ROW_NUMBER() OVER ( [ PARTITION BY [パーティションカラム], [パーティションカラム], ...] ORDER BY [ソートカラム])
パーティションカラム (PARTITION BY)
- パーティションカラムを指定した場合は、
結果セットをパーティションカラムで分割し、パーティション毎に1〜の連番を振る。 - パーティションカラムを指定しない場合は、結果セット全体で1〜の連番を振る。
ソートカラム (ORDER BY)
- パーティション内の行に連番を振る順序を指定する。
- ソートカラムの指定は必須で省略できない。
ROW_NUMBER 関数の使用例
ここからは実際に、ROW_NUMBERを使用して行番号を取得するサンプルを紹介していく。
① 行番号を取得する
以下では、社員の年齢順に並べた結果に、ROW_NUMBERで行番号を付けて取得するサンプル。
select
id,
dept,
age,
row_number() over (order by sal desc) as num
from
emp;
ID | DEPT | AGE | NUM |
4 | 営業部 | 40 | 1 |
6 | 技術部 | 38 | 2 |
2 | 営業部 | 35 | 3 |
3 | 営業部 | 35 | 4 |
5 | 技術部 | 33 | 5 |
1 | 営業部 | 30 | 6 |
ROW_NUMBER は ユニークに割り当てられ、
同一条件(年齢が同じ)場合でも異なる値となる※。
後述するが、同じく番号を降る関数にrank()関数が存在する。
rank()関数では同一条件の場合、同一の番号を振ると言う違いがあるので押さえておきたいポイントである。
② グループ毎に行番号を取得する
次に、partition byを使って、
部署別のグループ毎に、年齢の高い順に並べた結果に行番号を付けて取得するサンプルを紹介する。
①のサンプルに加えて、部署ごとにグループ化して行番号を割り当てているという違いがある。
select
id,
dept,
age,
row_number() over (partition by dept order by age desc) as num
from
emp;
ID | DEPT | AGE | NUM |
6 | 技術部 | 38 | 1 |
5 | 技術部 | 33 | 2 |
4 | 営業部 | 40 | 1 |
2 | 営業部 | 35 | 2 |
3 | 営業部 | 35 | 3 |
1 | 営業部 | 30 | 4 |
部署毎にグループ化した結果に対して、1からの連番(ROW_NUMBER)が振られているのが分かる。
同一条件で同じ数字を振る場合は、RANK()関数を使う
ROW_NUMBER 関数では、行番号はユニークに割り当てられてしまうが、
同一条件で同じ数字を割り当てるには rank 関数を使用する。
select
id,
dept,
age,
rank() over (partition by dept order by sal desc) as num
from
emp;
ID | DEPT | AGE | NUM |
6 | 技術部 | 38 | 1 |
5 | 技術部 | 33 | 2 |
4 | 営業部 | 40 | 1 |
2 | 営業部 | 35 | 2 |
3 | 営業部 | 35 | 2 |
1 | 営業部 | 30 | 4 |
rankを使ったことで、年齢が35歳の人同士の部分の連番は、同じく2が振られているのが分かる。
DENSE_RANK()関数でランクのスキップを防ぐ
rank 関数は、同一条件のレコードに同じランクを表示するが、その場合、次に表示するランクはスキップされたランクとなる(上記のサンプルでは、ランキング2 が 2つ存在するために、ランキング3 をスキップし、ランキング4 を出力している)。
このとき、
rank 関数ではなく、DENSE_RANK 関数を使用するとランキング3 がスキップされなくなる。
select
id,
dept,
age,
dense_rank() over (order by age desc) as num
from
emp;
ID | DEPT | AGE | NUM |
4 | 営業部 | 40 | 1 |
6 | 技術部 | 38 | 2 |
2 | 営業部 | 35 | 3 |
3 | 営業部 | 35 | 3 |
5 | 技術部 | 33 | 4 |
1 | 営業部 | 30 | 5 |
DENSE_RANKを使用したことで、ランクのスキップが起こらず連番が振られているのが分かるだろう。
関連記事
https://www.shift-the-oracle.com/sql/functions/row_number-practice.html
https://docs.oracle.com/cd/E82638_01/sqlrf/ROW_NUMBER.html
降順で連番を振る場合はORDER BY <COLUMN_NAME> DESC
のように指定する。