対応バージョン
Oracle Database 11.2-12.1
更新履歴
| 日付 | 更新概要 |
|---|---|
| 2016/06/10 | 記事公開 |
| 2016/06/15 | タイトルを変更、DBA_USERSを順番に並べる例を追加 |
列データの大きい、または小さい順の最初の数件を出力する場合に使用できる関数について説明します。
データの順番に番号の付与が可能な関数を使用することで、指定した件数に絞る事が出来ます。
以下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
順番を付与する関数
ランク付けを行うことが可能な関数として、主に以下のような関数があります。
| 関数 | 説明 |
|---|---|
| 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
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
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
.....
副問い合わせ内で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
