分析関数でROW_NUMBERを使用するサンプルSQLを紹介します。
分析関数 ROW_NUMBER() サンプルSQL
前提条件
SELECT
EMP_ID AS 従業員ID
, DEPT_ID AS 部署ID
, AGE AS 年齢
, SALARY AS 年収
FROM
TB_EMP
;
従業員ID 部署ID 年齢 年収
---------- -------- ------ ----------
1 01 25 3000000
2 01 30 4500000
3 02 35 4800000
4 02 36 5000000
5 02 37 5000000
5行が選択されました。
部署毎で年収のランキング表示を行う
SELECT
EMP_ID AS 従業員ID
, DEPT_ID AS 組織ID
, SALARY AS 年収
, ROW_NUMBER() OVER(PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RANKING
FROM
TB_EMP
;
従業員ID 組織ID 年収 RANKING
---------- -------- ---------- ----------
2 01 4500000 1
1 01 3000000 2
5 02 5000000 1
4 02 5000000 2
3 02 4800000 3
5行が選択されました。
『ROW_NUMBER() OVER(PARTITION BY DEPT_ID ORDER BY SALARY DESC)』とすることで、部署ID毎、給与の降順でランキング結果を抽出できます。
ここで注意点ですが、従業員IDの5と4の年収は同じですが、RANKINGは同じではなく連番になっています。
『ROW_NUMBER()』を使った場合、同じ年収が存在しても、一意な数値が返却されます。
『RANK()』を使った場合は、同じ年収が存在すると同じ数値が返却されますが、ROW_NUMBER()の場合は一意な数値が返却されます。
分析関数を使って必ず1件だけ抽出したい場合は『ROW_NUMBER()』を使う必要があります。
この違いを意識せずに『RANK()』を使っていると事故(バグ)の原因になりますので要注意です。
部署毎で年収が1位の従業員を1件だけ抽出する
SELECT
VW.EMP_ID AS 従業員ID
, VW.DEPT_ID AS 組織ID
, VW.SALARY AS 年収
, VW.RNK AS RANKING
FROM
(
SELECT
EMP_ID AS EMP_ID
, DEPT_ID AS DEPT_ID
, SALARY AS SALARY
, ROW_NUMBER() OVER(PARTITION BY DEPT_ID ORDER BY SALARY DESC, EMP_ID ASC) AS RNK
FROM
TB_EMP
) VW
WHERE
VW.RNK = 1
;
従業員ID 組織ID 年収 RANKING
---------- -------- ---------- ----------
2 01 4500000 1
4 02 5000000 1
副問合せを使ってROW_NUMBER()で付与されたランキング結果を抽出条件にすることで、部署毎で年収が一番高い従業員IDを抽出することができます。
従業員IDの4と5は同一の年収ですが、「EMP_ID ASC」とすることで、年収が同じでも従業員IDが小さい方のみが抽出されるようにしています。
私の経験上、必ず1件だけ抽出したいという場合はよくありますので、ROW_NUMBER()は覚えておいた方がよいです。