指定列のトップn件を表示する(RANK,ROW_NUMBER関数)

SQL ツール

対応バージョン

Oracle Database 11.2-12.1

2016-06-15タイトルを変更、DBA_USERSを順番に並べる例を追加

2016-06-10記事公開

列データの大きい、または小さい順の最初の数件を出力する場合に使用できる関数について説明します。
データの順番に番号の付与が可能な関数を使用することで、指定した件数に絞る事が出来ます。

以下DBA_USERSを並び替えた例です。Sから始まるユーザ名をACCOUNT_STATUS毎に、USERNAMEのアルファベットが若い順の番号を付与しています。
ACCOUNT_STATUS毎に上位5件を出力しています。

SQL> set pages 1000 line 120 trim on
SQL> column USERNAME format a25
SQL> select *
  2    from (
  3      select USERNAME
  4           , ACCOUNT_STATUS
  5           -- ACCOUNT_STATUS 毎に、 USERNAME 順の番号を付与。別名として RANK を指定。
  6           , rank() over(partition by ACCOUNT_STATUS order by USERNAME) as RANK
  7        from DBA_USERS
  8       where USERNAME like 'S%'
  9       order by ACCOUNT_STATUS
 10           , USERNAME
 11    )
 12  -- 上位5件を出力
 13  where RANK <= 5
 14  order by 1,2;

USERNAME                  ACCOUNT_STATUS                         RANK
------------------------- -------------------------------- ----------
SCOTT                     EXPIRED & LOCKED                          1
SH                        EXPIRED & LOCKED                          2
SI_INFORMTN_SCHEMA        EXPIRED & LOCKED                          3
SPATIAL_CSW_ADMIN_USR     EXPIRED & LOCKED                          4
SPATIAL_WFS_ADMIN_USR     EXPIRED & LOCKED                          5
SYS                       OPEN                                      1
SYSMAN                    OPEN                                      2
SYSMAN_APM                OPEN                                      3
SYSMAN_BIPLATFORM         OPEN                                      4
SYSMAN_MDS                OPEN                                      5

SQL> set pages 1000 line 120 trim on

SQL> column USERNAME format a25

SQL> select *

2 from (

3 select USERNAME

4 , ACCOUNT_STATUS

5 -- ACCOUNT_STATUS 毎に、 USERNAME 順の番号を付与。別名として RANK を指定。

6 , rank() over(partition by ACCOUNT_STATUS order by USERNAME) as RANK

7 from DBA_USERS

8 where USERNAME like 'S%'

9 order by ACCOUNT_STATUS

10 , USERNAME

11 )

12 -- 上位5件を出力

13 where RANK <= 5

14 order by 1,2;

USERNAME ACCOUNT_STATUS RANK

------------------------- -------------------------------- ----------

SCOTT EXPIRED & LOCKED 1

SH EXPIRED & LOCKED 2

SI_INFORMTN_SCHEMA EXPIRED & LOCKED 3

SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED 4

SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED 5

SYS OPEN 1

SYSMAN OPEN 2

SYSMAN_APM OPEN 3

SYSMAN_BIPLATFORM OPEN 4

SYSMAN_MDS OPEN 5

順番を付与する関数

ランク付けを行うことが可能な関数として、主に以下のような関数があります。

関数	説明
RANK	順位をNUMBER型の番号で戻します。同順があった場合に次の順位を飛ばします。（1位、1位、3位、4位と続きます）
DENSE_RANK	順位をNUMBER型の番号で戻します。同順があった場合に次の順位を飛ばしません。（1位、1位、2位、3位と続きます）
ROW_NUMBER	順番をNUMBER型の番号で戻します。同じ値があった場合でも、別の番号を振ります。（1位が同じ値だったとしても1位、2位、3位、4位と続きます）

参考情報（参考元マニュアル）
RANK関数について
https://docs.oracle.com/cd/E16338_01/server.112/b56299/functions141.htm
DENSE_RANK関数について
https://docs.oracle.com/cd/E16338_01/server.112/b56299/functions052.htm
ROW_NUMBER関数について
https://docs.oracle.com/cd/E16338_01/server.112/b56299/functions156.htm
Oracle Database SQL言語リファレンス
11gリリース2 (11.2)
B56299-07

関数の使い方（ランク付け部分のみ）

各関数の構文にて以下の2つを指定します。

関数	説明
PARTITION BY	指定した列でグループ化をします。グループ化した中で1位から順に番号を振ります。
ORDER BY	指定した列でソートを行います。ソートされた順で順位付けを行います。DESCを指定することで逆順にランク付けもできます。

RANK関数を使用した場合のサンプル

ASH（アクティブセッション履歴）の特定の期間のデータをランク付けして出力するサンプルです。

-- INST_ID （インスタンスID）ごとにランキングする ※ PARTITION BY b.INST_ID
-- COUNT(1) （サンプリングされたセッションの数）が大きい順にランキングする ※ order by COUNT(1) desc
SQL> set line 150 trim on pages 10000
SQL> column DB_NAME for a10
SQL> column WAIT_CLASS for a18
SQL> column EVENT_NAME for a40
SQL> 
SQL> select *
  2   from (
  3     select SYS_CONTEXT('USERENV','DB_NAME') as DB_NAME
  4          , b.INST_ID
  5          , rank() over(partition by b.INST_ID order by COUNT(1) desc) as RANK -- データのランク付け
  6          , COUNT(1) as SESSIONS -- COUNT(1)にてサンプリングされたセッション数をカウント
  7          , a.WAIT_CLASS
  8          , a.NAME as EVENT_NAME
  9          , SUM(b.TIME_WAITED) as TIME_WAITED_SUM
 10       from V$EVENT_NAME a
 11          , GV$ACTIVE_SESSION_HISTORY b
 12      where a.EVENT_ID = b.EVENT_ID
 13        and a.EVENT# = b.EVENT#
 14        and b.SESSION_TYPE = 'FOREGROUND'
 15        and b.SESSION_STATE = 'WAITING'
 16        and b.SAMPLE_TIME between to_date('2016/06/10 13:50', 'yyyy/mm/dd hh24:mi') and to_date('2016/06/10 14:00', 'yyyy/mm/dd hh24:mi')
 17      group by a.NAME
 18          , a.WAIT_CLASS
 19          , b.INST_ID
 20   )
 21 order by INST_ID
 22     , RANK
 23 ;

DB_NAME       INST_ID       RANK   SESSIONS WAIT_CLASS         EVENT_NAME                               TIME_WAITED_SUM
---------- ---------- ---------- ---------- ------------------ ---------------------------------------- ---------------
orcl                1          1          6 Commit             log file sync                                    1852502
orcl                1          1          6 User I/O           db file sequential read                            60192
orcl                1          3          5 Scheduler          resmgr:cpu quantum                               1459718
orcl                1          4          2 Other              null event                                             0
orcl                1          5          1 Concurrency        library cache lock                                  3299

-- INST_ID （インスタンスID）ごとにランキングする ※ PARTITION BY b.INST_ID

-- COUNT(1) （サンプリングされたセッションの数）が大きい順にランキングする ※ order by COUNT(1) desc

SQL> set line 150 trim on pages 10000

SQL> column DB_NAME for a10

SQL> column WAIT_CLASS for a18

SQL> column EVENT_NAME for a40

SQL>

SQL> select *

2 from (

3 select SYS_CONTEXT('USERENV','DB_NAME') as DB_NAME

4 , b.INST_ID

5 , rank() over(partition by b.INST_ID order by COUNT(1) desc) as RANK -- データのランク付け

6 , COUNT(1) as SESSIONS -- COUNT(1)にてサンプリングされたセッション数をカウント

7 , a.WAIT_CLASS

8 , a.NAME as EVENT_NAME

9 , SUM(b.TIME_WAITED) as TIME_WAITED_SUM

10 from V$EVENT_NAME a

11 , GV$ACTIVE_SESSION_HISTORY b

12 where a.EVENT_ID = b.EVENT_ID

13 and a.EVENT# = b.EVENT#

14 and b.SESSION_TYPE = 'FOREGROUND'

15 and b.SESSION_STATE = 'WAITING'

16 and b.SAMPLE_TIME between to_date('2016/06/10 13:50', 'yyyy/mm/dd hh24:mi') and to_date('2016/06/10 14:00', 'yyyy/mm/dd hh24:mi')

17 group by a.NAME

18 , a.WAIT_CLASS

19 , b.INST_ID

20 )

21 order by INST_ID

22 , RANK

23 ;

DB_NAME INST_ID RANK SESSIONS WAIT_CLASS EVENT_NAME TIME_WAITED_SUM

---------- ---------- ---------- ---------- ------------------ ---------------------------------------- ---------------

orcl 1 1 6 Commit log file sync 1852502

orcl 1 1 6 User I/O db file sequential read 60192

orcl 1 3 5 Scheduler resmgr:cpu quantum 1459718

orcl 1 4 2 Other null event 0

orcl 1 5 1 Concurrency library cache lock 3299

RANK列が1位のものが2つあり、1位の次が3位となっていることが確認できます。

DENSE_RANK関数を使用した場合のサンプル

RANKをDENSE_RANKに変更して実行したサンプルです。

-- INST_ID （インスタンスID）ごとにランキングする ※ PARTITION BY b.INST_ID
-- COUNT(1) （サンプリングされたセッションの数）が大きい順にランキングする ※ order by COUNT(1) desc
SQL> set line 150 trim on pages 10000
SQL> column DB_NAME for a10
SQL> column WAIT_CLASS for a18
SQL> column EVENT_NAME for a40
SQL> 
SQL> select *
  2   from (
  3     select SYS_CONTEXT('USERENV','DB_NAME') as DB_NAME
  4          , b.INST_ID
  5          , dense_rank() over(partition by b.INST_ID order by COUNT(1) desc) as RANK
  6          , COUNT(1) as SESSIONS
  7          , a.WAIT_CLASS
  8          , a.NAME as EVENT_NAME
  9          , SUM(b.TIME_WAITED) as TIME_WAITED_SUM
 10       from V$EVENT_NAME a
 11          , GV$ACTIVE_SESSION_HISTORY b
 12      where a.EVENT_ID = b.EVENT_ID
 13        and a.EVENT# = b.EVENT#
 14        and b.SESSION_TYPE = 'FOREGROUND'
 15        and b.SESSION_STATE = 'WAITING'
 16        and b.SAMPLE_TIME between to_date('2016/06/10 13:50', 'yyyy/mm/dd hh24:mi') and to_date('2016/06/10 14:00', 'yyyy/mm/dd hh24:mi')
 17      group by a.NAME
 18          , a.WAIT_CLASS
 19          , b.INST_ID
 20   )
 21 order by INST_ID
 22     , RANK
 23 ;

DB_NAME       INST_ID       RANK   SESSIONS WAIT_CLASS         EVENT_NAME                               TIME_WAITED_SUM
---------- ---------- ---------- ---------- ------------------ ---------------------------------------- ---------------
orcl                1          1          6 Commit             log file sync                                    1852502
orcl                1          1          6 User I/O           db file sequential read                            60192
orcl                1          2          5 Scheduler          resmgr:cpu quantum                               1459718
orcl                1          3          2 Other              null event                                             0
orcl                1          4          1 Concurrency        library cache lock                                  3299

-- INST_ID （インスタンスID）ごとにランキングする ※ PARTITION BY b.INST_ID

-- COUNT(1) （サンプリングされたセッションの数）が大きい順にランキングする ※ order by COUNT(1) desc

SQL> set line 150 trim on pages 10000

SQL> column DB_NAME for a10

SQL> column WAIT_CLASS for a18

SQL> column EVENT_NAME for a40

SQL>

SQL> select *

2 from (

3 select SYS_CONTEXT('USERENV','DB_NAME') as DB_NAME

4 , b.INST_ID

5 , dense_rank() over(partition by b.INST_ID order by COUNT(1) desc) as RANK

6 , COUNT(1) as SESSIONS

7 , a.WAIT_CLASS

8 , a.NAME as EVENT_NAME

9 , SUM(b.TIME_WAITED) as TIME_WAITED_SUM

10 from V$EVENT_NAME a

11 , GV$ACTIVE_SESSION_HISTORY b

12 where a.EVENT_ID = b.EVENT_ID

13 and a.EVENT# = b.EVENT#

14 and b.SESSION_TYPE = 'FOREGROUND'

15 and b.SESSION_STATE = 'WAITING'

16 and b.SAMPLE_TIME between to_date('2016/06/10 13:50', 'yyyy/mm/dd hh24:mi') and to_date('2016/06/10 14:00', 'yyyy/mm/dd hh24:mi')

17 group by a.NAME

18 , a.WAIT_CLASS

19 , b.INST_ID

20 )

21 order by INST_ID

22 , RANK

23 ;

DB_NAME INST_ID RANK SESSIONS WAIT_CLASS EVENT_NAME TIME_WAITED_SUM

---------- ---------- ---------- ---------- ------------------ ---------------------------------------- ---------------

orcl 1 1 6 Commit log file sync 1852502

orcl 1 1 6 User I/O db file sequential read 60192

orcl 1 2 5 Scheduler resmgr:cpu quantum 1459718

orcl 1 3 2 Other null event 0

orcl 1 4 1 Concurrency library cache lock 3299

RANK列が1位のものが2つあり、1位の次が2位となっていることが確認できます。

ROW_NUMBER関数を使用した場合のサンプル

ASHから10分毎の待機イベントの状況トップ3を出力するSQLのサンプルです。

-- group byの結果などに対しても適用できます。
-- rank <= 3とすることで、3位以上（1～3）を出力します。
SQL> set line 150 trim on pages 10000
SQL> column DB_NAME for a10
SQL> column WAIT_CLASS for a18
SQL> column EVENT_NAME for a40
SQL> 
SQL> select *
  2   from (
  3     select SYS_CONTEXT('USERENV','DB_NAME') as DB_NAME
  4          , b.INST_ID
  5          , to_char(b.SAMPLE_TIME,'yyyy/mm/dd hh24:') || to_char(trunc(to_number(to_char(b.SAMPLE_TIME,'mi')),-1),'FM00') as YYYYMMDDHH24MI
  6          , ROW_NUMBER() over(partition by b.INST_ID,to_char(b.SAMPLE_TIME,'yyyy/mm/dd hh24:') || to_char(trunc(to_number(to_char(b.SAMPLE_TIME,'mi')),-1),'FM00') order by SUM(b.TIME_WAITED) desc) as RANK
  7          , a.WAIT_CLASS
  8          , a.NAME as EVENT_NAME
  9          , SUM(b.TIME_WAITED) as TIME_WAITED_SUM
 10       from V$EVENT_NAME a
 11          , GV$ACTIVE_SESSION_HISTORY b
 12      where a.EVENT_ID = b.EVENT_ID
 13        and a.EVENT# = b.EVENT#
 14        and b.SESSION_TYPE = 'FOREGROUND'
 15        and b.SESSION_STATE = 'WAITING'
 16      group by a.NAME
 17          , a.WAIT_CLASS
 18          , b.INST_ID
 19          , to_char(b.SAMPLE_TIME,'yyyy/mm/dd hh24:') || to_char(trunc(to_number(to_char(b.SAMPLE_TIME,'mi')),-1),'FM00')
 20   )
 21 where RANK <= 3
 22 order by YYYYMMDDHH24MI
 23     , RANK
 24 ;

DB_NAME       INST_ID YYYYMMDDHH24MI     RANK WAIT_CLASS         EVENT_NAME                               TIME_WAITED_SUM
---------- ---------- ----------------- ----- ------------------ ---------------------------------------- ---------------
orcl                1 2016/06/10 13:50      1 Commit             log file sync                                    1852502
orcl                1 2016/06/10 13:50      2 Scheduler          resmgr:cpu quantum                               1459718
orcl                1 2016/06/10 13:50      3 User I/O           db file sequential read                            60192
orcl                1 2016/06/10 14:00      1 Scheduler          resmgr:cpu quantum                             104823843
orcl                1 2016/06/10 14:00      2 Concurrency        cursor: pin S wait on X                           899538
orcl                1 2016/06/10 14:00      3 User I/O           db file sequential read                           684176
orcl                1 2016/06/10 14:10      1 Commit             log file sync                                     487865
orcl                1 2016/06/10 14:10      2 Application        SQL*Net break/reset to client                        614
orcl                1 2016/06/10 14:20      1 User I/O           db file sequential read                           103840
orcl                1 2016/06/10 14:20      2 Other              ADR block file read                                99397
orcl                1 2016/06/10 14:20      3 Commit             log file sync                                      84852
orcl                1 2016/06/10 14:30      1 Commit             log file sync                                     232847
orcl                1 2016/06/10 14:30      2 User I/O           db file sequential read                             9910
orcl                1 2016/06/10 14:30      3 Concurrency        cursor: pin S wait on X                                0
.....

-- group byの結果などに対しても適用できます。

-- rank <= 3とすることで、3位以上（1～3）を出力します。

SQL> set line 150 trim on pages 10000

SQL> column DB_NAME for a10

SQL> column WAIT_CLASS for a18

SQL> column EVENT_NAME for a40

SQL>

SQL> select *

2 from (

3 select SYS_CONTEXT('USERENV','DB_NAME') as DB_NAME

4 , b.INST_ID

5 , to_char(b.SAMPLE_TIME,'yyyy/mm/dd hh24:') || to_char(trunc(to_number(to_char(b.SAMPLE_TIME,'mi')),-1),'FM00') as YYYYMMDDHH24MI

6 , ROW_NUMBER() over(partition by b.INST_ID,to_char(b.SAMPLE_TIME,'yyyy/mm/dd hh24:') || to_char(trunc(to_number(to_char(b.SAMPLE_TIME,'mi')),-1),'FM00') order by SUM(b.TIME_WAITED) desc) as RANK

7 , a.WAIT_CLASS

8 , a.NAME as EVENT_NAME

9 , SUM(b.TIME_WAITED) as TIME_WAITED_SUM

10 from V$EVENT_NAME a

11 , GV$ACTIVE_SESSION_HISTORY b

12 where a.EVENT_ID = b.EVENT_ID

13 and a.EVENT# = b.EVENT#

14 and b.SESSION_TYPE = 'FOREGROUND'

15 and b.SESSION_STATE = 'WAITING'

16 group by a.NAME

17 , a.WAIT_CLASS

18 , b.INST_ID

19 , to_char(b.SAMPLE_TIME,'yyyy/mm/dd hh24:') || to_char(trunc(to_number(to_char(b.SAMPLE_TIME,'mi')),-1),'FM00')

20 )

21 where RANK <= 3

22 order by YYYYMMDDHH24MI

23 , RANK

24 ;

DB_NAME INST_ID YYYYMMDDHH24MI RANK WAIT_CLASS EVENT_NAME TIME_WAITED_SUM

---------- ---------- ----------------- ----- ------------------ ---------------------------------------- ---------------

orcl 1 2016/06/10 13:50 1 Commit log file sync 1852502

orcl 1 2016/06/10 13:50 2 Scheduler resmgr:cpu quantum 1459718

orcl 1 2016/06/10 13:50 3 User I/O db file sequential read 60192

orcl 1 2016/06/10 14:00 1 Scheduler resmgr:cpu quantum 104823843

orcl 1 2016/06/10 14:00 2 Concurrency cursor: pin S wait on X 899538

orcl 1 2016/06/10 14:00 3 User I/O db file sequential read 684176

orcl 1 2016/06/10 14:10 1 Commit log file sync 487865

orcl 1 2016/06/10 14:10 2 Application SQL*Net break/reset to client 614

orcl 1 2016/06/10 14:20 1 User I/O db file sequential read 103840

orcl 1 2016/06/10 14:20 2 Other ADR block file read 99397

orcl 1 2016/06/10 14:20 3 Commit log file sync 84852

orcl 1 2016/06/10 14:30 1 Commit log file sync 232847

orcl 1 2016/06/10 14:30 2 User I/O db file sequential read 9910

orcl 1 2016/06/10 14:30 3 Concurrency cursor: pin S wait on X 0

.....

副問い合わせ内でorder byを使用する場合

昔ながらのやり方としては、副問い合わせの中で事前ソートを行い、ソートされた行の順番を保証する方法があります。上位n件以下の出力ということをする場合によく使われることが多いです。ただし、ROW_NUMBER関数で示したサンプルのように、グループ化をした中での順位出力が難しくなります。

副問い合わせ（from句の中のSQL）にてorder by句によるソートを行い、ソート内容の上位3番目までを取得するSQLのサンプルです。

SQL> set line 150 trim on pages 10000
SQL> column DB_NAME for a10
SQL> column WAIT_CLASS for a18
SQL> column EVENT_NAME for a40
SQL> 
SQL> select *
  2   from (
  3     select SYS_CONTEXT('USERENV','DB_NAME') as DB_NAME
  4          , b.INST_ID
  6          , COUNT(1) as SESSIONS -- COUNT(1)にてサンプリングされたセッション数をカウント
  7          , a.WAIT_CLASS
  8          , a.NAME as EVENT_NAME
  9          , SUM(b.TIME_WAITED) as TIME_WAITED_SUM
 10       from V$EVENT_NAME a
 11          , GV$ACTIVE_SESSION_HISTORY b
 12      where a.EVENT_ID = b.EVENT_ID
 13        and a.EVENT# = b.EVENT#
 14        and b.SESSION_TYPE = 'FOREGROUND'
 15        and b.SESSION_STATE = 'WAITING'
 16        and b.SAMPLE_TIME between to_date('2016/06/10 13:50', 'yyyy/mm/dd hh24:mi') and to_date('2016/06/10 14:00', 'yyyy/mm/dd hh24:mi')
 17      group by a.NAME
 18          , a.WAIT_CLASS
 19          , b.INST_ID
 20      order by b.INST_ID
 21          , COUNT(1) desc
 22   )
 23 where ROWNUM <= 3
 24 ;

DB_NAME       INST_ID   SESSIONS WAIT_CLASS         EVENT_NAME                               TIME_WAITED_SUM
---------- ---------- ---------- ------------------ ---------------------------------------- ---------------
orcl                1          6 User I/O           db file sequential read                            60192
orcl                1          6 Commit             log file sync                                    1852502
orcl                1          5 Scheduler          resmgr:cpu quantum                               1459718

SQL> set line 150 trim on pages 10000

SQL> column DB_NAME for a10

SQL> column WAIT_CLASS for a18

SQL> column EVENT_NAME for a40

SQL>

SQL> select *

2 from (

3 select SYS_CONTEXT('USERENV','DB_NAME') as DB_NAME

4 , b.INST_ID

6 , COUNT(1) as SESSIONS -- COUNT(1)にてサンプリングされたセッション数をカウント

7 , a.WAIT_CLASS

8 , a.NAME as EVENT_NAME

9 , SUM(b.TIME_WAITED) as TIME_WAITED_SUM

10 from V$EVENT_NAME a

11 , GV$ACTIVE_SESSION_HISTORY b

12 where a.EVENT_ID = b.EVENT_ID

13 and a.EVENT# = b.EVENT#

14 and b.SESSION_TYPE = 'FOREGROUND'

15 and b.SESSION_STATE = 'WAITING'

16 and b.SAMPLE_TIME between to_date('2016/06/10 13:50', 'yyyy/mm/dd hh24:mi') and to_date('2016/06/10 14:00', 'yyyy/mm/dd hh24:mi')

17 group by a.NAME

18 , a.WAIT_CLASS

19 , b.INST_ID

20 order by b.INST_ID

21 , COUNT(1) desc

22 )

23 where ROWNUM <= 3

24 ;

DB_NAME INST_ID SESSIONS WAIT_CLASS EVENT_NAME TIME_WAITED_SUM

---------- ---------- ---------- ------------------ ---------------------------------------- ---------------

orcl 1 6 User I/O db file sequential read 60192

orcl 1 6 Commit log file sync 1852502

orcl 1 5 Scheduler resmgr:cpu quantum 1459718

指定列のトップn件を表示する(RANK,ROW_NUMBER関数)

順番を付与する関数

関数の使い方（ランク付け部分のみ）

RANK関数を使用した場合のサンプル

DENSE_RANK関数を使用した場合のサンプル

ROW_NUMBER関数を使用した場合のサンプル

副問い合わせ内でorder byを使用する場合

関連記事