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行が選択されました。
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行が選択されました。
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関数を使う。
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 serveroutputon
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のままであり、条件が満たされることは永久に無い︶
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行が選択されました。
--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は給料高いな(笑)
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レコードになる。
どちらの方が効率がいいかは…どうなんだろう？ 小さなテーブルだと見ても大した差は無かった。
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億件まで作成できる。そんな件数になるテーブルは扱いたくないけどねー(苦笑)
:﹂︶や置換変数︵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から始まることになる。