順位を付ける - ROW_NUMBER / RANK / DENSE_RANK / NTILE

ランキング系ウィンドウ関数。同値・欠番の扱いが関数ごとに違うので使い分ける

概念図

ROW_NUMBER / RANK / DENSE_RANK / NTILE diagram

構文

sql

ROW_NUMBER() | RANK() | DENSE_RANK() | NTILE(n) OVER ( [PARTITION BY ...] ORDER BY ... )

サンプル

部署ごとに給与順で 3 種類の順位を付ける

sql

SELECT
  department_id,
  name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rk,
  DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS drk
FROM employees;

3 つのランキング関数の違い

同じ値（タイ）が 1 位に 3 人いる場合を考えます。

ROW_NUMBER(): 1, 2, 3, 4, ... と 必ず連番。タイでも片方に順位が付く（どちらになるかは ORDER BY の安定性次第）
RANK(): 1, 1, 1, 4 — タイは同順位、次は 人数分スキップ
DENSE_RANK(): 1, 1, 1, 2 — タイは同順位、次は +1（欠番なし）

「3 位以内を取りたい」というとき、RANK だと同率 1 位が 3 人いれば次は 4 位。DENSE_RANK なら次は 2 位。ユースケースで選び分けます。

「各グループの TOP N」の定石

ウィンドウ関数の超定番パターンが「各グループから上位 N 件を取る」です。ROW_NUMBER() でグループ内順位を付け、外側で WHERE rn <= N で絞ります。WHERE にウィンドウ関数を直接書けないので CTE かサブクエリが必要です。

sql

-- 部署ごとに給与トップ3
WITH ranked AS (
  SELECT
    department_id, name, salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT department_id, name, salary
FROM ranked
WHERE rn <= 3;

NTILE による分位

NTILE(n) は行数を できるだけ均等に n 個のバケットに分ける関数です。NTILE(4) なら四分位、NTILE(10) なら十分位。行数が n で割り切れないときは、先頭のバケットから 1 行ずつ多く配分されます。

パーセンタイルが欲しいだけなら、SQL:2003 の PERCENT_RANK() や CUME_DIST() の方が意味的に正確です。

落とし穴

ORDER BY の不安定さ: ROW_NUMBER はタイ破り用の列も ORDER BY に含めないと、実行ごとに順位が揺れる
NULL の位置: ORDER BY の NULLS FIRST / NULLS LAST は PostgreSQL / Oracle / SQLite 3.30+ / SQL Server 2022+ でサポート。MySQL 8.x は未対応で CASE 回避策が必要
RANK と LIMIT の組み合わせ: 同率を含めて N 件欲しいときは LIMIT ではなく WHERE rk <= N を使う