(Oracle) ROWNUMで擬似列を取得する

本記事では、
Oracleで擬似列を取得する関数である、ROWNUM関数の使い方について解説していく。

ROWNUM疑似列とは？

そもそもROWNUM疑似列とは何かと言うと、
SQLの結果セットから、ソート前のレコード番号を表す擬似列のことだ。

OracleのROWNUM擬似列

ROWNUM擬似列は、Oracle固有の機能であり、
SELECT文の結果セットに暗黙的に追加される列のことだ（※そのままでは表示はされない）。
ソート前のレコード番号を擬似列として保持する点が後々としてROWNUMの挙動を理解するポイントとなってくるので、上記を頭の片隅に入れて本記事を読み進めてもらいたい。

ROWNUMの使用目的・タイミング

ROWNUM擬似列は、
主に以下の様なシチュエーションで使用することが多い。

ROWNUMの使用タイミング

SQLの結果に対し、連番を振りたい時
「最初の○行」や「○行目〜△行目」など、取得するデータの件数を絞り込みたい時

特に、②のデータの件数の絞り込みと言う点では、Oracle以外のDBMSでは、LIMIT関数と言う専用の関数が存在するが、OracleにはLIMIT関数が存在しないためROWNUMで代替するというパターンが多い。

LIMIT クエリーの最適化

https://dev.mysql.com/doc/refman/5.6/ja/limit-optimization.html

ROWNUMの使い方

では具体的にROWNUMの使い方をサンプルを交えて紹介していく。

① ROWNUM擬似列を取得して、連番を振る

ROWNUM擬似列は、
SELECT句にROWNUMと指定することで、取得することが可能だ。

上記を利用して、以下の様にSQL結果セットに対して連番を振ることが可能だ。

SELECT
  ROWNUM
  , Name
  , Age
FROM
  users


--実行結果
+ ------ + ---- + --- +
| ROWNUM | Name | Age |
| ------ | ---- | --- |
|      1 | Taro |  10 |
|      2 | Jiro |  30 |
|      3 | Goro |  20 |
|      4 | Hide |  50 |
|      5 | Tomo |  40 |
+ ------ + ---- + --- +

上記の結果のように、
レコードの取得順にROWNUM擬似列に連番が振られているのが分かるだろう。

② 取得レコード数を制限する

ROWNUM擬似列を使えば、
WHERE句で取得するレコード数を制限することも可能だ。

次のSQLは、usersテーブルからレコードを3件に絞って取得する例だ。

SELECT
  ROWNUM
  , Name
  , Age
FROM
  users
WHERE
  ROWNUM <= 3


--実行結果
+ ------ + ---- + --- +
| ROWNUM | Name | Age |
| ------ | ---- | --- |
|      1 | Taro |  10 |
|      2 | Jiro |  30 |
|      3 | Goro |  20 |
+ ------ + ---- + --- +

以上でROWNUMの使い方の基本は以上となる。

ROWNUMの注意点・挙動理解

ここからはROWNUMを使用する上でいくつか注意点があるので紹介していく。

①「ROWNUM = 数字」や「BETWEEN」は使えない

ROWNUMでは、

「WHERE ROWNUM = 2」のようにして2行目のレコードだけを取得したり
「BETWEEN 10 AND 20」のように、特定の行数の範囲に絞ってレコード取得したり

と言った使い方ができない。

一見すると成立しそうな使い方にも見えるが、
上記のような使い方をすると、SQLの結果は必ず0件になる。

--レコードは取得できない！
SELECT * FROM users WHERE ROWNUM = 2
SELECT * FROM users WHERE BETWEEN 10 AND 20

上記はOracleの仕様で、ROWNUMは、
WHEREの条件が成立したレコードに対し、１〜の連番を順番に降っていく（加算していく）。

ROWNUM = 2とした場合、SQLがテーブルに対して一行一行結果を評価していく際、最初のレコードで「1 = 2」の条件が不成立となってしまう。つまり、ROWNUMの値は加算されず、また以降のレコードでも同様に「1 = 2」の評価をし続け、結果としてすべてのレコードで条件が不成立となり、レコードが一行も取得されないと言うことになるのである。

「ROWNUM = 数字」を実現するには？

「ROWNUM = 数字」を実現するには、以下のように、
一度サブクエリで連番を振ってから、親のSQLのWHEREでレコードを絞り込むことで実現可能だ。

SELECT
  *
FROM (
  SELECT
    ROWNUM AS num,
    users.*
  FROM
    users
)
WHERE
  num = 2


--実行結果
+ ------ + ---- + --- +
|    num | Name | Age |
| ------ | ---- | --- |
|      2 | Jiro |  30 |
+ ------ + ---- + --- +

② ORDER BYをすると連番通りにならない

ORDER BYを使用してソートする時に、
ROWNUM擬似列を使うときは注意が必要だ。

ROWNUMで取得した結果に対してORDER BYを指定すると、ROWNUMの連番はROWNUMの連番通りにならないからだ。

冒頭で述べたとおり、ROWNUM擬似列は、「ソート前のレコード番号を表す擬似列」だ。つまり、ROWNUMで連番を振った後にORDER BYのソートが行われる。早い話が、ORDER BYが実行されるのはROWNUMより後だと覚えておこう。

以下はORDER BYしたことによって、ROWNUMの値が連番ではなくなるサンプル。

SELECT
  ROWNUM
  , Name
  , Age
FROM
  users
ORDER BY
  Age DESC


--実行結果
+ ------ + ---- + --- +
| ROWNUM | Name | Age |
| ------ | ---- | --- |
|      4 | Hide |  50 |
|      5 | Tomo |  40 |
|      2 | Jiro |  30 |
|      3 | Goro |  20 |
|      1 | Taro |  10 |
+ ------ + ---- + --- +

レコードの取得順にROWNUM擬似列に連番が振られ、
その後に「Age」列のソートが行われるため、ROWNUMの値が１〜の連番にならないのが確認できるだろう。

ROWNUMはグループ単位に連番が振れない

ROWNUM擬似列は、SQLの結果セット全体に対して連番を振るため、グループ単位に連番を振ることができない。

例えば、「ユーザーの地域毎に連番を振る」などと言ったことがROWNUMでは実は実現できない。

ROW_NUMBERでグループ単位に連番を振る

ROW_NUMBER関数を使用すれば、グループ単位に連番を振る時ことが可能だ。

ROW_NUMBER関数を使うことで、
グループ単位に連番を振ったり、ソート条件を指定して連番を振ったりすることが可能となる。

以下のSQLは、ROW_NUMBER関数を使って、
ユーザーのスポーツ(Sport)毎にグループ化して、年齢(Age)の昇順に連番を振る例だ。

SELECT
  ROW_NUMBER() OVER(PARTITION BY Sport ORDER BY Age ASC) Num
  Name
  ,Sport
  ,Age
FROM
  users


--実行結果
+ --- + ---- + -------- + --- +
| Num | Name | Sport    | Age |
| --- | ---- | -------- | --- |
|   1 | Taro | Tennis   |  10 |
|   2 | Jiro | Tennis   |  20 |
|   1 | Goro | Baseball |  30 |
|   2 | Hide | Baseball |  40 |
|   1 | Tomo | Soccer   |  50 |
+ --- + ---- + -------- + --- +

ROW_NUMBER以外にも、RANK関数などで順位付けした連番を振ることも可能だ。

記事を取得できませんでした。記事IDをご確認ください。