このパターンは、「as-of join」または「nearest preceding match」として知られており、実行時に有効な最新の見積もりとトレードイベントをペアリングしなければならない金融データベースに起源を持ちます。これは、IoTセンサーのキャリブレーションや従業員の部門履歴など、離散的なイベントと徐々に変化するディメンションを持つ任意のドメインに一般化されます。課題は、集合ベースのパフォーマンスを犠牲にすることなく、時間的ナビゲーションを行うことです。
単純なアプローチは、ORDER BY と FETCH FIRST 1 ROW ONLY を使用した相関スカラーサブクエリを利用しますが、これによりエンジンはすべての行に対してサブクエリを実行しなければならず（RBAR）、O(n²)の複雑度となり、キャッシュのローカリティが悪化します。代わりに、イベントと参照ポイントの間に不等号結合（<=）を用いると、フィルタリングの前にサイズが膨らむ半直積が生成され、大規模なデータセットでディスクスピルを引き起こす可能性があります。どちらのアプローチも、数百万の行を処理する際にタイムアウトのリスクがあります。
確実な解決策は、タイムスタンプキーに対する不等号結合を使用し、次にイベントIDでパーティション分けされ、参照タイムスタンプで降順に並べられた ROW_NUMBER() ウィンドウ関数を使用します。row_num = 1 に対してフィルタリングを行うことで、最も近い前の一致のみを保持し、この操作を集合ベースのソートおよびフィルタリングに変換します。オプティマイザはハッシュ結合またはマージ結合を使用して実行できます。
WITH matches AS ( SELECT e.event_id, e.event_time, e.reading, r.calibration_value, ROW_NUMBER() OVER ( PARTITION BY e.event_id ORDER BY r.valid_from DESC ) AS rn FROM events e JOIN reference r ON r.sensor_id = e.sensor_id AND r.valid_from <= e.event_time ) SELECT event_id, event_time, reading, calibration_value FROM matches WHERE rn = 1;
製造プラントは、5,000台のセンサーから毎秒振動データを収集し vibration_logs に格納します。各センサーのキャリブレーション係数は sensor_calibrations 内で随時更新され（おおよそ月に一度）、分析チームはそのマイクロ秒で有効なキャリブレーション係数を用いて生の読み取り値を調整する必要がありますが、単純な相関サブクエリではバッチごとに3分以上かかり、取り込みパイプラインがブロックされました。
ソリューションA（相関サブクエリ）： このアプローチは、各振動ログ行の最新のキャリブレーションを個別に取得するために相関スカラーサブクエリに依存します。データベースエンジンは、通常、calibrated_at タイムスタンプに対してB-treeインデックスシークを利用して、単一の一致レコードを見つけるために、外部行ごとにこのサブクエリを評価します。これにより正しい結果は得られますが、オプティマイザがハッシュ結合やマージ結合を使用することを妨げ、ネストされたループを引き起こします。
ソリューションB（ウィンドウ関数を用いた不等号結合）： この方法は、不等号結合と ROW_NUMBER() ウィンドウ関数を組み合わせて、特定のセンサーのイベントパーティション内で各候補キャリブレーションマッチに順次ランクを割り当てます。結合がすべての候補ペアを生成した後、ウィンドウ関数がキャリブレーション時間を降順に並べ替え、ランク1をフィルタリングします。これにより、ロジックがバルク処理に適した集合ベースの操作に変換されます。
ソリューションC（条件付きロジックを用いたユニオンオール）： この戦略では、両方のテーブルを UNION ALL で単一の時間順のストリームにマージし、タイプフラグを付け、次に LAST_VALUE(... IGNORE NULLS) を使用して、後続のイベント行にわたって最後の既知のキャリブレーションを引き継ぐことを試みます。このアプローチは、理論的には各テーブルを一度だけスキャンし、結合爆発が発生しないようにします。
IGNORE NULLS は厳密には ANSI SQL ではない（オプション機能T611）；それなしでは、ロジックが複雑になり、非数値属性に失敗する；統一ストリームのソートが必要になります。選択されたソリューション： ソリューションBが選ばれたのは、PostgreSQL のクエリオプティマイザが Partial Merge Join を Sort 演算子と組み合わせてウィンドウ関数を実行できることが確認されたためです。中間結合の物質化のメモリオーバーヘッドは、1,000万行に対して2GB RAMで許容できると見なされました。さらに、このアプローチは、ソリューションAで見られたネストされたループの非決定論的パフォーマンスを回避しました。
結果： クエリ実行時間は、本番データセットで45秒から1.2秒に短縮されました。パイプラインは、連続取り込みストリームを妨げることなく、リアルタイムで毎時バッチを処理します。これにより、分析チームはわずか5分の遅延でキャリブレーション済みの振動レポートを生成できるようになりました。
なぜ不等号結合とROW_NUMBER()は、相関サブクエリと同様のO(n²)パフォーマンスの影響を受けることがないのか？それにもかかわらず概念的には大きな中間セットを生成するのに？
相関サブクエリは依存しており、外部の各行に対して再評価する必要があり、しばしば ネストされたループ を引き起こします。不等号結合は独立しており、オプティマイザは直積のような結果を生成するためにハッシュ結合またはマージ結合を選択でき、その後ウィンドウ関数を適用します。重要なのは、現代のエンジンが ROW_NUMBER() = 1 のフィルタリングに対して top-N最適化 を実装しているため、パーティションごとに最初の行を見つけた後はソートを停止し、操作を各イベントごとのインデックスシークまたはハッシュプローブに効果的に変換します。
キャリブレーションレコードが最初に存在する前にイベントが発生した場合、どのようにしてそれらが破棄されるのではなくデフォルト値を受け取るようにしますか？
不等号結合（<=）は、結合条件が失敗するため、最小参照時間より前のイベントを本質的に除外します。これらを含めるには、INNER JOIN の代わりに LEFT JOIN を使用し、その後参照値を COALESCE でラップしてデフォルトを代入します。さらに、キャリブレーションテーブルに valid_from = '1900-01-01' とデフォルト係数を持つセンチネル行を追加することで、すべてのイベントに少なくとも1つの前の一致があることを保証できます。これにより、ポストフィルタリングロジックなしで関係の閉包が保証されます。
テーブルを結合することなく、ウィンドウ関数の範囲句だけを使用してこの問題を解決できますか？両データセットが単一の統一テーブルにあると仮定して？
いいえ。RANGE句は、オーダリング列の値に基づいて現在の結果セットの行に対して動作します；結合述語なしに物理的に別のテーブルから値を選択的に照会することはできません。両テーブルを UNION ALL で統一しても、 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW は他のイベントを含むすべての以前の行を含めます。キャリブレーション行のみを分離するためには、 LAST_VALUE で IGNORE NULLS を使用する必要がありますが、これは厳密には ANSI SQL ではありません（オプション機能T611）。したがって、二つの異なる関係ソースを結合する際には、厳密なANSI SQL準拠のために結合操作が必須です。