【Oracle】RANK・DENSE_RANK・ROW_NUMBER完全ガイド｜違い・グループ内順位・集計RANK・実践パターンまで解説

SELECT
    emp_name,
    salary,
    RANK()       OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    NTILE(3)     OVER (ORDER BY salary DESC) AS ntile3
FROM (
    SELECT 'Alice'   AS emp_name, 8000 AS salary FROM dual UNION ALL
    SELECT 'Bob',    6000 FROM dual UNION ALL
    SELECT 'Carol',  6000 FROM dual UNION ALL
    SELECT 'Dave',   4500 FROM dual UNION ALL
    SELECT 'Eve',    4500 FROM dual UNION ALL
    SELECT 'Frank',  3000 FROM dual
);

-- 基本: 全体の給与ランキング（降順）
SELECT
    department_id,
    employee_id,
    last_name,
    salary,
    RANK()       OVER (ORDER BY salary DESC) AS salary_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_dense_rank
FROM employees
ORDER BY salary DESC;

-- 部署ごとに給与の順位を付ける（PARTITION BY）
SELECT
    department_id,
    employee_id,
    last_name,
    salary,
    RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_dense_rank
FROM employees
ORDER BY department_id, salary DESC;

-- 給与降順・入社日昇順（同じ給与なら早く入社した人を上位に）
SELECT
    employee_id,
    last_name,
    hire_date,
    salary,
    RANK() OVER (
        ORDER BY salary DESC, hire_date ASC
    ) AS salary_seniority_rank
FROM employees
ORDER BY salary DESC, hire_date ASC;

-- 部署内で「業績スコア高・欠勤少」の複合評価ランキング
SELECT
    department_id,
    employee_id,
    performance_score,
    absent_days,
    DENSE_RANK() OVER (
        PARTITION BY department_id
        ORDER BY performance_score DESC, absent_days ASC
    ) AS eval_rank
FROM employee_eval
ORDER BY department_id, eval_rank;

-- 欠勤日数が少ない順にランク付け（昇順 = 少ない方が1位）
SELECT
    employee_id,
    last_name,
    absent_days,
    RANK()       OVER (ORDER BY absent_days ASC)  AS absent_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank,
    -- 下位ランキング（給与が低い方が1位）
    RANK()       OVER (ORDER BY salary ASC)  AS low_salary_rank
FROM employees
ORDER BY absent_days;

-- 給与 5000 は全社員の中で何位になるか？
SELECT RANK(5000) WITHIN GROUP (ORDER BY salary DESC) AS hypothetical_rank
FROM employees;

-- 5000 はその給与を下回る人数 + 1 位が返る（RANK の定義と同じ）
-- 結果: 5000以上の給与の人数 + 1

-- DENSE_RANK 版（飛び番なし）
SELECT DENSE_RANK(5000) WITHIN GROUP (ORDER BY salary DESC) AS hypothetical_dense_rank
FROM employees;

-- 部署ごとに「5000という給与は何位相当か」を確認
SELECT
    department_id,
    RANK(5000) WITHIN GROUP (ORDER BY salary DESC) AS rank_if_5000
FROM employees
GROUP BY department_id
ORDER BY department_id;

-- 部署ごとに「最高給与の社員名（同率は姓の昇順で1人）」を取得
SELECT
    department_id,
    MAX(salary)                                              AS max_salary,
    MIN(last_name) KEEP (DENSE_RANK FIRST ORDER BY salary DESC)
                                                            AS top_earner_name,
    COUNT(*) KEEP (DENSE_RANK FIRST ORDER BY salary DESC)   AS tie_count
FROM employees
GROUP BY department_id
ORDER BY department_id;

-- 部署ごとに最低給与の社員名と入社日（LAST ORDER BY salary DESC = 最小給与）
SELECT
    department_id,
    MIN(salary)                                               AS min_salary,
    MIN(last_name)  KEEP (DENSE_RANK LAST ORDER BY salary DESC) AS lowest_earner,
    MIN(hire_date)  KEEP (DENSE_RANK LAST ORDER BY salary DESC) AS hire_date_of_lowest
FROM employees
GROUP BY department_id
ORDER BY department_id;

SELECT
    employee_id,
    last_name,
    salary,
    RANK()        OVER (ORDER BY salary DESC) AS rnk,
    CUME_DIST()   OVER (ORDER BY salary ASC)  AS cume_dist_pct,   -- 0.x 形式
    PERCENT_RANK() OVER (ORDER BY salary ASC) AS pct_rank,         -- 0.x 形式
    ROUND(CUME_DIST() OVER (ORDER BY salary ASC) * 100, 1)  AS cume_dist_100
FROM employees
ORDER BY salary DESC;

-- 全体の上位 30%（CUME_DIST >= 0.7）に該当する社員を抽出
SELECT employee_id, last_name, salary, cd
FROM (
    SELECT employee_id, last_name, salary,
           CUME_DIST() OVER (ORDER BY salary ASC) AS cd
    FROM employees
)
WHERE cd >= 0.7
ORDER BY salary DESC;

-- Oracle のデフォルト: DESC では NULL が先頭（最上位）、ASC では NULL が末尾
SELECT
    employee_id,
    commission_pct,
    -- NULL を末尾に（コミッションなし社員を最下位にする）
    RANK() OVER (ORDER BY commission_pct DESC NULLS LAST)  AS rank_nulls_last,
    -- NULL を先頭に（コミッションなし社員を最上位にする）
    RANK() OVER (ORDER BY commission_pct DESC NULLS FIRST) AS rank_nulls_first
FROM employees
ORDER BY commission_pct DESC NULLS LAST;

-- 部署ごとの給与上位 3 名（同率を含む → RANK を使う）
SELECT department_id, employee_id, last_name, salary, dept_rank
FROM (
    SELECT
        department_id,
        employee_id,
        last_name,
        salary,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
    FROM employees
)
WHERE dept_rank <= 3
ORDER BY department_id, dept_rank;

-- 同率 3 位を除外して厳密に 3 名だけにする → ROW_NUMBER を使う
SELECT department_id, employee_id, last_name, salary, row_num
FROM (
    SELECT
        department_id, employee_id, last_name, salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
    FROM employees
)
WHERE row_num <= 3
ORDER BY department_id, row_num;

-- 顧客ごとに最新の注文 1 件だけを取得（ROW_NUMBER で重複排除）
SELECT customer_id, order_id, order_date, amount
FROM (
    SELECT
        customer_id,
        order_id,
        order_date,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC  -- 同日なら order_id 降順
        ) AS rn
    FROM orders
)
WHERE rn = 1
ORDER BY customer_id;

-- 給与降順で 11〜20 件目（2 ページ目）を取得
SELECT employee_id, last_name, salary, rn
FROM (
    SELECT
        employee_id,
        last_name,
        salary,
        ROW_NUMBER() OVER (ORDER BY salary DESC, employee_id) AS rn
    FROM employees
)
WHERE rn BETWEEN 11 AND 20
ORDER BY rn;

-- Oracle 12c 以降は FETCH FIRST / OFFSET でシンプルに書ける
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC, employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- 部署内の給与順位で A/B/C ランクを付与
-- Oracle ではウィンドウ関数のネストが不可のため 2 段階サブクエリで記述
SELECT
    department_id,
    employee_id,
    last_name,
    salary,
    dense_rnk,
    max_rank,
    CASE
        WHEN dense_rnk <= ROUND(max_rank * 0.2) THEN 'A'
        WHEN dense_rnk <= ROUND(max_rank * 0.6) THEN 'B'
        ELSE 'C'
    END AS salary_grade
FROM (
    -- 第 2 段階: 部署内の最大 DENSE_RANK を取得
    SELECT
        department_id, employee_id, last_name, salary, dense_rnk,
        MAX(dense_rnk) OVER (PARTITION BY department_id) AS max_rank
    FROM (
        -- 第 1 段階: DENSE_RANK を計算
        SELECT
            department_id, employee_id, last_name, salary,
            DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
        FROM employees
    )
)
ORDER BY department_id, dense_rnk;

-- 今月と先月の売上ランキングを比較して順位が上昇した商品を抽出
WITH current_rank AS (
    SELECT product_id,
           RANK() OVER (ORDER BY monthly_sales DESC) AS rnk
    FROM sales_monthly WHERE yyyymm = '202412'
),
prev_rank AS (
    SELECT product_id,
           RANK() OVER (ORDER BY monthly_sales DESC) AS rnk
    FROM sales_monthly WHERE yyyymm = '202411'
)
SELECT c.product_id,
       p.rnk AS prev_rank,
       c.rnk AS curr_rank,
       p.rnk - c.rnk AS rank_up  -- プラスが順位上昇
FROM current_rank c JOIN prev_rank p ON c.product_id = p.product_id
WHERE p.rnk - c.rnk > 0
ORDER BY rank_up DESC;