重複する契約カバレッジ期間を効果的な連続ブロックに統合する際、ANSI SQLのウィンドウ関数を使用してこれらのintervalを非重複の整然とした範囲にどのようにマージしますか？

質問への回答

質問の歴史。 重複する時間間隔を統合する必要性は、アレンの区間代数（1983年）および初期のリレーショナルデータベースにおける時間データベースの研究に起源を持っています。保険システム、ホテル予約プラットフォーム、リソーススケジューリングアプリケーションは、複数のカバレッジ期間、予約、またはメンテナンスウィンドウが重なり合い、それを非重複の連続ブロックに正規化する必要があるため、この課題に頻繁に直面します。単純な集計とは異なり、この操作は順序と継続性の理解を要求し、高度なANSI SQLウィンドウ関数の習熟度を測る標準的なテストとなります。

問題の説明。 start_dateとend_date列で定義された日付範囲のテーブルが与えられた場合、目的はすべての重複または隣接するintervalを非重複の範囲の最小集合にマージすることです。手続き的アプローチでは、現在のマージ範囲と各行を比較するために実行中のバッファを維持しますが、これはSQLのセットベースのパラダイムに違反します。コアの難しさは、自己結合や再帰的CTEなしで継続性の「アイランド」を特定することにあります。特に、遷移的な重なりが存在する場合（範囲AがBと重なり、BがCと重なり、AとCは直接接触しない）に困難です。

解決策。 同じパーティション内のこれまでのすべての行の最大end_dateと現在の行のstart_dateを比較して、各新しいアイランドの開始を検出するために、ANSI SQLウィンドウ関数を利用します。start_dateが前の最大end dateを超える場合、新しいアイランドが始まります。そうでない場合、現在の行は既存のアイランドを延長します。これらの「区切り」フラグの累積をisland_idとして割り当て、次にこの識別子でグループ化して統合されたmin(start_date)とmax(end_date)を計算します。このアプローチは、単一通過のソートと集約を通じてO(n log n)の複雑さを実現します。

生活からの状況

問題の説明。 多国籍医療提供者は、患者が複数の重複する保険ポリシーを保持する請求処理データベースを維持していました。第一のカバレッジは1月1日から3月31日まで、第二は2月15日から4月15日まで、第三は5月1日から開始されていました。既存のシステムは、これらを別々のアクティブ期間として扱うため、重複した請求拒否を生成していました。ビジネスは、1月1日から4月15日までの連続したカバレッジブロックおよび5月の延長の統合ビューを必要としました。監査証跡を元のポリシーレコードから保存しながら、「重複支払いなし」ルールを強制する必要がありました。

解決策1：手続き型カーソルベースの反復。 初期の提案では、start_dateで順序付けられたカーソルを使用したストアドプロシージャを利用し、変数@current_startと@current_endを維持しました。各行について、start_date ≤ @current_end の場合、コードは@current_endをmax(@current_end, end_date)に更新しました。そうでない場合は、現在の範囲を出力し、変数をリセットしました。利点：命令的背景を持つ開発者にとって概念的に明快であり、ステップバイステップでデバッグが容易です。欠点：PL/pgSQLまたはT-SQLの手続き拡張が必要; O(n)メモリで行ごとに実行されますが、I/O性能は悪い; 任意の準拠したエンジンで実行できる純粋な宣言的ANSI SQLの要件に違反します。

解決策2：自己結合による遷移閉包の検出。 別のアプローチは、t1 JOIN t2 ON t1.start_date <= t2.end_date AND t1.end_date >= t2.start_dateを行う自己結合を実行し、直近の重なりを見つけ、その後に重なりのグラフを歩き、連結成分を識別するために再帰的CTEを使用しました。利点：理論的に正しく複雑な遷移関係を処理しますが、ウィンドウ関数は使用しません。欠点：再帰前にO(n²)の中間行を生成します; 大規模データセットでは計算的に爆発的です; 再帰CTEに依存し、ANSI SQL規格ですが、この特定の線形秩序問題にはウィンドウ関数よりもパフォーマンスが劣ります。

解決策3：ウィンドウ関数のギャップ検出（選択された）。 チームは純粋なウィンドウ関数ソリューションを実装しました：is_new_island = CASE WHEN start_date > MAX(end_date) OVER (PARTITION BY patient_id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN 1 ELSE 0 ENDとフラグ付けし、次にisland_id = SUM(is_new_island) OVER (PARTITION BY patient_id ORDER BY start_date)を計算しました。最終的な集約はpatient_id, island_idでグループ化しました。利点：ANSI SQL標準構文を利用した単一通過実行; ソートに制限されたO(n log n)の複雑さ; 実行中の最大値を通じて遷移的重なりを暗黙的に処理します。欠点：NULLのend dates（無期限のカバレッジ）の取り扱いと隣接intervalのセマンティクスの注意（接触する範囲がマージされるかどうか）を必要とします。

結果。 デプロイにより230万のポリシーレコードが89万の連続カバレッジブロックに統合され、標準ハードウェアで12秒未満で処理されました。45分のカーソルベースのバッチ処理ジョブを置き換えました。クエリはビューとしてカプセル化され、リアルタイムの資格確認を可能にし、次の四半期の99%の重複請求拒否を排除しました。

WITH coverage_flags AS (
    SELECT patient_id, start_date, end_date,
           CASE 
             WHEN start_date > MAX(end_date) OVER (
                 PARTITION BY patient_id 
                 ORDER BY start_date 
                 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
             ) THEN 1 
             ELSE 0 
           END AS is_new_island
    FROM insurance_periods
),
islands AS (
    SELECT patient_id, start_date, end_date,
           SUM(is_new_island) OVER (
               PARTITION BY patient_id 
               ORDER BY start_date
           ) AS island_id
    FROM coverage_flags
)
SELECT patient_id, 
       MIN(start_date) AS consolidated_start,
       MAX(end_date) AS consolidated_end
FROM islands
GROUP BY patient_id, island_id;

候補者がしばしば見逃すこと

隣接するintervalが端点で接触する場合、例えば[1月1-10]と[1月11-20]をどのように処理し、どのような述語の修正が必要ですか？

候補者はしばしば厳密な不等式start_date > previous_end_dateを使用し、隣接するintervalを別々のアイランドとして扱います。医療カバレッジや連続スケジューリングの場合、隣接期間は通常、途切れのないサービスを表し、マージされるべきです。述語はintervalタイプに応じて調整する必要があります：閉区間（開始と終了が含まれる）の場合、start_date > previous_end_date + INTERVAL '1' DAYを使用します。半開区間[start, end)（終了が排他的）の場合、条件start_date > previous_end_dateは自然に隣接をマージします。なぜなら、1月11日は1月11日に等しいからです。ANSI SQLは区間算術を直接サポートしているため、解決策にはCASE WHEN start_date > MAX(end_date) OVER (...) + INTERVAL '1' DAY THEN 1 ELSE 0 ENDが必要です。

MAX(end_date)ウィンドウ関数がNULL値を含む入力で不正確な島の境界を生成するのはなぜですか？

ANSI SQLの集計ウィンドウ関数のようなMAX()は、フレーム内のNULL値を無視します。ポリシーに終了日がない場合（NULLが「現在」を示す）、MAX(end_date)は前の行で最新の非NULL日付を返し、その後のintervalが新しいアイランドを開始すべきであるインデファイナイトギャップの後にマージされる可能性があります。候補者はNULLが特別な処理を必要とすることを認識しなければなりません。事前CTEでフィルタリングするか、COALESCE(end_date, DATE '9999-12-31')を使用して無期限のカバレッジを無限に延長します。あるいは、CASE WHEN end_date IS NULL THEN 0 ELSE 1 ENDロジックを使用してNULLを強制的な区切りとして扱い、次の行が新しい島を開始するようにします。

このロジックを、元の原子性を失うことなく、patient_idとinsurance_typeの各組み合わせごとにintervalを別々に統合するように拡張するにはどうすればよいですか？

多くの候補者は手動でパーティションを設定するサブクエリや自己結合を試みます。正しいアプローチは、ANSI SQLウィンドウ関数でPARTITION BY句を活用することです。MAX(end_date)とSUM(is_new_island)の計算でフレームの仕様をPARTITION BY patient_id, insurance_typeに修正します。これにより、各異なるグループに対して実行中の最大とアイランドIDカウンターがリセットされ、パーティション全体でO(n log n)のパフォーマンスが維持されます。正しくパーティションを設定しないと、一人の患者のタイムラインにギャップがある場合に、別の患者に新しいアイランドが不適切にトリガーされ、統合ロジックが破損します。