Oracleの擬似列rownumは、selectした結果(ソート前)の各行に連番を付与してくれるもの。
似たものにrow_number関数がある。これはソート後に連番を付与してくれる。
|
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関数を使う。
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のままであり、条件が満たされることは永久に無い)
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行が選択されました。
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から始まることになる。