Posted inデータベース

SQLウィンドウ関数を完全マスター：ROW_NUMBER、RANK、LEAD、LAGの実践活用ガイド

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

火曜日の深夜2時15分。1,400件のアクティブアカウントで請求サービスの照合エラーが発生し、PagerDutyのアラートが一斉に鳴り響きます。上流キューの不具合によって重複した決済Webhookが送信され、二重課金が発生していました。エンジニアリングリードは、朝6時00分の出金バッチ処理が走る前に、2つのデータを必要としています。それは、重複を除去したクリーンなトランザクションリストと、重複呼び出し間の正確なミリ秒単位の時間差です。

ここで安易に複雑な自己結合（セルフジョイン）やネストしたサブクエリを使うと、レプリカのCPU使用率は100%に張り付き、処理完了までに数分を要することになります。しかし、標準的なSQLウィンドウ関数を使えば、1,000万行のデータであっても1回のスキャンでこの問題を解決できます。

クイック修正：5分で重複レコードを切り分ける

重複したWebhook呼び出しがデータベースに大量に流入した際、決済ごとに最も古いレコードのみを保持する必要があります。ROW_NUMBER()と共通テーブル式（CTE）を組み合わせることが、最もスマートな解決策です。

まずは、payment_eventsテーブルに届いたデータを確認してみましょう。

-- 重複レコードの確認
SELECT id, account_id, transaction_id, amount, created_at
FROM payment_events
WHERE created_at >= NOW() - INTERVAL '6 hours'
ORDER BY transaction_id, created_at ASC
LIMIT 5;

元のWebhookを特定して重複を除去するには、行をtransaction_idでパーティション分割し、挿入日時順に並べ替えます。

WITH ranked_payments AS (
    SELECT 
        id,
        account_id,
        transaction_id,
        amount,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY transaction_id 
            ORDER BY created_at ASC
        ) AS event_sequence
    FROM payment_events
    WHERE created_at >= NOW() - INTERVAL '6 hours'
)
SELECT id, account_id, transaction_id, amount, created_at
FROM ranked_payments
WHERE event_sequence = 1;

標準的なGROUP BYでは、一致するすべての行が1つの集計行に折りたたまれてしまいます。それに対し、ウィンドウ関数は出力内の個々の行をそのまま保持しながら、指定したパーティション全体で値を計算します。

基本の仕組み：OVER()句の真の挙動

すべてのウィンドウ関数はOVER()句に依存しています。この句は、結果を計算する前にデータをどのようにスライスし、ソートし、フレーム化するかをデータベースエンジンに指示します。

FUNCTION() OVER (
    PARTITION BY column_name
    ORDER BY sort_column
    [window_frame]
)
  • PARTITION BY: 行を個別のグループ（バケット）に分割します。新しいパーティションに移るたびに計算はリセットされます。
  • ORDER BY: 各パーティション内でどの順序で行を処理するかを正確に制御します。
  • Window Frame: 移動計算の対象とするスライド式の行の境界（現在の行とその前の2行など）を設定します。

ROW_NUMBER vs. RANK vs. DENSE_RANK

インシデント対応中にランキング関数の選択を誤ると、重要な異常値を見落としたり、予期せぬ順位の重複が発生したりする原因になります。顧客の取引データを評価する際の各関数の挙動の違いは以下の通りです。

SELECT 
    account_id,
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
    RANK()       OVER (ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM account_transactions
WHERE transaction_date = CURRENT_DATE;
Account ID Amount ROW_NUMBER() RANK() DENSE_RANK()
ACC-101 $500 1 1 1
ACC-102 $500 2 1 1
ACC-103 $300 3 3 (2をスキップ) 2 (連番を維持)
ACC-104 $150 4 4 3

ページネーションや重複排除のために一意な連番が必要な場合はROW_NUMBER()を使用します。同値のレコードに同じ順位を割り当て、次の順位をスキップさせたい場合はRANK()を選択します。同値の順位を共有しつつ、後続の順位をスキップせずに詰めて採番したい場合はDENSE_RANK()を使用します。

LEADとLAGを使った時間差の計算

先ほどの午前2時のインシデントでは、重複したWebhookが15ミリ秒以内に届いたのか（リトライストーム）、それとも60秒の間隔を空けて届いたのか（キューの再処理ループ）を把握する必要がありました。LAG()を使えば、負荷の高い自己結合を行わずに前の行の値を取得できます。

SELECT 
    transaction_id,
    created_at,
    LAG(created_at, 1) OVER (
        PARTITION BY transaction_id 
        ORDER BY created_at ASC
    ) AS previous_event_time,
    ROUND(
        EXTRACT(EPOCH FROM (created_at - LAG(created_at, 1) OVER (
            PARTITION BY transaction_id 
            ORDER BY created_at ASC
        )))::numeric
    , 3) AS delta_seconds
FROM payment_events
WHERE created_at >= NOW() - INTERVAL '6 hours';

LAG()は前のレコードを参照し、LEAD()は後の行を参照します。どちらもユーザーのセッション滞在時間の監視、ファネルのステップごとの離脱分析、APIのリトライジッターの検証などに欠かせません。

本番での活用パターン：累計値と移動平均

ウィンドウフレームを使うことで、SUM()、AVG()、COUNT()といった標準的な集約関数を、時系列に沿った移動指標へと変換できます。

累計の財務エクスポージャーを追跡する

障害発生中に各トランザクションが計上される際のアカウント全体の債務総額を追跡するには、累計（ランニングトータル）を計算します。

SELECT 
    account_id,
    created_at,
    amount,
    SUM(amount) OVER (
        PARTITION BY account_id
        ORDER BY created_at ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_spent
FROM payment_events
WHERE created_at >= NOW() - INTERVAL '24 hours';

移動平均によるレイテンシスパイクの平滑化

散発的なネットワーク遅延のトラブルシューティングでは、突発的なレイテンシスパイクが生データチャートを歪めてしまうことがあります。直近5リクエストの移動平均を算出することで、実際のトレンドを可視化できます。

SELECT 
    request_id,
    endpoint,
    response_time_ms,
    AVG(response_time_ms) OVER (
        PARTITION BY endpoint
        ORDER BY request_time ASC
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS moving_avg_latency_ms
FROM api_access_logs
WHERE request_time >= NOW() - INTERVAL '1 hour';

障害対応のトリアージでは、クエリ結果をCSVにエクスポートし、ローカルのステージング環境で修正を検証するのが一般的な運用です。モックテスト用にCSV出力をJSONへ手早く変換したい場合は、toolcraft.app/ja/tools/data/csv-to-jsonが便利です。ブラウザのクライアントサイドで動作するため、機密性の高いトランザクションデータが端末の外に送信される心配はありません。

大規模システムにおけるパフォーマンス原則

1. ウィンドウの定義に合わせて複合インデックスを作成する

ウィンドウ関数はソートとハッシュパーティショニングをメモリ上で実行します。クエリでPARTITION BY transaction_id ORDER BY created_atを使用している場合は、その完全な順序で両方のカラムをカバーする複合インデックスを作成します。

CREATE INDEX idx_payment_events_tx_created 
ON payment_events(transaction_id, created_at);

このインデックスが存在することで、PostgreSQLはソート済みデータをディスクから直接読み取れるようになります。1,200万行のテーブルにおいて、実行時間は8.4秒からわずか42ミリ秒へと大幅に短縮されます。

2. WHERE句内でウィンドウ関数を使用しない

SQLエンジンは、ウィンドウ関数を計算する前にWHERE句を評価します。そのため、WHERE ROW_NUMBER() OVER (...) = 1を実行すると構文エラーが発生します。計算をCTEでラップするか、エンジンがサポートしている場合はQUALIFY句を使用してください（Snowflake、BigQuery、Databricksなど）。

-- Snowflake、BigQuery、Databricksでサポート
SELECT id, transaction_id, created_at
FROM payment_events
QUALIFY ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY created_at) = 1;

3. work_memとディスクへのスピル（外部ソート）を監視する

パーティション分割されていない大規模なデータセットでウィンドウ関数を実行すると、PostgreSQLのデフォルトのwork_mem（通常4MBに設定）をあっという間に超過する可能性があります。これが発生すると、PostgreSQLはディスクベースのソートにフォールバックし、EXPLAIN ANALYZEでSort Method: external merge Diskと表示されます。WHERE句に厳格なタイムスタンプフィルターを追加するか、セッション単位で一時的にwork_memを引き上げてソート処理をRAM内に収めるようにしてください。

Share:
Tags: