分析関数でRANKを使用するサンプルSQLを紹介します。
分析関数 RANK
部署毎で年収のランキング表示を行う
SELECT
EMP_ID AS 従業員ID
, DEPT_ID AS 部署ID
, SALARY AS 年収
FROM
TB_EMP
;
従業員ID 部署ID 年収
---------- -------- ----------
1 01 3000000
2 01 4500000
3 02 4800000
4 02 5000000
5 02 5000000
5行が選択されました。
部署毎で年収のランキング表示を行う
SELECT
EMP_ID AS 従業員ID
, DEPT_ID AS 組織ID
, SALARY AS 年収
, RANK() 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 1
3 02 4800000 3
5行が選択されました。
『RANK() OVER(PARTITION BY DEPT_ID ORDER BY SALARY DESC)』とすることで、部署ID毎、給与の降順でランキング結果を表示できます。
『RANK()』を使った場合、同じ年収が存在すれば同じランキング数字が返却されます。
そして、重複した分だけ連番は飛ばされます。従業員ID「3」の『RANKING』は2とならずに3となっていますね。
部署毎で年収が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
, RANK() OVER(PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RNK
FROM
TB_EMP
) VW
WHERE
VW.RNK = 1
;
従業員ID 組織ID 年収 RANKING
---------- -------- ---------- ----------
2 01 4500000 1
4 02 5000000 1
5 02 5000000 1
副問合せを使ってRANK()で付与されたランキング結果を抽出条件にすると、部署毎で、年収が一番高い従業員を抽出することができます。
従業員IDの4と5は同一の年収なので両方とも抽出されます。