이 패턴은 as-of join 또는 가장 가까운 이전 일치로 알려져 있으며, 거래 이벤트를 실행 시점에 유효한 가장 최근 인용과 쌍으로 만들어야 하는 금융 데이터베이스에서 유래되었습니다. 이는 IoT 센서 보정 또는 직원 부서 이력과 같이 점진적으로 변화하는 차원을 가진 모든 도메인으로 일반화됩니다. 문제는 세트 기반 성능을 희생하지 않고 시간적 탐색을 수행하는 데 있습니다.
단순한 접근 방법은 ORDER BY와 FETCH FIRST 1 ROW ONLY를 사용하는 상관 스칼라 서브쿼리를 사용하는 것으로, 이는 엔진이 모든 행에 대해 서브쿼리를 실행하도록 강제하여 RBAR로 인해 O(n²) 복잡성과 낮은 캐시 지역성을 초래합니다. 또는 이벤트와 참조 포인트 간의 부등식 조인(<=)은 필터링하기 전에 크기가 폭발하는 반 카르테지안 곱을 생성하여 대규모 데이터 세트에서 디스크 스필을 유발할 수 있습니다. 두 접근 모두 수백만 행을 처리할 때 시간 초과의 위험이 있습니다.
강력한 솔루션은 타임스탬프 키에 대한 부등식 조인을 사용한 다음, 이벤트 ID로 파티셔닝하고 참조 타임스탬프를 내림차순으로 정렬하여 ROW_NUMBER() 윈도우 함수를 사용합니다. row_num = 1을 필터링하면 가장 가까운 이전 일치 항목만 유지되어, 작업이 해시 또는 병합 조인을 사용하여 실행할 수 있는 세트 기반 정렬 및 필터로 변환됩니다.
WITH matches AS ( SELECT e.event_id, e.event_time, e.reading, r.calibration_value, ROW_NUMBER() OVER ( PARTITION BY e.event_id ORDER BY r.valid_from DESC ) AS rn FROM events e JOIN reference r ON r.sensor_id = e.sensor_id AND r.valid_from <= e.event_time ) SELECT event_id, event_time, reading, calibration_value FROM matches WHERE rn = 1;
제조 공장은 매초 5,000개의 센서에서 진동 데이터를 vibration_logs에 수집합니다. 각 센서의 보정 계수는 sensor_calibrations에 불규칙하게 업데이트됩니다(대략 한 달에 한 번). 분석 팀은 매 마이크로초에 활성화된 보정 계수로 모든 원시 판독값을 조정해야 하지만, 단순 상관 서브쿼리는 배치당 3분 이상 소요되었고 데이터 수집 파이프라인을 차단했습니다.
솔루션 A (상관 서브쿼리): 이 접근 방법은 각 진동 로그 행에 대해 최신 보정을 개별적으로 가져오기 위해 상관 스칼라 서브쿼리에 의존합니다. 데이터베이스 엔진은 일반적으로 일치하는 단일 레코드를 찾기 위해 calibrated_at 타임스탬프에 대한 B-트리 인덱스 탐색을 사용하여 이 서브쿼리를 외부 행당 한 번 평가합니다. 이는 올바른 결과를 반환하지만 옵티마이저가 해시 또는 병합 조인을 사용할 수 없게 하고 중첩 루프를 생성합니다.
솔루션 B (윈도우 함수가 있는 부등식 조인): 이 방법은 특정 센서의 이벤트 파티션 내에서 각 잠재적 보정 일치에 순차적 순위를 부여하기 위해 부등식 조인과 ROW_NUMBER() 윈도우 함수를 결합하여 사용합니다. 조인이 모든 후보 쌍을 생성한 후, 윈도우 함수가 이를 보정 시간 내림차순으로 정렬하고 순위 1을 필터링합니다. 이는 논리를 대량 처리에 적합한 세트 기반 작업으로 변환합니다.
솔루션 C (조건 논리를 가진 Union-All): 이 전략은 UNION ALL을 통해 두 테이블을 단일 연대기적 스트림으로 병합하고, 유형 플래그로 표시한 다음 **LAST_VALUE(... IGNORE NULLS)**를 사용하여 이후 이벤트 행을 통해 마지막으로 알려진 보정을 전달하려 합니다. 이 접근 방식은 이론적으로 각 테이블을 한 번씩만 스캔하여 조인 폭발을 피할 수 있습니다.
IGNORE NULLS는 엄격하게 ANSI SQL(선택적 기능 T611)이 아니며, 없으면 논리가 복잡해지고 비숫자 속성에 대해 실패하게 됩니다; 통합 스트림을 정렬해야 합니다.선택한 솔루션: 솔루션 B는 PostgreSQL 쿼리 옵티마이저가 부분 병합 조인과 윈도우 함수에 대한 정렬 연산자를 수행할 수 있음을 확인한 후 선택되었습니다. 중간 조인을 물질화하는 메모리 오버헤드는 1000만 개의 행에 대해 2GB RAM으로 허용 가능하다고 판단되었습니다. 또한, 이 접근 방식은 솔루션 A에서 중첩 루프에서 볼 수 있는 비결정론적 성능을 피했습니다.
결과: 쿼리 실행 시간이 생산 데이터세트에서 45초에서 1.2초로 감소했습니다. 이제 파이프라인은 연속 데이터 수집 스트림을 차단하지 않고 실시간으로 시간 단위 배치를 처리합니다. 이렇게 하여 분석 팀은 오직 5분의 지연으로 보정된 진동 보고서를 생성할 수 있게 되었습니다.
부등식 조인과 ROW_NUMBER()가 상관 서브쿼리와 같은 O(n²) 성능 문제를 겪지 않는 이유는 무엇입니까? 대규모 중간 집합을 생성하더라도 개념적으로는 그렇게 보이지 않습니까?
상관 서브쿼리는 종속적입니다. 각 외부 행에 대해 다시 평가해야 하므로 종종 중첩 루프가 발생합니다. 부등식 조인은 독립적입니다. 옵티마이저는 카르테지안과 유사한 조인을 생성한 다음 윈도우 함수를 적용할 수 있습니다. 중요하게도 현대 엔진은 ROW_NUMBER() = 1 필터에 대해 top-N 최적화를 구현하므로, 각 파티션당 첫 번째 행을 찾은 후 정렬을 중단하여 이 작업을 각 이벤트당 인덱스 탐색 또는 해시 프로브로 전환합니다.
첫 번째 보정 레코드가 존재하기 전에 발생하는 이벤트를 처리할 때 어떻게 기본값을 받도록 하여 버리지 않도록 합니까?
부등식 조인(<=)은 조인 조건이 실패하기 때문에 기본적으로 최소 참조 시간 이전의 이벤트를 제외합니다. 포함시키려면 INNER JOIN 대신 LEFT JOIN을 사용한 다음 참조 값을 COALESCE로 감싸서 기본값으로 대체합니다. 또한 참조 테이블에 valid_from = '1900-01-01'와 기본 계수를 가진 센티넬 행을 추가하면 모든 이벤트에 대해 최소한 하나의 이전 일치 항목이 존재하도록 보장합니다. 이는 포스트 필터링 논리 없이 관계적 폐쇄를 보장합니다.
두 개의 별도 관계 소스를 결합할 때 조인 없이 윈도우 함수의 RANGE 절만 사용하여 이 문제를 해결할 수 있습니까?
아니요. RANGE 절은 정렬 열의 값에 따라 현재 결과 집합의 행에서 작동하며, 조인 술어 없이 물리적으로 별도의 테이블에서 값을 선택적으로 조회할 수 없습니다. 두 테이블을 UNION ALL로 통합하더라도 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW는 모든 이전 행을 포함하며, 다른 이벤트뿐만 아니라 보정 행도 포함됩니다. 오직 보정 행만 분리하려면 LAST_VALUE에 IGNORE NULLS를 사용해야 하며, 이는 엄격하게 ANSI SQL이 아닙니다(선택적 기능 T611) 따라서 두 개의 별도 관계 소스를 결합할 때 조인 작업이 필수입니다.