歴史: 時系列データウェアハウジングでは、最後の観察を前方に運ぶ（LOCF）技術が欠落値の補完において支配的であり、以前の有効なレコードを使用してギャップを埋めます。しかし、特定の分析ドメイン、例えば、日次の照合を intraday 財務取引に適用したり、ラボの確認を以前の仮の診断に逆伝播させたりする必要がある場合、逆の次の観察を後方に運ぶ（NOCB）アプローチが必要です。歴史的に、NOCBは相関するサブクエリや手続き型カーソルを通じて実装されてきましたが、両方とも O(n²) の複雑さを示し、現代の集合ベースのオプティマイザーを活用できません。
問題: 完全に順序付けられたシーケンス（例：event_time）が与えられた場合、各 NULL 値はそのシーケンス内の後に発生する最も近い非 NULL 値で置き換える必要があります。有効なレコードの前に存在する連続する NULL は、同じ後続の値を受け取るべきです。標準関数の LEAD() は、即座の次の行のみを参照し、非 NULL のアンカーの前に複数の連続する NULL が存在する場合に失敗します。自己結合と再帰 CTE はパフォーマンスの制約により禁止されています。
解決策: この解決策では、COUNT(expression) の NULL を無視するセマンティクスを利用します。現在の行からパーティションの終わりまでの非 NULL 値をカウントすることにより（ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING）、二つの非 NULL アンカーの間のすべての行が同一の「バケット識別子」を生成します。それぞれのバケット内で、MAX(val)（これも NULL を無視します）がアンカー値を取得し、そのグループ内のすべての行に伝播します。
WITH bucketed AS ( SELECT record_id, event_time, status_code, COUNT(status_code) OVER ( ORDER BY event_time, record_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS bucket_id FROM audit_log ) SELECT record_id, event_time, COALESCE( MAX(status_code) OVER ( PARTITION BY bucket_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ), 'UNKNOWN' ) AS confirmed_status FROM bucketed;
コンテキストと問題の説明: 高頻度取引会社は、マイクロ秒単位の株式取引をキャプチャする execution テーブルを維持します。取引所報告プロトコルにより、任意の分の最終「統合価格」—クリアリングハウスによって確認される—は、分が終了してから30秒後に到着し、境界でのみスタンプが押されます（例：14:30:00.000）。規制のTWAP（時間加重平均価格）計算のために、その分のすべてのミリ秒は最終統合価格を反映する必要があり、すべての前の 14:29:00.000 - 14:29:59.999 レコードに対して逆補充が必要です。1日のボリュームは5千万人を超え、バッチウィンドウは10分です。
解決策1: 相関スカラサブクエリ。 このアプローチは、各行のためにスカラサブクエリを使用して、consolidated_price IS NOT NULL である将来の行の MIN(event_time) を特定し、それから戻ってその価格を取得します。
長所: 手続き型背景のある開発者にとって概念的に簡単です。
短所: O(n²) の比較を実行します。本番データでは、クエリの実行時間が45分を超え、バッチウィンドウを超えました。複数の連続する NULL を処理するためには、前に飛ばす追加のロジックが必要で、複雑さとエラー率が増加します。
解決策2: 再帰CTEのトラバース。 再帰 CTE は、非 NULL 価格を逆に運び続けます。
長所: 任意の ANSI SQL 準拠データベースで動作が保証されます。
短所: 再帰 CTE は多くのエンジン（例：PostgreSQL）で行を逐次的に処理し、単一スレッドの実行になり、深いパーティションでスタックオーバーフローの可能性があります。ベンチマークでは、メモリの圧力が高く、20分の実行時間が示され、本番SLAにとって不適切となっています。
解決策3: ウィンドウ関数のバケット化（選択された）。 COUNT と MAX パターンを実装します。後向きの COUNT が未来の同じ値を必要とするすべての行の同一バケットを作成し、MAX がその値をバケット内に伝播します。
長所: 完全に集合ベースで、並列処理可能で、ソート操作により O(n log n) 時間で実行されます。ボリュームに対して線形にスケールし、PostgreSQL、SQL Server、Oracle、および DB2 の間でポータブルな標準 ANSI SQL を使用します。
短所: データに対して2回パスが必要ですが（CTE と外部クエリ）、現代のオプティマイザーはこれを結合することがよくあります。完全な順序が必要で、複製されたタイムスタンプには決定論を確保するためにタイブレーカー列が必要です。
結果: パイプラインの実行時間は、5千万人のデータセットで45分から8秒に短縮されました。会社は壊れやすいPythonのバックフィルスクリプトを排除し、インフラストラクチャの複雑さを減らし、規制レポートをコンプライアンスウィンドウ内で生成することを確保しました。
なぜ grouped key を構築する際に COUNT(column) を使用する必要があるのか？ COUNT(*) または ROW_NUMBER() ではないのか？
多くの候補者は、データをセグメント化できると信じて COUNT(*) や ROW_NUMBER() を直感的に使用します。COUNT(*) は NULL に関係なくすべての行をカウントし、後方フレーム内の各行に対して一意で単調に変化する値を生成します。これにより安定したグループの形成が妨げられます。ROW_NUMBER() は各行にユニークな識別子を割り当てるため、同様にグルーピングを破壊します。唯一の COUNT(column) は非 NULL 値に遭遇することによってのみインクリメントし、次の非 NULL 境界までのすべての先行する NULL に同じ「バケットID」を割り当てます。この区別は重要で、集計ウィンドウ関数の NULL を無視するセマンティクスを利用して手続きロジックなしで「先読み」をシミュレートします。
もしパーティションが後に NULL 値で終わると、クエリはどのように動作し、将来の観察が存在しない場合の決定論的な処理を確保するための修正は何ですか？
順序付けられたパーティションの最後の行が NULL である場合、 COUNT(status_code) はそれらの行に対してゼロを評価します。したがって、MAX(status_code) は NULL を返します。これは論理的に正しい—後方に運ぶ未来の観察が存在しません。候補者はこれを下流のビジネスロジックで処理することを忘れることがよくあります。デフォルト値（例：静的なプレースホルダーまたは外部ルックアップからの値）を提供するためには、結果を COALESCE でラップする必要があります。さらに、データ品質モニタリングのために「埋められた NULL」と「埋められない NULL」を区別するためには、元の値と埋められた値を比較する必要があります：CASE WHEN status_code IS NULL AND bucket_id = 0 THEN 'UNCONFIRMED' END。
ORDER BY 句に重複値が含まれている場合、どのような決定論的な問題が発生し、ROWS から RANGE に切り替えることで問題が悪化する理由は何ですか？
注文キーに重複（タイ）が含まれている場合、ウィンドウフレームの定義が曖昧になります。ROWS（物理的オフセット）を使用すると、物理テーブル順に基づいてグループが割り当てられますが、ユニークな二次ソートキーが提供されない限り、この順序は任意です。RANGE（論理値範囲）に切り替えると、同じ順序付け値を持つすべての行は仲間として扱われ、同じフレームを共有します。この解決策では、複数の行が同じ event_time を共有している場合、RANGE は NULL 行を同じタイムスタンプの非 NULL 行と不正にグループ化したり、グループを予測不可能に分割したりする可能性があります。候補者は、すべての ANSI SQL 実装での決定論的なバケットの割り当てを保証するために、ORDER BY 句にユニークなキー（例：record_id）を追加する必要があります：ORDER BY event_time, record_id。