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

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)と格納されているデータ

テーブル定義

カラム名項目名(日本語)型(サイズ)
USER_ID
 └ (主キー)
ユーザーIDVARCHAR(10)
USER_NAME名前VARCHAR(20)
CREATED_ON作成日DATE

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

No.*1USER_IDUSER_NAMECREATED_ON
1102緒方 辰夫2005/01/28
2157大久保 未来2005/01/29
3112鈴木 有里2005/01/29
4153大坪 暢子2005/01/29
5170三好 隆史2005/01/29
6120江藤 洋匡2005/02/01
7152堀内 空2005/02/03
8130氏家 東生2005/02/07
9122植木 虎之助2005/02/08
10190小林 遥2005/02/08
11171川端 若菜2005/02/13
12.........
(SI Object Browser 8 で作成した架空の名前リストです)

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

非決定的になる可能性がある例 (作成日付昇順に 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 は不明

分析関数 ROW_NUMBER の使用例の関連トピックス

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ オラクルサポートセンター

*1 便宜上見やすいように付けただけのもの、定義には存在しない