S-JIS[2007-07-14/2008-01-27] 変更履歴

rownum擬似列

Oracleの擬似列rownumは、selectした結果(ソート前)の各行に連番を付与してくれるもの。
似たものにrow_number関数がある。これはソート後に連番を付与してくれる。


rownum使用例

SQL> select rownum,empno,ename from emp;

    ROWNUM      EMPNO ENAME
---------- ---------- ----------
         1       7369 SMITH
         2       7499 ALLEN
         3       7521 WARD
         4       7566 JONES
         5       7654 MARTIN
         6       7698 BLAKE
         7       7782 CLARK
         8       7788 SCOTT
         9       7839 KING
        10       7844 TURNER
        11       7876 ADAMS
        12       7900 JAMES
        13       7902 FORD
        14       7934 MILLER
        
14行が選択されました。


selectした各行に順番に振られる番号なので、select条件が違えば番号も異なってくる。

SQL> select rownum,empno,ename from emp
  2  where ename like'%A%';

    ROWNUM      EMPNO ENAME
---------- ---------- ----------
         1       7499 ALLEN
         2       7521 WARD
         3       7654 MARTIN
         4       7698 BLAKE
         5       7782 CLARK
         6       7876 ADAMS
         7       7900 JAMES

7行が選択されました。


rownumの番号はソート前に採番される。

SQL> select rownum,empno,ename from emp
  2  where ename like'%A%'
  3  order by ename;

    ROWNUM      EMPNO ENAME
---------- ---------- ----------
         6       7876 ADAMS
         1       7499 ALLEN
         4       7698 BLAKE
         5       7782 CLARK
         7       7900 JAMES
         3       7654 MARTIN
         2       7521 WARD

7行が選択されました。

ソート後の番号にしたい場合は、副問い合わせとして一回「ソート有りのselect」をしてからrownumを使って採番するか、row_number関数を使う。


Top n

rownum擬似列をwhere条件で使うと、(ソート前の)select結果の先頭n件を取得することが出来る。

SQL> select rownum,empno,ename from emp
  2  where rownum <= 10;

    ROWNUM      EMPNO ENAME
---------- ---------- ----------
         1       7369 SMITH
         2       7499 ALLEN
         3       7521 WARD
         4       7566 JONES
         5       7654 MARTIN
         6       7698 BLAKE
         7       7782 CLARK
         8       7788 SCOTT
         9       7839 KING
        10       7844 TURNER

10行が選択されました。

where条件でrownumを使って上限を指定した場合、その個数を超えるとそれ以上テーブルの探索はされないようだ。
(オプティマイザーがそういう判断をしてくれるらしい)
すなわち、大量のレコードが入っているテーブルから限られた件数だけ取得する目的にも使える。

1件だけ取り出したい場合は「where rownum <= 1」あるいは「where rownum < 2」とすればよい。
どちらで書いてもOracle側としては(実行時には)大差ないだろうが、「1件取得」であれば「1」という数がSQL上に現れる方が人間が見た際に素直で分かり易いと思う。


本当に上限で探索が打ち切られるのか、ちょっと試してみた。

与えられた数字をデバッグ出力する関数:

SQL> create function put_number(dt in number) return number
  2 is
  3 begin
  4   dbms_output.put_line(dt);
  5   return dt;
  6 end;
  7 /

ファンクションが作成されました。

SQL> set serveroutput on
SQL> select rownum,empno,ename from emp
  2  where rownum <= 10 and put_number(rownum) >= 0;		←条件の書き順(andの前後)を入れ替えてみても、結果は同じだった

    ROWNUM      EMPNO ENAME
---------- ---------- ----------
         1       7369 SMITH
         2       7499 ALLEN
         3       7521 WARD
         4       7566 JONES
         5       7654 MARTIN
         6       7698 BLAKE
         7       7782 CLARK
         8       7788 SCOTT
         9       7839 KING
        10       7844 TURNER

10行が選択されました。

SQL> exec dbms_output.new_line();
1
2
3
4
5
6
7
8
9
10	←ちゃんと、put_numberが10回しか呼ばれていない

PL/SQLプロシージャが正常に完了しました。

where句にrownumを使わないと以下のようになり、全データが読まれているのが分かる。

SQL> select rownum,empno,ename from emp
  2  where put_number(rownum) <= 10;

    ROWNUM      EMPNO ENAME
---------- ---------- ----------
         1       7369 SMITH
         2       7499 ALLEN
         3       7521 WARD
         4       7566 JONES
         5       7654 MARTIN
         6       7698 BLAKE
         7       7782 CLARK
         8       7788 SCOTT
         9       7839 KING
        10       7844 TURNER

10行が選択されました。

SQL> exec dbms_output.new_line();
1
2
3
4
5
6
7
8
9
10
11
11
11
11	←put_numberが14回(テーブルの全データに対して)呼ばれている

PL/SQLプロシージャが正常に完了しました。

where句で使う関数からどんな値が返ってくるかはオプティマイザーには分からない為、各レコード毎に関数を呼び出すしかない。 したがってテーブルの全検索になってしまう。

出力してみたrownumの値は面白いことになっている。11で止まっているのは、この例では最終的にwhere条件を満たす件数が10件なので、それ以上インクリメントされないのだろう。
つまり、where条件を満たして選択結果とならない限り、rownumは増えない

ということは、「先頭m件を除いた」という使い方は出来ないということ。
(「where rownum >= 5」では、1件目のときはrownumが1なので条件外。したがってrownumは増えない。だから2件目のときもrownumは1のまま。先に進んで5件目になろうと10件目になろうとrownumは1のままであり、条件が満たされることは永久に無い)


row_number関数

rownum擬似列と異なり、row_number関数はソートしてから番号を採番する。
overを付けて、その中にソート条件を指定する。(select文全体でのorder by句は不要なようだ)
(row_number()にoverを付けないと、「ORA-30484: このファンクションのウィンドウ指定が欠落しています」というエラーが発生する)

SQL> select row_number() over(order by ENAME) rn, rownum,EMPNO,ENAME from EMP
  2  where ename like'%A%';

        RN     ROWNUM      EMPNO ENAME
---------- ---------- ---------- ----------
         1          6       7876 ADAMS
         2          1       7499 ALLEN
         3          4       7698 BLAKE
         4          5       7782 CLARK
         5          7       7900 JAMES
         6          3       7654 MARTIN
         7          2       7521 WARD

7行が選択されました。

キー毎のTop n

row_number関数を使うと、グループ毎に先頭n件を抽出することが出来る。[2008-01-19]

--DEPTNO毎にSALの上位3件を表示するSQL
select * from
(
	select DEPTNO,ENAME,SAL,
		row_number() over(partition by DEPTNO order by SAL desc) rn
	from EMP
)
where rn <= 3
order by DEPTNO,rn
;
    DEPTNO ENAME             SAL         RN
---------- ---------- ---------- ----------
        10 KING             5020          1
        10 CLARK            2470          2
        10 MILLER           1320          3
        20 SCOTT            3020          1
        20 FORD             3020          2
        20 JONES            2995          3
        30 BLAKE            2870          1
        30 ALLEN            1620          2
        30 TURNER           1520          3

9行が選択されました。

ちょうどこの例では、DEPTNO=20のSCOTTとFORDのSALが同じで、たまたまSCOTTの方が先に表示されている。
partition byやorder byにはカンマ区切りで複数の項目を指定することが出来るので、他の条件を加えることも可能。

	row_number() over(partition by 項目,項目,… order by 項目 ASC,項目 DESC,…)

どーでもいいが、KINGは給料高いな(笑)


row_number()の値をwhere条件で比較する数値を1にすれば、条件内のレコードを1つずつ取得することになる。
つまり分析関数max()を使って最大値を持つ1レコードを取得する例と同じ事が出来る。

select * from
(
  select
    KEY,
    DATA1, DATA2, …,
    TARGET,
    row_number() over(partition by KEY order by TARGET desc) rn
  from TABLE
)
where rn = 1
;

max()を使う方法では最大値が複数一致する場合があると複数レコード取れてしまうが、こちらは必ず1レコードになる。

どちらの方が効率がいいかは…どうなんだろう? 小さなテーブルだと見ても大した差は無かった。


テストデータ作成

ROWNUMはレコード毎に変わる番号として使えるので、(規則的でよければ)大量のデータを作成するのに利用できる。[2008-01-27]

insert into テーブル
select
 ROWNUM, --連番となるキーとか
 '値',
 NULL,
 〜
from all_objects
where ROWNUM <= 50 --50件作成
;

SELECT-INSERT構文を使う際、SELECT元のテーブルに適当なテーブルを指定する。
これは、作成したい件数より多いテーブルであれば何でもいい。all_objectsなら2万件ちょっとある。
それより多い件数のデータを作りたい場合は「from all_objects, all_objects」という様にカンマ区切りでテーブルを追加する。
こうするとSELECT元が指定したテーブルの直積となるので、all_objectsが2つなら2万×2万=4億件まで作成できる。そんな件数になるテーブルは扱いたくないけどねー(苦笑)


例えば既存データの続きから指定番号までのデータを作りたかったら、ROWNUMに演算を施せばよい。
何度も使えるように 汎用的に作っておきたいなら、SQL*Plusのバインド変数(variable,「:」)置換変数(define,「&」)を利用するといいかも。

SQL> def end=200		←この番号まで作成する
SQL> var last number	←前回の最大値の保持用
SQL> --現在の最大値を取得
SQL> exec select max(KEY) into :last from TEST_MANY

PL/SQLプロシージャが正常に完了しました。

SQL> print last

      LAST
----------
        50

select 項目 into :バインド変数」(バインド変数への値の代入)はPL/SQLでないと使えない。したがってexecuteコマンドSQLを実行する。
(executeコマンドなので、複数行にわたる長いSQL文を書きたい場合は各行の末尾に「-」を付ける必要がある)
 

SQL> --データ作成
SQL> insert into TEST_MANY
  2  (KEY) --selectにより設定する項目を列挙
  3  select
  4    ROWNUM + :last
  5  from all_objects
  6  where
  7    ROWMUM + :last <= &end
  8  ;
旧   7:  ROWMUM + :last <= &end
新   7:  ROWMUM + :last <= 200

150行が作成されました。

SQL> select * from TEST_MANY;

       KEY DATA
---------- ----------
         1
         2
         3
〜
       199
       200

200行が選択されました。

ROWNUMは毎回1から始まるので、lastが50なら「ROWNUM + :last」は51から始まることになる。


Oracle関数へ戻る / SQLメモへ戻る / Oracle目次へ戻る / 技術メモへ戻る
メールの送信先:ひしだま