【SQL】RONUMで連番を取得!Oracleで連番を取得する方法と注意点を解説
  • facebookページ
  • twitterページ
  • 2020.06.19

    【SQL】RONUMで連番を取得!Oracleで連番を取得する方法と注意点を解説

    ROWNUM疑似列とは?

    ROWNUM疑似列は、SQLの結果セットから、ソート前のレコード番号を表す擬似列です。
    Oracle固有機能である ROWNUM擬似列は、SELECT文の結果セットに暗黙的に追加されます。
    この記事では、SQLで ROWNUMを使う方法と注意点を解説します。

    ROWNUMの用途

    ROWNUM擬似列は、主にSQLの結果に対し、連番を振りたい時に使用します。
    また、データを検索する時に「最初の○行」や「○行目〜△行目」など、取得するデータの件数を絞り込みたい時にも使用します。

    ROWNUMの基本的な使い方

    ROWNUM擬似列は、SELECT句に ROWNUMと指定することで、取得できます。

    上の結果のように、レコードの取得順に ROWNUM擬似列に連番が振られています。

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

    WHERE句で ROWNUM擬似列を使って、取得するレコード数を制限することもできます。
    次のSQLは、usersテーブルからレコードを10件に絞って取得する例です。

    「ROWNUM = 2」は使えない

    前の結果を見ると、「 WHERE ROWNUM = 2」のようにして、2行目のレコードだけを取得したり、「 BETWEEN 10 AND 20」のように、特定の行数の範囲に絞ってレコード取得できるのでは…?と思う方もいるでしょう。
    実は、このような条件は無効で、SQLの結果は必ず0件になります。

    上の結果に疑問を持つ方もいると思いますが、これはOracleの仕様通りの動作です。
    ROWNUMは、WHEREの条件で一致したレコードに対し、1〜の連番を降ります。
    つまり ROWNUM = 2とした場合、最初のレコードで 1 = 2の条件が不成立となり、以降のレコードも ROWNUMの値が変わらず「1」のままのため、すべてのレコードで条件が不成立となり、結果としてレコードが選択されません。

    ROWNUMで2レコード目だけを抽出する場合は、以下のように、一度サブクエリで連番を振ってから、親のSQLのWHEREでレコードを絞り込みます。

    ORDER BYを使用する時の注意

    ORDER BYを使用してソートする時に、 ROWNUM擬似列を使うときは注意が必要です。
    冒頭で述べたとおり、 ROWNUM擬似列は、「ソート前のレコード番号を表す擬似列」です。

    つまり、 ROWNUMで連番を振った後にORDER BYのソートが行われるため、以下のように ROWNUMの値が連番ではなくなります。

    レコードの取得順に ROWNUM擬似列に連番が振られ、その後に「Age」列のソートが行われるため、 ROWNUMの値が1〜の連番になりません。

    ポテパンダの一言メモ

    ソート前のレコードの取得順は、Oracleのオプティマイザに依存します。場合によっては、上のようなSQLでも ROWNUM擬似列の値がうまく連番になるかもしれません。ただし、それは偶然で、ORDER BYを使うとROWNUM`の値は連番にならないと認識しておきましょう。

    ROWNUMは速度が早い?

    ROWNUMは、SQLの結果セットに暗黙的に追加される仮想列です。では ROWNUMをWHERE句に指定すると、検索スピードはどうなるでしょうか?

    一般的に、データ量が多いテーブルに対して検索処理を高速化する場合、検索するキーに対して索引を追加しますが、 ROWNUMには索引がないため、WHERE句に指定しても検索スピードが早くなることはありません。

    ただし、 ROWNUMをWHERE句に指定すると、読み込むレコード数が絞り込まれるため、「ディスク読み込み」や「ネットワーク転送量」などI/Oの部分で低減が見込まれるため、結果的にSQLの実行時間が早くなることが多いです。

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

    ROWNUM擬似列は、SQLの結果セット全体に対して連番を振るため、例えば、ユーザーの地域毎に連番を振るなど、グループ単位に連番を振ることができません。

    代わりにROW_NUMBERを使う

    グループ単位に連番を振る時は、 ROW_NUMBER関数を使用します。 ROW_NUMBER関数を使うことで、グループ単位に連番を振ったり、ソート条件を指定して連番を振ったりできます。

    以下のSQLは、ユーザーの地域(City)毎に、年齢(Age)の昇順に ROW_NUMBER関数で連番を振る例です。

    ROW_NUMBER以外にも、 RANK関数、 DENSE_RANK関数で、順位付けした連番を振ることもできます。

    【関連記事】
    【SQL】データを順位づけするRANK関数の使い方を解説

    ROWNUMはOracleでしか使えないので注意

    ROWNUM擬似列で連番を取得する方法を解説してきました。
    ROWNUMはOracleにしかない機能であるため、MySQL、SQL Serverなどのデータベースでは使用できません。
    将来的にOracle以外を使用する予定がある場合は、 ROW_NUMBER関数を使用しましょう。



    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE