分析関数は、問い合わせで実行される演算の集合である。結合、WHERE句、GROUP BY句及びHAVING句を実行した後で分析関数が処理される。したがって、WHERE句、GROUP BY句及びHAVING句の中では分析関数は使えない。ORDER BY句の中では分析関数を使用できる。

ROW_NUMBER

OracleデータベースのROW_NUMBER関数は、検索結果レコードに1から始まる順番を付ける関数である。OVER関数と共に用いて、上位 n 位や下位 n 位のレコードを選択する場合に使われる。

構文

ROW_NUMBER() OVER(ORDER BY col [,col...])
ROW_NUMBER() OVER(PARTITION BY col [,col...] ORDER BY col [,col...])

ROW_NUMBER関数を使ったSQLのサンプルを示す。

SELECT ROW_NUMBER() OVER(ORDER BY sal), sal FROM emp

上位10件を表示する例を示す。

SELECT sal, ordr FROM(SELECT sal, RANK() OVER(ORDER BY sal) FROM emp) WHERE ordr <= 10

PostgreSQL

PostgreSQLでもROW_NUMBERを使うことができる。

SELECT ROW_NUMBER(), sql OVER(ORDER BY sal), sal FROM emp

MySQL

MySQLでROW_NUMBERは使えない。

RANK

RANKは順位を付けるSQL関数である。順位なので、同じ値には同じ順位が付けられる。同じ順位が複数あった場合、その後は順位が飛ばされる。

構文

RANK() OVER(ORDER BY col [,col...])
RANK() OVER(PARTITION BY col [,col...] ORDER BY col [,col...])

RANK関数を使ったSQLのサンプルを示す。

SELECT RANK() OVER(ORDER BY sal), sal FROM emp

次のSQLのように、WHERE句、GROUP BY句及びHAVING句の中でRANK関数を使うと、ORA-30483「ここでウィンドウ・ファンクションは使用できません。」のエラーが発生する。

SELECT RANK() OVER(ORDER BY sal), sal FROM emp WHERE RANK() OVER(ORDER BY sal) <= 10

DENSE_RANK

DENSE_RANKは順位を付けるSQL関数である。順位なので、同じ値には同じ順位が付けられる。同じ順位が複数あった場合でも、その後に順位は飛ばされない。

構文

DENSE_RANK() OVER(ORDER BY col [,col...])
DENSE_RANK() OVER(PARTITION BY col [,col...] ORDER BY col [,col...])

DENSE_RANK関数を使ったSQLのサンプルを示す。

SELECT DENSE_RANK() OVER(ORDER BY sal), sal FROM emp

ROW_NUMBER、RANK、DENSE_RANKの違い

SELECT sal,
       ROW_NUMBER() OVER(ORDER BY sal),
       RANK()       OVER(ORDER BY sal),
       DENSE_RANK() OVER(ORDER BY sal)
FROM emp
ROW_NUMBER、RANK、DENSE_RANKの違い
sal ROW_NUMBER RANK DENSE_RANK
800 1 1 1
950 2 2 2
1250 3 3 3
1250 4 3 3
1300 5 5 4
1500 6 6 5