サブクエリとウィンドウ関数 — 行をまたいで数える

「顧客ごとに、金額の大きい注文を 2 件ずつ並べたい」。この要件は GROUP BY では書けません。集計すると顧客ごとに 1 行へ畳まれてしまい、個々の注文が消えるからです。かといって畳まなければ、順位を付ける手立てがありません。

必要なのは、行を残したまま、他の行を見て計算する道具です。SQL にはそれが 2 系統あります。サブクエリとウィンドウ関数です。

この章で学ぶこと

• サブクエリを置ける 3 つの位置と、それぞれの役割
• 相関サブクエリと EXISTS、NOT IN との違い
• CTE (WITH) で処理を読む順に並べる
• ウィンドウ関数が行を畳まずに集計する仕組み
• ROW_NUMBER、RANK、DENSE_RANK の違い
• 累計と、グループごとの上位 N 件の出し方
• どのデータベースのどのバージョンから使えるか

前提知識

結合と集計 の評価順序と GROUP BY の粒度、NULL の三値論理を前提にします。例に使う表も同じです (customers 5 行 / orders 7 行 / coupons 3 行)。

この章で扱わないこと

観点	参照先
実行計画を見て書き方を選ぶ手順	データベース設計ガイド — インデックス設計入門
再帰 CTE による階層データの探索	本章では扱いません (木構造そのものは データ構造の選び方)
結合の基本と集計の粒度	結合と集計

サブクエリが置ける 3 つの位置

サブクエリは SELECT の中に書いた SELECT です。どこに置いたかで役割が変わります。

SELECT 句に置くと、1 行 1 列の値になります (スカラサブクエリ)。外側の 1 行ごとに 1 つの値を返す必要があります。

SELECT c.name,
       (SELECT COUNT(*) FROM orders AS o WHERE o.customer_id = c.id) AS orders
FROM customers AS c
ORDER BY c.id;

顧客 5 人がそのまま 5 行で返り、高橋は 0 になります。**結合していないので行は増えません。**外部結合して COUNT する書き方と結果は同じですが、こちらは行数が変わらないことが読んだだけで分かります。

FROM 句に置くと、表として扱えます (派生テーブル)。集計した結果をさらに絞り込むときに使います。

SELECT t.customer_id, t.total
FROM (
  SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id
) AS t
WHERE t.total >= 5000
ORDER BY t.total DESC, t.customer_id;

3 行 (顧客 1 と 5 が 9500、顧客 2 が 7000) が返ります。HAVING でも同じ結果になりますが、集計結果に対してさらに結合や計算を重ねるなら派生テーブルのほうが素直です。

WHERE 句に置くと、条件の一部になります。

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount >= 5000)
ORDER BY id;

佐藤・鈴木・伊藤の 3 人が返ります。

相関サブクエリと EXISTS

上の 3 例のうち、1 つ目だけが外側の列 (c.id) を参照しています。これを相関サブクエリと呼びます。**意味としては外側の 1 行ごとに評価されます。**実際にどう実行されるかは実行計画が決めるので、書き方から速度は判断できません。

条件として使うときの定番は EXISTS です。

SELECT c.name FROM customers AS c
WHERE EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.id AND o.amount >= 5000
)
ORDER BY c.id;

先ほどの IN と同じ 3 人が返ります。EXISTS は**行が返るかどうかだけを見ます。**PostgreSQL は「サブクエリは通常、少なくとも 1 行が返るかどうかを判定できるところまでしか実行されない」と説明しています (PostgreSQL — Subquery Expressions)。SELECT 1 と書くのはこのためで、列の中身は使われません。

否定するときに IN と EXISTS の差が出ます。結合と集計 で見たとおり、NOT IN の右辺に NULL があると結果は真にならず、1 行も返りません。NOT EXISTS は行の有無だけを見るので影響を受けません。

-- AUTUMN が返る。NOT IN 版は 0 行だった
SELECT p.id, p.code FROM coupons AS p
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.coupon_id = p.id);

否定の条件を書くときは NOT EXISTS を既定にすると事故が減ります。

CTE で読む順に書く

サブクエリを入れ子にすると、内側から外側へ読むことになります。処理の順序と読む順序が逆になるので、3 段を超えたあたりから追えなくなります。

WITH を使うと、途中結果に名前を付けて上から並べられます。SQLite はこれを「1 つの SQL 文の間だけ存在する一時的なビューのように振る舞う」と説明しています (SQLite — WITH clause)。

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id
)
SELECT c.name, t.total
FROM customer_totals AS t
JOIN customers AS c ON c.id = t.customer_id
WHERE t.total >= 5000
ORDER BY t.total DESC, c.id;

佐藤 9500、伊藤 9500、鈴木 7000 が返ります。派生テーブルで書いた場合と結果は同じです。名前が付くぶん、後から読む人が「この中間結果は何を表しているか」を推測せずに済みます。カンマで区切って複数の CTE を並べれば、段階を分けて書けます。

ウィンドウ関数は行を畳まない

GROUP BY の問題は、集計すると元の行が消えることです。**ウィンドウ関数は集計値を出しながら行を残します。**PostgreSQL の説明が端的です。

ウィンドウ関数は、現在の行と何らかの関係を持つ行の集合にまたがって計算を行う。これは集計関数でできる計算と似ているが、ウィンドウ関数は集計関数のように行を 1 つの出力行へまとめてしまうことはない。行はそれぞれの独立性を保ったままになる (PostgreSQL — Window Functions より要約)

SELECT c.name, o.ordered_on, o.amount,
       SUM(o.amount) OVER (PARTITION BY o.customer_id) AS customer_total
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;

注文 7 件がそのまま 7 行で返り、各行にその顧客の合計が並びます。佐藤の 3 行にはすべて 9500 が入ります。GROUP BY customer_id なら 4 行に畳まれて、個々の注文の日付と金額は失われていました。

OVER の中身が「どの行を見るか」を決めます。PARTITION BY は集計の単位で、GROUP BY に対応します。空の OVER () なら全行が対象です。

ウィンドウ関数は SELECT 句と ORDER BY 句にしか書けません。WHERE や GROUP BY や HAVING では使えず、理由は「それらの処理の後に論理的に実行されるから」です (出典は同じ)。実際に WHERE ROW_NUMBER() OVER (...) <= 2 と書くと、SQLite は misuse of window function ROW_NUMBER() で止まります。順位で絞りたいときの書き方は後述します。

ROW_NUMBER と RANK と DENSE_RANK

同点があるときに違いが出ます。

WITH totals AS (
  SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id
)
SELECT c.name, t.total,
       ROW_NUMBER() OVER (ORDER BY t.total DESC) AS row_number,
       RANK()       OVER (ORDER BY t.total DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY t.total DESC) AS dense_rank
FROM totals AS t
JOIN customers AS c ON c.id = t.customer_id
ORDER BY t.total DESC, c.id;

name	total	row_number	rank	dense_rank
佐藤	9500	1	1	1
伊藤	9500	2	1	1
鈴木	7000	3	3	2
田中	1200	4	4	3

• ROW_NUMBER は 1 から順に連番を振ります。同点でも同じ番号にはなりません
• RANK は同点に同じ順位を与え、その分だけ次を飛ばします (1, 1, 3)
• DENSE_RANK は同点に同じ順位を与え、次を飛ばしません (1, 1, 2)

出典は SQLite — Window Functions です。

**同点のとき ROW_NUMBER がどちらの行に 1 を振るかは決まりません。**上の表で佐藤が 1、伊藤が 2 になったのは、たまたまそう実行されたからです。結果を再現させたいなら、OVER の ORDER BY に同点を割る列を足して順序を一意にします。

累計

OVER に ORDER BY を書くと、集計の対象が全行から先頭のほうだけに変わります。ただし**どこで切るかを決めているのは枠 (フレーム) で、既定の枠には後述の落とし穴があります。**まず、並び順が一意になる例です。

SELECT ordered_on, amount,
       SUM(amount) OVER (ORDER BY ordered_on, id) AS running_total
FROM orders
ORDER BY ordered_on, id;

7 行が日付順に並び、running_total が 3000 → 8000 → 13000 → 14200 → 23700 → 25700 → 27200 と増えていきます。PARTITION BY customer_id を足せば顧客ごとの累計になり、顧客が変わるたびに数え直します (佐藤なら 3000 → 8000 → 9500)。

**ORDER BY に , id を付けているのは、同じ日付の注文が入ったときに累計が崩れないようにするためです。**この例のデータは日付が全件違うので、外しても結果は変わりません。崩れる条件は次のとおりです。既定の枠は RANGE UNBOUNDED PRECEDING で、これは「先頭から、現在の行と並び順が等しい行 (peer) の最後まで」を意味します (PostgreSQL — Window Function Calls)。並び順に同じ値が並ぶと、その行たちは同じ枠を共有するので、全員に同じ合計が入ります。

手元で SUM(amount) OVER (ORDER BY customer_id) を実行すると、顧客 1 の 3 行はすべて 9500 になりました。1 行ずつ積み上がりません。同じ値が並びうる列で累計を出すなら、並び順を一意にするか、枠を ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW と明示します。

グループごとの上位 N 件

冒頭の要件です。ウィンドウ関数を WHERE に書けないので、順位を付けたものを CTE か派生テーブルに包み、外側で絞ります。

WITH ranked AS (
  SELECT o.customer_id, o.ordered_on, o.amount,
         ROW_NUMBER() OVER (
           PARTITION BY o.customer_id ORDER BY o.amount DESC, o.id
         ) AS pos
  FROM orders AS o
)
SELECT c.name, r.ordered_on, r.amount, r.pos
FROM ranked AS r
JOIN customers AS c ON c.id = r.customer_id
WHERE r.pos <= 2
ORDER BY c.id, r.pos;

佐藤は 5000 円と 3000 円の 2 行、鈴木は 5000 円と 2000 円の 2 行、田中と伊藤は 1 行ずつで、合計 6 行が返ります。注文が 3 件ある佐藤の 1500 円だけが落ちます。

ORDER BY o.amount DESC, o.id と 2 列目を足しているのは、金額が同点でも順位が決まるようにするためです。これが無いと、同点の行のどちらが選ばれるかが実行のたびに変わりえます。

どの書き方を選ぶか

同じ結果を出せる書き方が複数あります。**どちらが速いかは書き方だけでは決まりません。**どの実行計画になるかはデータの分布と索引次第なので、比べたいなら EXPLAIN で実測します (データベース設計ガイド — インデックス設計入門)。判断できるのは意味と読みやすさです。

やりたいこと	選ぶもの	理由
条件を満たす行があるかを見たい	EXISTS / NOT EXISTS	行が増えない。NULL の影響を受けない
相手の表の列の値が要る	結合	サブクエリでは 1 列 1 値しか返せない
集計結果をさらに加工したい	CTE または派生テーブル	途中結果に名前が付き、上から読める
集計値を出しつつ元の行も残したい	ウィンドウ関数	GROUP BY では行が消える
グループごとの上位 N 件	ウィンドウ関数 + CTE	順位付けと絞り込みは同じ階層に書けない

結合で書くと行が増えることを忘れないでください。「注文が 5000 円以上ある顧客」を結合で書くと、条件を満たす注文が 2 件ある顧客は 2 行になります。DISTINCT で消す前に、EXISTS で書けないかを見ます。

使えるかどうかを先に確かめる

どちらも比較的新しい機能で、古い環境では動きません。

データベース	ウィンドウ関数	CTE (WITH)
PostgreSQL	8.4 から (2009-07-01)	8.4 から (2009-07-01)
MySQL	8.0 から	8.0 から
SQLite	3.25.0 から (2018-09-15)	3.8.3 から (2014-02-03)

出典は PostgreSQL 8.4 リリースノート、MySQL — What Is New in MySQL 8.0、SQLite — Window Functions、SQLite 3.8.3 リリースノート です。

**同じ機能でも、細かい構文の対応時期はさらに後ろにずれます。**SQLite でウィンドウの範囲指定 (EXCLUDE 句、GROUPS、RANGE での式による境界) が使えるようになったのは 3.28.0 (2019-04-16) からです。基本の PARTITION BY と ORDER BY が入ったのは 3.25.0 (2018-09-15) で、別の版になります。MySQL で再帰 CTE に LIMIT を書けるのは 8.0.19 からです。版の条件は機能単位ではなく構文単位で確かめてください。

MySQL 5.7 以前を使っている環境では、どちらも書けません。移行の予定があるならウィンドウ関数を前提に設計を分けるほうが、後の書き直しが減ります。

よくある誤解

「ウィンドウ関数は GROUP BY の代わり」 — 目的が逆です。GROUP BY は行を畳み、ウィンドウ関数は畳みません。1 顧客 1 行がほしいなら GROUP BY のままで足ります。

「WHERE で順位を絞れる」 — ウィンドウ関数は SELECT と ORDER BY にしか書けません。順位で絞るには CTE か派生テーブルに包みます。

「RANK と ROW_NUMBER はほぼ同じ」 — 同点があると結果が変わります。上位 3 件を取るとき、RANK なら 1 位が 3 人いれば 3 行、ROW_NUMBER なら常に 3 行です。

「ROW_NUMBER の順位は毎回同じ」 — OVER の ORDER BY が同点を割れていなければ、どの行が先に来るかは決まりません。一意になる列を足します。

「OVER に ORDER BY を書けば累計になる」 — 既定の枠は peer の最後までなので、並び順に同じ値が並ぶとその行たちが同じ合計になります。並び順を一意にするか、枠を ROWS で明示します。

「CTE のほうが速い / 遅い」 — 一般には決まりません。ただし PostgreSQL 12 以降は、副作用がなく再帰でもなく 1 回しか参照されない CTE を外側のクエリへ埋め込み (インライン化)、複数回参照される CTE は 1 回だけ計算します。MATERIALIZED で埋め込みを止め、NOT MATERIALIZED で強制できます。12 より前は埋め込みが一切なく、常に先に評価されていました (PostgreSQL 12 リリースノート)。差が出るかは版と参照回数で決まるので、比べるなら EXPLAIN を見ます。

「サブクエリより結合のほうが速い」 — 同上です。ただし結合は行を増やすことがあるので、意味の面では EXISTS と使い分けが要ります。

確認問題

問 1. SELECT customer_id, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS pos FROM orders WHERE pos = 1 はエラーになります。理由と、正しい書き方を説明してください。

理由は 2 つあり、どちらも評価順序から導けます。

1 つ目は、pos が SELECT 句で付けた別名だからです。WHERE は SELECT より前に評価されるので、その時点で pos は存在しません (結合と集計 で見た制約です)。ただしこれは標準 SQL と PostgreSQL / MySQL の話で、SQLite は別名を WHERE で使えます。

2 つ目は、別名でなく ROW_NUMBER() OVER (...) をそのまま WHERE に書いても通らないことです。ウィンドウ関数は SELECT 句と ORDER BY 句にしか書けません。WHERE の処理が終わった後に実行されるからです。

正しい書き方は、順位を付けたものを包んでから絞ることです。

WITH ranked AS (
  SELECT customer_id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, id) AS pos
  FROM orders
)
SELECT customer_id, amount FROM ranked WHERE pos = 1;

ORDER BY に id を足しているのは、同じ金額の注文があったときに順位を一意にするためです。このクエリは 4 行 (顧客 1 が 5000 円、2 が 5000 円、3 が 1200 円、5 が 9500 円) を返します。

なお、SQLite で実際に出るエラーは 2 つ目のほうだけです (misuse of window function ROW_NUMBER())。1 つ目は SQLite では止まりません。

問 2. 合計金額が 9500 円の顧客が 2 人、7000 円が 1 人、1200 円が 1 人います。金額の降順に RANK と DENSE_RANK を付けると、それぞれどうなりますか。

RANK は 1, 1, 3, 4 になり、DENSE_RANK は 1, 1, 2, 3 になります。

どちらも同点には同じ順位を与えます。違うのは次の順位の決め方です。RANK は「自分より前に何行あったか」を見るので、1 位が 2 人いれば次は 3 位になります。DENSE_RANK は順位の値そのものを 1 ずつ増やすので、飛びません。

「上位 3 位までを表示する」という要件で挙動が分かれます。RANK <= 3 なら 3 行 (9500 の 2 人と 7000 の 1 人)、DENSE_RANK <= 3 なら 4 行 (全員) が返ります。同点をどう扱いたいかを先に決めてから、関数を選びます。

問 3. 「一度もクーポンを使っていない顧客」を出したいとき、NOT IN ではなく NOT EXISTS を勧めるのはなぜですか。両方のクエリを書いて説明してください。

NOT IN は、右辺に NULL が 1 つでもあると 1 行も返さなくなるからです。

-- 右辺に NULL が 1 つでも混じった瞬間、結果が常に空になる
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE coupon_id IS NOT NULL);

-- NULL の有無に影響されない
SELECT c.name FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.id AND o.coupon_id IS NOT NULL
);

どちらも鈴木・高橋・伊藤の 3 人を返します。この例では orders.customer_id に NOT NULL 制約があるので、上のクエリも今は正しく動きます。**問題は、それが制約に依存していることです。**列の定義が変わったり、途中に外部結合が挟まったりして NULL が混じった瞬間に、エラーを出さないまま空の結果を返すようになります。

NOT EXISTS は「条件を満たす行が存在するか」だけを見るので、この壊れ方をしません。否定の条件では NOT EXISTS を既定にします。

まとめ

• サブクエリは置く位置で役割が変わります。SELECT 句なら 1 つの値、FROM 句なら表、WHERE 句なら条件の一部です
• EXISTS は行が返るかどうかだけを見ます。否定するときは NOT IN でなく NOT EXISTS を既定にします
• CTE は途中結果に名前を付けて、処理を上から並べられるようにします。派生テーブルと結果は同じですが、実行のされ方は版と参照回数で変わります (PostgreSQL 12 以降は 1 回だけ参照される CTE を埋め込む)
• ウィンドウ関数は行を畳まずに集計値を出します。GROUP BY と目的が逆です
• ウィンドウ関数は SELECT 句と ORDER BY 句にしか書けません。順位で絞るには CTE か派生テーブルに包みます
• ROW_NUMBER は連番、RANK は同点で飛ぶ順位、DENSE_RANK は飛ばない順位です。同点を一意にしたいなら OVER の ORDER BY に列を足します
• **既定の枠は「現在の行の最後の peer まで」**なので、並び順に同じ値が並ぶと累計になりません。並び順を一意にするか、枠を ROWS で明示します
• 同じ結果になる書き方どうしの速さは、書き方だけでは決まりません。選ぶ基準は意味と読みやすさで、速さは EXPLAIN で確かめます
• 対応バージョンは PostgreSQL 8.4 / MySQL 8.0 / SQLite 3.25.0 (CTE は 3.8.3) です。構文の細部はさらに後の版で入っています

関連リファレンス

• データベース設計ガイド — インデックス設計入門 — EXPLAIN の読み方と、どの列に索引を張るか
• PostgreSQL — Window Functions — ウィンドウ関数の定義と、書ける場所の制限
• PostgreSQL — Subquery Expressions — EXISTS と IN の意味
• SQLite — Window Functions — 順位を返す関数の定義と対応バージョン
• MySQL — WITH (Common Table Expressions) — CTE と再帰 CTE の構文

次に読む

• イベントループ — ここから第 13 部。データの読み書きから、処理そのものがどう並ぶかの話へ移ります
• インデックス — 書いたクエリが速く動くかどうかを決める側
• データの分散 — 表が 1 台に収まらなくなったとき、結合と集計に何が起きるか
• ロックと分離レベル — 集計している最中に他の更新が入るとどう見えるか