Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

ROW_NUMBER 分析ファンクションの使用例

ROW_NUMBER ファンクションを使用することで、上位 n件、下位 n件、中間順位 n件のレコードを抽出することができる。 (ROWNUM 擬似列は、ORDER BY より先に実行されるため、ROW_NUMBER と同じ処理をするにはインラインビューで抽出する。)
ROW_NUMBER の内部ソートは 非決定的 なファンクションであるため、複数回コールして分割取得するような用途として使用する場合には一意にソートされるように注意が必要。

ROW_NUMBER 分析関数を使用した簡単な例 （ORDER BY のみ）

NO 列で並び替えた後の 中間順位 5〜10 番を取得する。

 CREATE TABLE RIVUS.ROWNUM_TEST (
    NO                             NUMBER(3,0),
    NAME                           VARCHAR2(10)
 ) ;
 ...
 SELECT NO, NAME FROM (
     SELECT NO, NAME,
            ROW_NUMBER() OVER (ORDER BY NO) RNUM
     FROM ROWNUM_TEST
 ) WHERE RNUM BETWEEN 5 AND 10

ROW_NUMBER 関数 と PARTITION BY、ORDER BY の例

CATEGORY で GROUP BY した各グループセットを NO 列で並び替えた後で各カテゴリから 5〜10 番目を取得する。 (全カテゴリ数× 中間順位 5〜10 番を取得する)

 CREATE TABLE RIVUS.ROWNUM_TEST (
    CATEGORY                       NUMBER(3,0),
    NO                             NUMBER(3,0),
    NAME                           VARCHAR2(10)
 ) ;
 ...
 SELECT NO, NAME FROM (
     SELECT NO, NAME,
            ROW_NUMBER() OVER (PARTITION BY CATGORY ORDER BY NO) RNUM
     FROM ROWNUM_TEST
 ) WHERE RNUM BETWEEN 5 AND 10

　ROW_NUMBERファンクションにおいて 非決定的な例

ORDER BY は並び順が決定的になるように注意する。

テーブル定義 (USER_MASTER)と格納されているデータ

テーブル定義

テーブルデータ(作成日付の昇順)

非決定的な書き方 (非決定的のため、結果が不定)

非決定的になる可能性がある例　(作成日付昇順に 3件づつ取り出す場合での 2回目の取り出し例)

作成日が同一のレコードがある場合には、戻される結果セットが常に一定と限らない。 中間順位で抽出した場合、同じレコードが2度以上取り出されることがあることを意味する。
(レコードの取り出される順番は、統計データの内容、実行計画の内容によっても変動することがある)

SELECT USER_ID, USER_NAME, CREATED_ON, LINE_NO
FROM (
	SELECT
		 USER_ID
		,USER_NAME
		,CREATED_ON
		,ROW_NUMBER() OVER (ORDER BY CREATED_ON) LINE_NO
		                           --  ↑ レコードを一意に決定できない 
	FROM
		USER_MASTER
)
WHERE
	LINE_NO >= 4
	AND LINE_NO < 7
ORDER BY
	CREATED_ON, USER_ID

決定的な書き方

決定的になる例　(作成日付昇順,ユーザーID昇順に 3件づつ取り出す場合での 2回目の取り出し例))

作成日が同一のレコードがあった場合でも、ユーザーIDは 主キー であるため戻される結果セットが常に一定になる。(決定的)

SELECT USER_ID, USER_NAME, CREATED_ON, LINE_NO
FROM (
	SELECT
		 USER_ID
		,USER_NAME
		,CREATED_ON
		,ROW_NUMBER() OVER (ORDER BY CREATED_ON, USER_ID) LINE_NO
		                                     --  ↑ ここが違う
	FROM
		USER_MASTER
)
WHERE
	LINE_NO >= 4
	AND LINE_NO < 7
ORDER BY
	CREATED_ON, USER_ID

汎用的に ORDER BY に ROWID を入れるという方法もある。

---

ライセンス

分析関数を使用するには Oracle 8i エンタープライズ版、または、Oracle 9i スタンダード版以上が必要

サイト統合にともない代替情報の URL は不明

• Oracle 9i Technical Data 主要機能一覧 (oracle.co.jp)