質問への回答

質問の歴史: 時間間隔の分析は1970年代以来、リレーショナルデータベースに挑戦してきました。SQLは元々ネイティブな間隔タイプを持たずに設計されていたためです。初期のソリューションはカーソルベースの反復や間隔間のデカルト積に依存しており、結果的に二次的な複雑性をもたらしました。SQL:2003におけるウィンドウ関数の導入とROWS BETWEENのフレーム指定により、効率的な運用集計が可能になり、現代のイベントベースの同時実行計算の基盤となりました。

問題: 最大同時実行数を特定するには、状態変化が発生する正確な瞬間を理解する必要があります。具体的には、予約が始まるときまたは終わるときです。ナイーブなアプローチは、すべての間隔を離散的な時間単位に拡張します（時間スライス）。これは、長期間の滞在にとって計算的に負担となります。コアな課題は、タイムラインの各瞬間を具体化せずに、特定の時点でいくつの間隔が重なっているかを計算することです。

解決策: 離散イベントシミュレーションパターンを採用します。間隔テーブルをイベントストリームに変換し、UNION ALLを使用して、チェックイン（開始）には**+1**、チェックアウト（終了）には**-1**の重みを割り当てます。これらのイベントを時間順にソートし、SUM() OVER (ORDER BY ...) ウィンドウ関数を適用することで、すべての遷移点でのアクティブ数を導出します。この運用集計の最大値がピーク同時実行数を表します。

WITH events AS (
    SELECT check_in AS event_time, 1 AS delta
    FROM reservations
    UNION ALL
    SELECT check_out AS event_time, -1 AS delta
    FROM reservations
),
concurrency AS (
    SELECT 
        event_time,
        SUM(delta) OVER (
            ORDER BY event_time, delta DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS active_count
    FROM events
)
SELECT MAX(active_count) AS peak_concurrency
FROM concurrency;

実生活からの状況

問題の説明: 高級リゾートチェーンは、休日の週末に空きがあると報告されるにもかかわらず、神秘的なオーバーブッキング事件を経験しました。古いクエリは、再帰的なCTEを使用して各予約を個別の夜の行に爆発させることによって毎日の占有率を計算し、1ヶ月の滞在で数百万行を生成していました。大晦日の分析では、このアプローチにより12秒の実行時間が必要となり、予約トランザクションテーブルがデッドロックし、リアルタイムの予約が妨げられました。

解決策A: タイムスライス拡張と集計テーブル。 オペレーションチームは最初にカレンダーテーブルを事前生成し、event_date BETWEEN check_in AND check_outを使用して予約と結合することを提案しました。この方法は、標準のGROUP BY句と互換性のある直感的な日次集計を提供します。利点: ビジネスアナリストにとって概念的に簡単で、既存のBIツールに統合しやすい。欠点: **O(N × D)**行を生成するため、Dは平均的な継続期間であり、指数関数的に増加する。ミリ秒単位の粒度や長期のリースでは壊滅的に失敗し、tempdbのスペースを大量消費します。

解決策B: 物理的パスを持つ間隔ツリー。 シニアアーキテクトは、間隔の境界をインデックス付けするためにネストされたセットを使用してセグメントツリー構造を実装することを提案しました。これにより、対数時間での重なりクエリが可能になります。利点: 頻繁な更新やポイントクエリに最適な理論的複雑性。欠点: ツリーのバランスを維持するために複雑なトリガが必要; 手続き拡張に依存することからANSI SQLの移植性を損ない、本予約スパイク中のOLTPワークロードに害を及ぼす書き込み増幅を導入します。

解決策C: 実行中の合計を持つ時間的イベントストリーム（選択された）。 データベースチームはイベントベースのアプローチを採用し、各予約の境界をデルタ操作として扱いました。これにより、数百万の爆破行から正確に2Nイベント（各予約の開始と終了）にデータセットが削減されました。利点: ソート操作によって支配されるO(N log N)の複雑性、一定のメモリフットプリント、そしてPostgreSQL、Oracle、SQL Server全体での純粋なANSI SQL互換性。欠点: 同時イベントの慎重な処理が必要であり、追加の結合がない限り、ピークに寄与した特定の予約を特定できません。

結果: クエリの待機時間は12秒から45ミリ秒に短縮されました。分析から、真のボトルネックは部屋の在庫（500ユニット）ではなく、エレベーターの能力であることが明らかになりました。320人のゲストが同時に午後6時にチェックインを試みました。この洞察により、新しいウィングを構築するのではなく、段階的なチェックインティアを実装することになり、200万ドルの資本支出を節約し、デッドロックを排除しました。

候補者が見落としがちな点

なぜ解決策が特にORDER BY event_time, delta DESCを必要とするのか、deltaの二次ソートを省略した場合に何が起こるのか？

候補者はしばしば共有タイムスタンプでの境界条件の意味を無視します。一人のゲストが午前10時00分にチェックアウトし、別のゲストが午前10時00分にチェックインした場合、処理順序によって部屋が二人のゲストによって同時に占有されているかどうかが決まります。delta DESCでソートすることによって、同じタイムスタンプで**-1**（出発）が**+1**（到着）よりも先に処理されることが確保されます。この二次ソートがない場合、運用集計は一時的に減少し、その後急増し、前の状態が実際には高かったときにファントムピークを記録する可能性があります。この微妙な順序付けは、実稼働システムでのオーバーブッキングの脆弱性を引き起こすオフバイワンエラーを防ぎます。

ピーク同時実行数の瞬間にアクティブな特定の予約を特定するために、このクエリをどのように修正しますか、単にカウントするのではなく？

ほとんどの候補者は同じCTE内でフィルタリングを試み、ピークが単一のポイントではなく連続した間隔にまたがる可能性があることを認識しません。堅牢なアプローチには、まずactive_countが最大値に等しいタイムスタンプをサブクエリまたはCTEを使用して分離し、その後オーバーラップ述語r.check_in <= peak.event_time AND r.check_out > peak.event_timeを使用して元の予約テーブルに戻す必要があります。候補者は、ピークのプラトーが複数のイベント遷移にまたがる可能性があるため、重複した予約リストを避けるためにDISTINCTまたはEXISTS句が必要であると見落とします。

ビジネスルールが変更されてチェックアウト時間が含まれる場合（ゲストは午後11時59分まで部屋を占有します）、これがイベントの重み付けにどのように影響しますか？

候補者は間隔の境界意味論を見過ごすことがよくあります。包含的なエンドポイント[開始, 終了]は、ある予約が終了し、別の予約が同じ日に始まるときに重なりを引き起こします。この解決策は、チェックアウト時間に無限小のイプシロン（または次の離散時間単位）を追加して、包含的な境界を排他的に変換するか、運用集計ロジックを維持しながらcheck_out >= event_timeを使用するように結合ロジックを調整する必要があります。この調整を行わないと、離散的なイベントモデルが半開区間から閉区間に変更され、アルゴリズムが存在しない衝突を報告し、高い回転期間中に真の収容数を正確に1ユニット過小評価することになります。