호텔 예약 시스템에서 체크인 및 체크아웃 타임스탬프를 사용하여 동시에 활성화된 예약의 최대 수를 계산하시오. 시간 분할이나 절차적 루프를 사용하지 않고 순수 ANSI SQL 집합 기반 작업만 사용하여 계산하시오.

질문에 대한 답변

질문의 역사: 시간 간격 분석은 1970년대부터 관계형 데이터베이스에 도전 과제가 되어왔으며, SQL은 원래 기본 간격 유형 없이 설계되었습니다. 초기 솔루션은 커서 기반 반복이나 간격 간의 데카르트 곱을 의존하여 제곱 복잡성을 초래했습니다. SQL:2003의 윈도우 함수 도입과 ROWS BETWEEN 프레임 사양은 효율적인 실행 집계를 가능하게 하였고, 현대 이벤트 기반 동시성 계산의 기초를 마련했습니다.

문제: 최대 동시성을 결정하려면 상태 변화가 발생하는 정확한 순간, 즉 예약이 시작되거나 끝나는 시점을 이해해야 합니다. 단순한 접근 방법은 각 간격을 개별 시간 단위(시간 분할)로 확장하는 것인데, 이는 장기간 체류에는 계산적으로 심각한 부담이 됩니다. 핵심 과제는 모든 순간을 구체화하지 않고 특정 시점에서 얼마나 많은 간격이 겹치는지를 계산하는 것입니다.

해결책: 이산 이벤트 시뮬레이션 패턴을 사용합니다. 간격 테이블을 이벤트 스트림으로 변환하여 각 체크인(시작)에는 +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를 사용하여 각 예약을 개별적인 숙박 일자 행으로 확장하여 일일 점유율을 계산했습니다. 새해 전날 분석에서 이 접근 방식은 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시에 체크인 하려고 했습니다. 이 통찰력으로 새로운 동을 짓는 대신 단계적 체크인 등급을 구현하게 되었고, $2M의 자본 지출을 절약하면서 교착 상태를 제거했습니다.

후보자들이 자주 놓치는 점

왜 해결책이 ORDER BY event_time, delta DESC가 특히 필요하며, delta에 대한 이차 정렬을 생략하면 어떻게 됩니까?

후보자들은 종종 공유 타임스탬프에서 경계 조건 의미를 무시합니다. 한 손님이 정확히 오전 10시에 체크아웃하고 다른 손님이 오전 10시에 체크인할 때, 처리 순서에 따라 방이 두 손님에 의해 동시에 점유되고 있는지 여부가 결정됩니다. delta DESC로 정렬하여 동일한 타임스탬프에서 -1 (퇴거)이 +1 (도착)보다 먼저 처리되도록 합니다. 이 이차 정렬이 없으면 실행 합계가 일시적으로 떨어졌다가 급증하여 이전 상태가 실제로 더 높았던 경우에 환상적인 피크를 기록할 수 있습니다. 이 미묘한 정렬은 실제 시스템에서 초과 예약 취약점으로 이어지는 오프 바이 원 오류를 방지합니다.

피크 동시성 순간 동안 어떤 특정 예약이 활성 상태였는지를 식별하기 위해 이 쿼리를 어떻게 수정하시겠습니까?

대부분의 후보자는 같은 CTE 내에서 필터링을 시도하며 피크가 단일 지점이 아니라 연속적인 간격을 포함할 수 있음을 인식하지 못합니다. 견고한 접근 방식은 두 번의 패스 전략을 요구합니다: 첫째, 서브쿼리나 CTE를 사용하여 active_count가 최대값과 같은 타임스탬프를 분리한 다음, 원래 예약 테이블로 조인하여 중첩 조건 r.check_in <= peak.event_time AND r.check_out > peak.event_time을 사용합니다. 후보자들은 여러 타임스탬프가 동일한 최대 값을 공유할 수 있으므로 피크 고원에서 중복 예약 목록을 피하기 위해 DISTINCT 또는 EXISTS 절을 사용해야 한다는 것을 간과합니다.

비즈니스 규칙이 변경되어 체크아웃 시간이 배타적이지 않고(손님이 오후 11시 59분까지 방을 사용) 포괄적이라면 어떤 수정이 필요하며, 이는 이벤트 가중치에 어떤 영향을 미칩니까?

후보자들은 종종 간격 경계 의미를 간과합니다. 포괄적인 끝점 [start, end]는 한 예약이 끝나고 다른 예약이 같은 날 시작될 때 겹침을 만듭니다. 이 해결책은 포함 경계를 배타적으로 변환하는 것으로, 체크아웃 시간에 무한소 엡실론(또는 다음 이산 시간 단위)을 더합니다. 또는 조인 로직을 조정하여 check_out >= event_time을 사용하되 실행 합계 논리는 그대로 유지합니다. 이를 조정하지 않으면 이산 이벤트 모델이 반닫힌 간격에서 열린 간격으로 변환되어 알고리즘이 실제로 존재하지 않는 충돌을 보고하고 고회전 기간 동안 정확히 하나의 단위로 실제 용량을 과소평가하게 만듭니다.