分析関数 ROW

分析関数で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()は覚えておいた方がよいです。

分析関数 ROW_NUMBER