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

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

 

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

 

ROWNUM疑似列とは?

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

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

 

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

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

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

 

特に、②のデータの件数の絞り込みと言う点では、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の条件が成立したレコードに対し、1〜の連番を順番に降っていく(加算していく)

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の値が1〜の連番にならないのが確認できるだろう。

 

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をご確認ください。

 

関連記事

(Oracle) RANKで指定列のランク順を取得する
(Oracle) LISTAGGで複数行を1行に集約する

 

https://www.shift-the-oracle.com/sql/column/rownum.html

https://docs.oracle.com/cd/E16338_01/server.112/b56299/pseudocolumns009.htm

 

 

記事が気に入った方はシェアをお願いします!

コメントを残す

メールアドレスが公開されることはありません。