SQL ROW_NUMBER 関数

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

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...])

ORDER BY

指定した列で並び替える。

給料が高い順に行番号をつける例を以下に示す。

SQL> SELECT ROW_NUMBER() OVER(ORDER BY sal DESC) AS rn, sal, deptno, ename FROM emp;

RN SAL DEPTNO ENAME
-- --- ------ ----------
 1 200      1 井上和
 2 190      2 一ノ瀬美空
 3 180      1 菅原咲月
 4 170      2 小川彩
 5 160      1 冨里奈央
 6 150      2 奥田いろは

PARTITION BY

指定した列でグルーピングする。

部署毎にグルーピングして、給料が高い順に行番号をつける例を以下に示す。

SQL> SELECT ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) AS rn, sal, deptno, ename FROM emp;

RN SAL DEPTNO ENAME
-- --- ------ ----------
1 200      1 井上和
2 180      1 菅原咲月
3 160      1 冨里奈央
1 190      2 一ノ瀬美空
2 170      2 小川彩
3 150      2 奥田いろは

上位10件

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

同じ値

同じ値があったとき、ROW_NUMBER 関数はそれぞれ異なる行番号をつける。

同じ値の扱いは、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

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 関数を使うことができる。

SQL Sever

SQL Server でも ROW_NUMBER 関数を使うことができる。

参考文献

Microsoft (2022) ROW_NUMBER (Transact-SQL) - SQL Server

Amazon Web Services, Inc. (2022) ROW_NUMBER ウィンドウ関数 - Amazon Redshift