(Oracle) ROW_NUMBERで行番号を取得する

本記事では、Oracleで、
行番号を取得することができるROW_NUMBER関数の使い方について解説していく。

ROW_NUMBER　–行番号を取得する–

OracleのROW_NUMBER関数を使えば、
SQLの結果セットに対して行番号を取得することが可能だ。

ROW_NUMBERの構文

ROW_NUMBER関数の構文は以下の様になっている。

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

パーティションカラム (PARTITION BY)

• パーティションカラムを指定した場合は、
  結果セットをパーティションカラムで分割し、パーティション毎に１〜の連番を振る。
• パーティションカラムを指定しない場合は、結果セット全体で１〜の連番を振る。

ソートカラム (ORDER BY)

• パーティション内の行に連番を振る順序を指定する。
• ソートカラムの指定は必須で省略できない。

ROW_NUMBER 関数の使用例

ここからは実際に、ROW_NUMBERを使用して行番号を取得するサンプルを紹介していく。

① 行番号を取得する

以下では、社員の年齢順に並べた結果に、ROW_NUMBERで行番号を付けて取得するサンプル。

select
  id,
  dept,
  age,
  row_number() over (order by sal desc) as num
from
  emp;

ROW_NUMBER は ユニークに割り当てられ、
同一条件（年齢が同じ）場合でも異なる値となる※。

② グループ毎に行番号を取得する

次に、partition byを使って、
部署別のグループ毎に、年齢の高い順に並べた結果に行番号を付けて取得するサンプルを紹介する。

①のサンプルに加えて、部署ごとにグループ化して行番号を割り当てているという違いがある。

select
  id,
  dept,
  age,
  row_number() over (partition by dept order by age desc) as num
from
  emp;

部署毎にグループ化した結果に対して、1からの連番（ROW_NUMBER）が振られているのが分かる。

同一条件で同じ数字を振る場合は、RANK()関数を使う

ROW_NUMBER 関数では、行番号はユニークに割り当てられてしまうが、
同一条件で同じ数字を割り当てるには rank 関数を使用する。

select
  id,
  dept,
  age,
  rank() over (partition by dept order by sal desc) as num
from
  emp;

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;

DENSE_RANKを使用したことで、ランクのスキップが起こらず連番が振られているのが分かるだろう。

関連記事

関連記事(Oracle) ROWNUMで擬似列を取得する

2022.09.11

関連記事(Oracle) RANKで指定列のランク順を取得する

2022.09.11

関連記事(Oracle) LISTAGGで複数行を1行に集約する

2022.09.11

https://www.shift-the-oracle.com/sql/functions/row_number-practice.html

https://docs.oracle.com/cd/E82638_01/sqlrf/ROW_NUMBER.html