ネストされたサブクエリ、自己結合(Self-Join)、それともウィンドウ関数?
昨年の初め、私たちの売上ダッシュボードは注文データが500万件に達したあたりから急激にパフォーマンスが低下し始めました。ピークとなる毎週月曜の朝、マーケティングチームが「売上トップ3の注文と、その前後の注文との比較レポート」を抽出すると、RDS PostgreSQLのCPU使用率は一気に98%まで跳ね上がり、クエリの完了までに30秒以上もかかる状態に陥っていました。
このような課題に直面した際、バックエンドエンジニアが検討するアプローチは主に次の3つです。
- 相関サブクエリ(Correlated Subquery):
SELECT句やWHERE句にサブクエリを埋め込む手法。初期段階ではロジックを直感的に把握しやすいのが特徴です。 - 自己結合(Self-Join): タイムスタンプやIDをキーにして、同一テーブル同士を結合する手法。
- ウィンドウ関数(OVER句 + PARTITION BY): 行を集約してまとめずに、指定したデータのパーティションごとに直接計算を行う手法。
本番運用の実態:リファクタリングから6ヶ月後の比較検証
データサイズ約4.2GBのordersテーブルでベンチマークを実施したところ、性能差は歴然でした。
1. サブクエリと自己結合:手軽に書けるが、実行時にボトルネック化
開発初期は手軽さからサブクエリを選びがちですが、その代償としてデータベースに甚大な負荷がかかります。
- I/Oの過負荷: 相関サブクエリにより、ストレージエンジンは何百万回ものフルテーブルスキャン($O(N^2)$)を強いられます。その結果、バッファキャッシュは瞬く間に枯渇します。
- メンテナンス性の悪化: 前後の注文値を取得するだけで、同じテーブルを3回も自己結合する必要がありました。SQL文は1ページを埋め尽くすほど長大化し、他のエンジニアにとって可読性・保守性が極めて低いコードになってしまいます。
2. ウィンドウ関数:1回のスキャンで完了し、レイテンシが激減
チームですべてのクエリをウィンドウ関数へ移行したところ、クエリの実行時間は28秒からわずか340msへと短縮されました。
- 大きなメリット: PostgreSQLがテーブルをスキャンするのは1回のみです(Single Pass Scan)。データはメモリ(work_mem)上に展開されてその場で算出されます。SQL文の行数も半分に削減され、ロジックが明快になりました。
- 注意すべきポイント: ウィンドウ関数は処理順序上、
WHERE、GROUP BY、HAVINGの後に評価されます。そのため、WHERE句の中でウィンドウ関数を直接使って絞り込むことはできません。絞り込みを行う場合は、CTE(WITH ... AS)や外側のサブクエリでラップする必要があります。
ウィンドウ関数はいつ使うべきか?
判断に迷ったときのクイックルール:
- 行を集約・集約結果のみにまとめたい場合(顧客グループごとに1つの集計値だけが必要な場合):
GROUP BYを選択。 - 各行の詳細をそのまま保持しつつ、計算列を追加したい場合(注文の順位付け、直前レコードの取得、累積値の計算など):迷わず
ウィンドウ関数(Window Functions)を選択。
PostgreSQLでのハンズオン実践
注文シナリオをシミュレートするためのテストテーブルを準備します。
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT,
amount NUMERIC(10, 2),
order_date DATE
);
INSERT INTO orders (customer_id, amount, order_date) VALUES
(101, 150.00, '2026-01-05'),
(101, 300.00, '2026-01-12'),
(101, 300.00, '2026-02-01'),
(102, 50.00, '2026-01-10'),
(102, 200.00, '2026-01-18'),
(103, 500.00, '2026-02-15');
既存システムからダンプしたCSVファイルがあり、手軽に検証したい場合は、サーバーにデータを漏洩させる心配なくJSONへの変換や構造プレビューができる toolcraft.app/ja/tools/data/csv-to-json を利用するのも便利です。
1. ROW_NUMBER、RANK、DENSE_RANKの違い
これら3つの関数は、ランキング処理の実装時によく混同されがちです。
ROW_NUMBER(): 値が同一であっても考慮せず、1, 2, 3…と一意の連番を付与します。RANK(): 同値の行には同じ順位を割り当てますが、後続の順位はスキップされます(例:1, 2, 2, 4)。DENSE_RANK(): 同値の行に同じ順位を割り当てますが、後続の順位はスキップされず連続します(例:1, 2, 2, 3)。
顧客ごとの注文金額を順位付けしてみましょう。
SELECT
order_id,
customer_id,
amount,
ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS row_num,
RANK() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS rnk,
DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS dense_rnk
FROM orders;
ここでは、PARTITION BY customer_idによって顧客ごとに注文をグループ分けし、そのグループ内でORDER BY amount DESCによって金額の降順にソートしています。
2. グループごとのトップNレコードを取得する
各顧客の最高額の注文を抽出するには、共通テーブル式(CTE)とROW_NUMBER()を組み合わせます。
WITH ranked_orders AS (
SELECT
order_id,
customer_id,
amount,
ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS rn
FROM orders
)
SELECT customer_id, order_id, amount
FROM ranked_orders
WHERE rn = 1;
3. LAGとLEADで前後の行との差分を計算する
前月比(MoM)や購入間隔などを算出する場合、LAGとLEADを使用すると極めてシンプルに記述できます。
LAG(列, offset, default): 前の行の値を取得します。LEAD(列, offset, default): 次の行の値を取得します。
同一顧客における前回の注文金額との差分(増減額)を計算する例です。
SELECT
customer_id,
order_date,
amount AS current_amount,
LAG(amount, 1, 0) OVER(PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
amount - LAG(amount, 1, amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS diff_amount
FROM orders;
4. 累計値(Running Total)の計算
再帰クエリを書く代わりに、SUM()とORDER BYを組み合わせるだけで実現できます。
SELECT
customer_id,
order_date,
amount,
SUM(amount) OVER(
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;
各行において、最初の注文から現在行までの金額が自動的に累積加算されます。SQL文が簡潔になるだけでなく、(customer_id, order_date)のインデックスを有効活用できるため、I/Oボトルネックの発生も防げます。
