[ Oracle ] 行番号を取得する ( ROW_NUMBER 関数 )

Pocket

Oracle の ROW_NUMBER 関数で行番号を取得することができます。ここでは、以下の 従業員情報を格納した emp テーブルを例に ROW_NUMBER 関数の使用方法を確認します。

スポンサーリンク

SQL> select * from emp;

ID  DEPT              SAL
--- ---------- ----------
001 総務部             30
002 総務部             35
003 総務部             35
004 総務部             40
005 技術部             33
006 技術部             38

ROW_NUMBER 関数の使用例

全社員を給料の高い順に並べた結果に行番号を付けて取得しています。

SQL> select id, dept, sal, row_number() over (order by sal desc) from emp;

ID  DEPT              SAL ROW_NUMBER()OVER(ORDERBYSALDESC)
--- ---------- ---------- --------------------------------
004 総務部             40                                1
006 技術部             38                                2
002 総務部             35                                3
003 総務部             35                                4
005 技術部             33                                5
001 総務部             30                                6

ROW_NUMBER は ユニークに割り当てられ、同一条件(給料が同じ)場合でも異なる値となります。すいません、当たり前ですね。

次に、部門別に給料の高い順に並べた結果に行番号を付けて取得しています。先ほどのサンプルとは部門ごとに行番号を割り当てているという違いがあります。

SQL> select id, dept, sal, row_number() over (partition by dept order by sal desc) from emp;

ID  DEPT              SAL ROW_NUMBER()OVER(PARTITIONBYDEPTORDERBYSALDESC)
--- ---------- ---------- -----------------------------------------------
006 技術部             38                                               1
005 技術部             33                                               2
004 総務部             40                                               1
002 総務部             35                                               2
003 総務部             35                                               3
001 総務部             30                                               4

部門別のようにグループ単位で行番号を割り当てるために、partition by によってグループ化するカラムを指定しています。

ROW_NUMBER 関数では、行番号はユニークに割り当てられますが、同一条件で同じ数字を割り当てるには rank 関数を使用します。

SQL> select id, dept, sal, rank() over (partition by dept order by sal desc) from emp;

ID  DEPT              SAL RANK()OVER(PARTITIONBYDEPTORDERBYSALDESC)
--- ---------- ---------- -----------------------------------------
006 技術部             38                                         1
005 技術部             33                                         2
004 総務部             40                                         1
002 総務部             35                                         2
003 総務部             35                                         2
001 総務部             30                                         4

出力結果から rank 関数は、ランキング2 が 2つ存在するために、ランキング3 をスキップし、ランキング4 を出力していることがわかります。このとき、rank 関数ではなく、DENSE_RANK 関数を使用するとランキング3 がスキップされなくなります。

SQL> select id, dept, sal, dense_rank() over (order by sal desc) from emp;

ID  DEPT              SAL DENSE_RANK()OVER(ORDERBYSALDESC)
--- ---------- ---------- --------------------------------
004 総務部             40                                1
006 技術部             38                                2
002 総務部             35                                3
003 総務部             35                                3
005 技術部             33                                4
001 総務部             30                                5
参考
スポンサーリンク


Pocket

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>