ウィンドウ関数 ③ — 順位・分析関数と実行順序

順位を付ける — ROW_NUMBER / RANK / DENSE_RANK

順位を付ける専用の関数がROW_NUMBER() / RANK() / DENSE_RANK()です。

いずれもOVER (ORDER BY ...)で並べた順に番号を振りますが、同順位（同じ値）の扱いが異なります。

下の SQL と、その結果を表した図で、売上合計タイがある実例を 1 行ずつ確認してください。

-- 社員ごとの売上合計を求め、合計の高い順に 3 種類の順位を付ける
SELECT emp_id,
  SUM(amount) AS total,
  ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS rn,
  RANK()       OVER (ORDER BY SUM(amount) DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dr
FROM sales
GROUP BY emp_id
ORDER BY total DESC;
-- GROUP BY で先に集計してから、その集計値に対して順位を付けられる

「社員の売上合計ランキングを作りたい。同額タイがあるので、連番・順位飛ばしあり・順位飛ばしなしの 3 種類を並べて違いを見たい」という要件を想定します。（正しく実行できれば解説が表示されます）

① salesテーブルをemp_idでGROUP BYし、SUM(amount)をtotalという別名で取り出してください。

② 売上合計の降順で並べたウィンドウに、ROW_NUMBER()をrn、RANK()をrk、DENSE_RANK()をdrという別名で追加してください。

③ 判定は行の並び順を見るため、totalの降順、同額のときはemp_idの昇順で並べてください。

LAG / LEAD — 前の行・次の行の値を持ってくる

LAG(列)は1 つ前の行の値、LEAD(列)は1 つ後ろの行の値を、現在行に持ってくる関数です（lag = 後れる、lead = 先んじる）。

OVER (PARTITION BY ... ORDER BY ...)で「どの順に並べた前後か」を決めます。

前回売上との差（前回比）、前月との増減、次回予定との比較などに使います。

LAG(amount)は前の行がない最初の行でNULLを返します（LEADは最後の行が NULL）。

LAG(amount, 1, 0)のように第 2 引数で「何行前か」、第 3 引数で「該当行がないときの既定値」を指定できます。

amount - LAG(amount) OVER (...)のように引き算すれば、そのまま前回比の差額になります。

-- 前回売上 (LAG) と前回比の差額を出す
SELECT emp_id, sale_date, amount,
  LAG(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS prev_amount,
  amount - LAG(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS diff_prev
FROM sales
ORDER BY emp_id, sale_date;

-- 次回売上 (LEAD) を併記したいときは LEAD を使う
SELECT emp_id, sale_date, amount,
  LEAD(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS next_amount
FROM sales
ORDER BY emp_id, sale_date;

「社員ごとに、売上を日付順に並べて、前回売上と前回比の差額を併記したい」という要件を想定します。

① salesテーブルからemp_id、sale_date、amountの 3 列を取り出してください。

② 社員ごとに区切り、sale_date順に並べたウィンドウで、1 つ前のamountをprev_amountという別名で追加してください。

③ さらにamountから前回のamountを引いた差額をdiff_prevという別名で追加してください。

④ 判定は行の並び順を見るため、emp_idの昇順、同じ社員内はsale_dateの昇順で並べてください。

NTILE — 並べた行を n 個のグループに均等分割する

NTILE(n)は、OVER (ORDER BY ...)で並べた行をn 個のグループにできるだけ均等に分割し、各行に 1 〜 n のグループ番号を振ります。

「上位 1/4」「四分位」「五分位」のような分位分けに使います。

行数が n で割り切れないときは、前のグループから 1 行ずつ多く配分されます（例: 5 行を 4 グループにすると、1 番目が 2 行、残りが 1 行ずつ）。

PARTITION BYと併用すれば「社員ごとに、その社員の売上を上位・下位グループに分ける」こともできます。

下の図で、emp_id=11 の 5 件を金額の高い順に NTILE(4) で 4 分割した結果を確認してください。

-- 社員ごとに、売上を金額の高い順で 4 グループに分ける
SELECT emp_id, sale_date, amount,
  NTILE(4) OVER (PARTITION BY emp_id ORDER BY amount DESC) AS quartile
FROM sales
ORDER BY emp_id, amount DESC;

-- 全社員の売上を金額順に 4 等分（上位 25% を quartile = 1 で取り出せる）
SELECT sale_id, emp_id, amount,
  NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM sales
ORDER BY amount DESC;

「社員ごとに、その社員の売上を金額の高い順で 4 つのグループに分け、quartile 順（上位グループから下位グループへ）に一覧したい」という要件を想定します。

① salesテーブルからemp_id、sale_date、amountの 3 列を取り出してください。

② 社員ごとに区切り、amountの降順に並べたウィンドウでNTILE(4)を計算し、quartileという別名で 4 列目に追加してください。

③ 判定は行の並び順を見るため、quartileの昇順、同じ quartile 内では emp_id の昇順、さらに sale_id の昇順で並べてください（ORDER BY は SELECT で計算した別名 quartile を直接参照できます）。

「上位 25%（quartile = 1）の売上だけに絞り込みたい」という要件を想定します。実践 3 のクエリにWHERE quartile = 1を足してみてください。

① 実践 3 と同じSELECTにし、その後にWHERE quartile = 1を付けてください。

② このクエリは実行するとエラーになります（エラーが出れば正解です）。

③ なぜエラーになるのかは次の節「SQL の実行順序」で説明します。ORDER BYでは使えたquartileが、WHEREでは使えない理由を頭に置いて先に進んでください。

SQL の実行順序 — ウィンドウ関数を WHERE で使えない理由

「WHERE rk = 1のように、付けた順位で絞り込めないのか」と思うかもしれません。

これはエラーになります。

理由はSQL の論理的な実行順序にあります。

書く順番（SELECT → FROM → WHERE ...）と、評価される順番は別です。

評価は概ね次の順で進みます: FROM / JOIN（表をつなぐ）→ WHERE（行を絞る）→ GROUP BY（グループ化）→ HAVING（グループを絞る）→ SELECT（列を計算、ここでウィンドウ関数が評価される）→ ORDER BY（並べ替え）→ LIMIT（件数制限）。

WHEREはSELECTより前に評価されるため、SELECT段階で初めて計算されるウィンドウ関数の結果（順位など）をWHEREから参照できません。

順位で絞り込みたいときは、ウィンドウ関数を計算するクエリをサブクエリ（または CTE）にして 1 段階内側に入れ、外側のWHEREで絞ります。

内側のSELECTでウィンドウ関数が評価され終わってから、外側がその結果を 1 つの列として扱えるためです。

-- これはエラー: WHERE は SELECT より前なので rk をまだ参照できない
-- SELECT emp_id, RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
-- FROM sales GROUP BY emp_id WHERE rk <= 3;

-- 正: 順位を内側で計算し、外側の WHERE で絞る
SELECT * FROM (
  SELECT emp_id,
    SUM(amount) AS total,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
  FROM sales
  GROUP BY emp_id
) AS ranked
WHERE rk <= 3
ORDER BY rk, emp_id;

「売上合計の上位 3 名だけを取り出したい。順位での絞り込みなので実行順序を踏まえてサブクエリを使いたい」という要件を想定します。

① 内側のサブクエリで、salesをemp_idでGROUP BYし、SUM(amount)をtotal、RANK() OVER (ORDER BY SUM(amount) DESC)をrkという別名で取り出してください。サブクエリにはrankedという別名を付けてください。

② 外側のWHEREでrk <= 3の行だけに絞り込んでください。

③ 判定は行の並び順を見るため、rkの昇順、同順位のときはemp_idの昇順で並べてください。