PostgreSQLのウィンドウ関数でデータ分析を最適化:実戦で学ぶRANK、ROW_NUMBER、LAG/LEAD

Database tutorial - IT technology blog
Database tutorial - IT technology blog

ネストされたサブクエリ、自己結合(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ボトルネックの発生も防げます。

Share: