GROUP BY で泣いた夜に
「部署ごとの最高給与を取りたいだけなのに、なんで社員の名前も一緒に返ってこないの……？」
SQLを書いていて、こんな“理不尽”に出会ったことはありませんか。GROUP BY で集約すると、集計に使った列以外の情報は容赦なく消し飛びます。集約は便利ですが、たとえるなら「クラス全員の平均点は分かるけど、誰が一番だったかは教えない先生」みたいなものです。気になるところだけ隠される。
そんなときに登場するのが 分析関数(Analytic Functions) です。分析関数は、行をまとめて消してしまう GROUP BYと違い、1行1行を残したまま、その行から見た「順位」や「前の行の値」を計算して列として付け足してくれる、気の利いた相棒です。
この記事では、現場で出番の多い 3 兄弟、
-
ROW_NUMBER（とにかく連番） -
RANK（同点は仲良く同じ順位） -
LAG（ひとつ前の行をのぞき見）
を、実際に動くSQLとともに紹介します。
対象はOracle Database 19cです。
分析関数のキホン：OVER句
分析関数の構文は、ざっくり言うとこうです。
関数名(引数) OVER (
[PARTITION BY 列リスト] -- グループ分け（任意）
[ORDER BY 列リスト] -- 並び順
[ウィンドウ句] -- 集計範囲（任意）
)
ポイントは OVER 句。これが「どの範囲で計算するか」を指定するものです。
-
PARTITION BY：
GROUP BYの親戚。ただし行は消えない。「部署ごとに」「顧客ごとに」といった仕切りを作ります。書かなければ全行が1つのグループ。 - ORDER BY：その仕切りの中での並び順。順位付けや「前の行」の概念は、並び順があって初めて意味を持ちます。
用語補足：「ウィンドウ（window）」とは、計算対象になる行の範囲のこと。
注目している行の周辺だけを見る、というイメージです。
では、以下のような社員テーブルを例に進めます。
CREATE TABLE employees (
emp_id NUMBER,
emp_name VARCHAR2(20),
dept VARCHAR2(10),
salary NUMBER
);
INSERT INTO employees VALUES (1, '田中', '営業', 500000);
INSERT INTO employees VALUES (2, '鈴木', '営業', 600000);
INSERT INTO employees VALUES (3, '佐藤', '営業', 600000);
INSERT INTO employees VALUES (4, '高橋', '開発', 700000);
INSERT INTO employees VALUES (5, '伊藤', '開発', 550000);
INSERT INTO employees VALUES (6, '渡辺', '開発', 550000);
COMMIT;
失敗例：やりがちなSQL
まず部署ごとの最高給与のみ見てみましょう。
SELECT dept,
MAX(salary)
FROM employees
GROUP BY dept;
結果：
DEPT MAX(SALARY)
営業 600000
開発 700000
SQL触りたての多くの人がこう思うかもしれません。
「営業部の最高給与は 60万円なんだから、鈴木さんの名前も一緒に出せばいいじゃん！」
そこで次のように書いてみます。
SELECT dept,
emp_name,
MAX(salary)
FROM employees
GROUP BY dept;
すると、エラーが出ます。
ORA-00979: "EMP_NAME": must appear in the GROUP BY clause or be used in an aggregate function
なぜならOracleにとって、
営業部 → 600000円
という情報は分かっても、
600000円の人 → 鈴木さん？
600000円の人 → 佐藤さん？
が決められないからです。
実際、サンプルデータを見ると営業部には最高給与の社員が2人います。
EMP_NAME DEPT SALARY
鈴木 営業 600000
佐藤 営業 600000
こんな時に分析関数を使用して解決することができます。
ここからは、分析関数の実践をしていきましょう。
① ROW_NUMBER：とにかく重複なしの連番が欲しい
ROW_NUMBER は、並び順に沿って 1, 2, 3, ... と重複なしの連番を振ります。同じ値が並んでいても容赦なく別番号です。整理券を配るおじさんだと思ってください。給与が同じでも「はい次の人、次の人」と機械的に番号を渡します。
SELECT
emp_name,
dept,
salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees;
結果（営業・開発それぞれで連番）：
EMP_NAME DEPT SALARY RN
鈴木 営業 600000 1
佐藤 営業 600000 2 ← 同額でも番号は別
田中 営業 500000 3
高橋 開発 700000 1
伊藤 開発 550000 2
渡辺 開発 550000 3
使い方：各グループの「1位だけ」を取り出す
冒頭の悩み「部署ごとの最高給与の人を、名前付きで」はこう解決します。
SELECT emp_name, dept, salary
FROM (
SELECT
emp_name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
)
WHERE rn = 1;
結果：
EMP_NAME DEPT SALARY
鈴木 営業 600000
高橋 開発 700000
rn = 1 で各部署のトップだけが残ります。注意点として、WHERE 句では分析関数を直接使えない（WHERE ROW_NUMBER() = 1 はエラー）ので、いったんサブクエリで列にしてから絞り込みます。分析関数は SELECT と ORDER BY でしか書けない、と覚えておきましょう。
② RANK：同点は同じ順位、でも番号は飛ぶ
スポーツの順位表を思い出してください。1位タイが2人いたら、次は3位ですよね（2位はいない）。これが RANK の挙動です。同じ値には同じ順位を与え、その分だけ次の順位を飛ばします。
SELECT
emp_name,
dept,
salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees;
結果：
EMP_NAME DEPT SALARY RNK
鈴木 営業 600000 1
佐藤 営業 600000 1 ← 同額なので同じ1位
田中 営業 500000 3 ← 2位は飛んで3位
高橋 開発 700000 1
伊藤 開発 550000 2
渡辺 開発 550000 2
営業の田中さんが「3位」になっているのがポイント。1位が2人いたので2位は欠番です。
「順位を飛ばしたくない」なら DENSE_RANK
「同点でもいいけど、次は2位にしてほしい（欠番イヤ）」という場合は DENSE_RANK を使います。denseは「密な」という意味で、その名のとおり順位がギッシリ詰まって飛びません。
SELECT
emp_name,
salary,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;
結果：
EMP_NAME DEPT SALARY RNK DESNSE_RNK
高橋 開発 700000 1 1
鈴木 営業 600000 2 2
佐藤 営業 600000 2 2
伊藤 開発 550000 4 3
渡辺 開発 550000 4 3
田中 営業 500000 6 4
3兄弟（ROW_NUMBER / RANK / DENSE_RANK）の違いを一言でまとめると：
- ROW_NUMBER：同点を認めない。常に連番。
- RANK：同点を認める。順位は飛ぶ。
- DENSE_RANK：同点を認める。順位は飛ばない。
「TOP3を取りたい」場合、ROW_NUMBER なら同点を無視して必ず3行、RANK/DENSE_RANK なら同点を全部拾うので3行を超えることがあります。要件次第で使い分けましょう。
③ LAG：ひとつ前の行をこっそりのぞく
LAG は、並び順における「ひとつ前の行の値」を今の行に持ってきてくれる関数です。
月別売上の「前月比」を出す、という定番ケースで使います。
CREATE TABLE monthly_sales (
ym VARCHAR2(6),
amount NUMBER
);
INSERT INTO monthly_sales VALUES ('202401', 1000);
INSERT INTO monthly_sales VALUES ('202402', 1500);
INSERT INTO monthly_sales VALUES ('202403', 1200);
INSERT INTO monthly_sales VALUES ('202404', 1800);
COMMIT;
SELECT
ym,
amount,
LAG(amount) OVER (ORDER BY ym) AS prev_amount,
amount - LAG(amount) OVER (ORDER BY ym) AS diff
FROM monthly_sales;
結果：
YM AMOUNT PREV_AMOUNT DIFF
202401 1000 (null) (null)
202402 1500 1000 500
202403 1200 1500 -300
202404 1800 1200 600
一番最初の行は「前の行」が存在しないので NULL になります。先頭の NULL が気になるなら、LAG の引数で何個前かとデフォルト値を指定できます。
-- LAG(対象列, ずらす行数, 該当なし時のデフォルト値)
LAG(amount, 1, 0) OVER (ORDER BY ym)
こうすると先頭は 0 になり、差分の計算で NULL に悩まされません。
LEAD は LAG の双子
逆に「次の行」を見たいときは LEAD を使います。引数の使い方は LAG と同じです。「翌月の売上」を見たいなら LEAD(amount) OVER (ORDER BY ym)。
まとめ：迷ったらこの早見表
| やりたいこと | 使う関数 |
|---|---|
| グループごとに重複なしの連番／各グループの1件抽出 | ROW_NUMBER |
| 順位付け（同点同順位・順位は飛ぶ） | RANK |
| 順位付け（同点同順位・順位は飛ばない） | DENSE_RANK |
| 前の行の値（前月比など） | LAG |
| 次の行の値（翌月予測など） | LEAD |
分析関数のキモは、結局のところ OVER (PARTITION BY ... ORDER BY ...) という「どの仕切りで、どんな順番に」を表現する部分に尽きます。ここさえ押さえれば、これまで GROUP BY とサブクエリの森をさまよっていたクエリが、驚くほどスッキリします。
そして繰り返しになりますが、大事なルール：
- 分析関数は
SELECTとORDER BY句でしか書けない -
WHEREで絞りたいときは、いったんサブクエリで列にしてから
この2点を忘れなければ、もう「平均点は教えるけど誰が1位かは秘密」の先生に泣かされることはありません。
参考
- Oracle Database 19c Documentation Library（Oracle Database SQL言語 内の 分析ファンクション、ならびに
ROW_NUMBER／RANK／DENSE_RANK／LAG／LEAD各関数の説明）