중복 계약 적용 기간을 효과적인 연속 블록으로 통합하는 맥락에서, 이러한 간격을 서로 겹치지 않는 비연속 범위로 병합하려면 절차적 루프 없이 철저히 ANSI SQL 윈도우 함수를 사용하여 어떻게 해야 합니까?

질문에 대한 답변

질문의 역사. 중복되는 시간 간격을 통합할 필요성은 앨런의 구간 대수(1983)와 초기 관계형 데이터베이스의 시간 데이터베이스 관련 연구에서 발생했습니다. 보험 시스템, 호텔 예약 플랫폼, 리소스 스케줄링 애플리케이션은 여러 적용 기간, 예약 또는 유지 관리 창이 겹칠 때 이 문제에 자주 직면하며, 이를 서로 겹치지 않는 연속 블록으로 정규화해야 정확한 가용성 보고 및 청구를 위해 요구됩니다. 간단한 집계와는 달리, 이 작업은 순서와 연속성을 이해해야 하므로 고급 ANSI SQL 윈도우 함수의 숙련도를 테스트하는 일반적인 시험입니다.

문제. start_date와 end_date 열로 정의된 날짜 범위의 테이블이 주어질 때, 목표는 모든 겹치는 또는 인접한 간격을 최소한의 비겹치는 범위 집합으로 병합하는 것입니다. 절차적 접근 방식은 현재 병합된 범위와 각 행을 비교하며 실행 중인 버퍼를 유지하지만, 이것은 SQL의 집합 기반 패러다임을 위반합니다. 핵심 어려움은 자기 조인이나 재귀 CTE 없이 연속성의 "섬"을 식별하는 것으로, 특히 전이적 겹침이 존재할 때 (범위 A가 B에 겹치고, B가 C에 겹치지만 A와 C는 직접적으로 접촉하지 않음) 그렇습니다.

해결책. ANSI SQL 윈도우 함수를 활용하여 현재 행의 start_date를 동일한 파티션 내의 모든 이전 행의 최대 end_date와 비교하여 각 새로운 섬의 시작을 감지합니다. start_date가 이전 최대 종료일을 초과할 때 새 섬이 시작되고, 그렇지 않으면 현재 행이 기존 섬을 연장합니다. 이러한 "중단" 플래그의 실행 합계를 island_id로 할당한 후, 이 식별자로 그룹화하여 통합된 min(start_date) 및 max(end_date)를 계산합니다. 이 접근 방식은 단일 패스 정렬 및 집계를 통해 O(n log n) 복잡도를 달성합니다.

실제 사례 공유

문제 설명. 다국적 의료 제공업체는 환자가 여러 겹치는 보험 정책을 보유했던 청구 처리 데이터베이스를 유지했습니다—1월 1일부터 3월 31일까지의 기본 보장, 2월 15일부터 4월 15일까지의 보조 보장, 그리고 5월 1일에 시작되는 제3 보장. 기존 시스템은 이를 별도의 활성 기간으로 처리하여 중복 청구 거부를 발생시켰습니다. 비즈니스는 "중복 결제 금지" 규칙을 시행하면서 원래 정책 기록의 감사 추적을 유지하는 통합된 보기를 요구했습니다.

해결책 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 종료일(무기한 적용)에 대한 주의 깊은 처리와 인접한 간격의 의미(접촉 범위가 병합되는지 여부)가 필요합니다.

결과. 배포는 230만 개의 정책 레코드를 890,000개의 연속 적용 블록으로 통합하는 데 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;

지원자들이 자주 놓치는 점

시작 날짜와 종료 날짜가 같은 인접한 간격([1월 1일부터 10일]과 [1월 11일부터 20일])을 어떻게 처리하나요, 어떤 조건 변경이 필요합니까?

지원자들은 종종 엄격한 불평등 start_date > previous_end_date를 사용하여 인접한 간격을 별도의 섬으로 처리합니다. 의료 보장이나 지속적인 일정의 경우, 인접 기간은 일반적으로 끊김 없는 서비스를 나타내며 병합되어야 합니다. 조건은 간격 유형에 맞춰 조정해야 합니다: 닫힌 간격(시작 및 종료 포함)의 경우, start_date > previous_end_date + INTERVAL '1' DAY를 사용합니다. 반면, 반열린 간격 [start, end) (여기서 종료는 독점적)의 경우, 조건 start_date > previous_end_date는 본래의 병합 방식을 유지합니다. ANSI SQL은 간격 산술을 직접 지원하므로, 솔루션은 CASE WHEN start_date > MAX(end_date) OVER (...) + INTERVAL '1' DAY THEN 1 ELSE 0 END가 필요합니다.

NULL 값을 포함하는 입력에 대해 MAX(end_date) 윈도우 함수가 잘못된 섬 경계를 생성하는 이유는 무엇입니까?

ANSI SQL 집계 윈도우 함수는 프레임 내에서 NULL 값을 무시합니다. 정책에 종료 날짜가 없고 NULL이 "현재"를 의미하는 경우, 이전 행의 MAX(end_date)은 최신 비-NULL 날짜를 반환하여 무기한 간격 뒤에 시작해야 하는 후속 간격을 병합할 수 있습니다. 지원자는 NULL을 특별한 방식으로 처리해야 한다는 것을 인식해야 합니다: 이를 미리 CTE에서 필터링하거나, COALESCE(end_date, DATE '9999-12-31')를 사용하여 무기한 적용을 무한대로 간주합니다. 또는 NULL을 강제로 중단으로 처리하여 CASE WHEN end_date IS NULL THEN 0 ELSE 1 END 논리를 사용하여, 다음 행이 새 섬을 시작하도록 보장할 수 있습니다.

이 논리를 여러 차원 포장으로 확장하여 각 환자 ID 및 보험 유형 조합에 대해 간격을 별도로 통합하되, 원자성을 잃지 않도록 하려면 어떻게 해야 합니까?

많은 지원자들은 수동으로 파티션된 서브쿼리나 자기 조인을 시도합니다. 올바른 접근 방식은 ANSI SQL 윈도우 함수의 PARTITION BY 절을 활용하는 것입니다. 두 가지 모두에서 MAX(end_date)와 SUM(is_new_island) 계산을 수행할 때 PARTITION BY patient_id, insurance_type로 프레임 명세를 수정합니다. 이는 각 별도의 그룹에 대해 실행 중 최대값과 섬 ID 카운터를 재설정하여, O(n log n) 성능을 각 파티션에서 유지합니다. 올바르게 파티셔닝하지 않으면 한 환자의 타임라인의 간격 부족이 다른 환자에 대해 잘못된 섬을 트리거하여 통합 논리를 손상시킬 수 있습니다.