히스토리: ANSI SQL:2003이 분석 기능을 도입하기 전에는 희소한 시계열 데이터를 채우기 위해 비효율적인 자체 조인이나 행을 개별적으로 처리하는 절차형 커서가 필요했습니다. LOCF 패턴은 SAS와 R과 같은 통계 패키지에서 유래되었으며, 마지막으로 알려진 관측치를 전달하는 것은 표준 데이터 정리 기술입니다. 데이터베이스 공급자는 나중에 이러한 논리를 SQL에 구현했으며, IGNORE NULLS 절은 특히 이러한 간극을 선언적으로 처리하기 위해 ANSI SQL:2011에서 공식화되었습니다.
문제: 센서 네트워크와 금융 거래 시스템은 전송 실패나 비거래 시간으로 인해 NULL 값을 자주 생성합니다. 단순한 LAG 함수는 즉각적인 이전 값을 반환하므로 NULL일 수 있어 계산된 메트릭에 간극을 만듭니다. 이 문제는 성능을 사각형으로 저하시키는 자체 조인을 사용하지 않고 정렬된 파티션을 통해 가장 최근의 비NULL 값을 찾는 것을 요구합니다.
해결책: IGNORE NULLS 옵션과 파티션 시작부터 현재 행까지 확장되는 프레임 사양을 사용하여 LAST_VALUE 윈도우 함수를 사용하세요. 이 구성은 엔진에게 비NULL 값의 실행 중인 버퍼를 유지하도록 지시하여 NULL을 거슬러 올라가 마지막 유효한 관측치를 검색합니다. IGNORE NULLS가 없는 시스템의 경우, 비NULL 값을 가지고 안정된 그룹을 만들기 위해 COUNT를 사용하지만, 이는 기술적으로 서브쿼리를 포함합니다.
SELECT device_id, reading_time, temperature, LAST_VALUE(temperature IGNORE NULLS) OVER ( PARTITION BY device_id ORDER BY reading_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS locf_temperature FROM sensor_readings;
헬스케어 분석 플랫폼은 웨어러블 장치를 사용하여 당뇨병 환자의 지속적인 포도당 수준을 모니터링합니다. Bluetooth 간섭으로 인해 약 12%의 판독값이 NULL로 도착하지만, 임상 의사들은 주사 조정에 대한 전체 곡선이 필요하며, 보간이 의학적으로 안전하지 않을 수 있습니다. 정확한 LOCF 논리는 중요합니다. 수면 중이나 식사 중의 결측값은 잘못된 저혈당 경고를 유발할 수 있습니다.
해결책 A: 커서 기반 절차적 업데이트. PL/SQL 저장 프로시저가 연대순으로 환자 기록을 반복하며 마지막 유효한 포도당 판독값을 저장하기 위해 세션 변수를 유지하고 NULL 행을 즉시 업데이트합니다. 장점: 윈도우 함수 지원이 없는 구 버전 Oracle과 호환 가능; 명령형 프로그래밍 배경을 가진 개발자들이 쉽게 이해할 수 있습니다. 단점: 행별 처리로 과도한 I/O 및 테이블 잠금 생성; 1천만 행 처리에 45분이 소요되어 실시간 대시보드를 불가능하게 만듭니다.
해결책 B: 상관된 서브쿼리와의 자체 조인. 쿼리는 왼쪽 조인을 수행하여 포도당이 NULL이 아닌 현재 행보다 작은 최대 타임스탬프를 찾아 각 간극에 대해 이전 값을 조회합니다. 장점: 절차적 코드 없이 선언적인 SQL 사용; ANSI SQL-92 준수 시스템에서 작동합니다. 단점: O(n²) 복잡성이 기하급수적 느려짐을 초래하고, 유사한 테이블 스캔이 반복되어 생산 데이터 세트에서 쿼리가 6시간 후 시간 초과됩니다.
해결책 C: IGNORE NULLS가 있는 윈도우 함수. 환자별로 파티션되어 시간별로 정렬된 LAST_VALUE(glucose IGNORE NULLS)를 구현하며, 인덱스를 통해 단일 패스를 활용합니다. 장점: O(n log n) 복잡성이 동일한 1천만 행 데이터 세트에서 28초 만에 실행됩니다. 메모리 사용이 최소화되고 잠금 문제가 없습니다. 단점: ANSI SQL:2011 지원이 필요하며, 기존 PostgreSQL 9.5 인스턴스에서 데이터베이스 업그레이드가 필요합니다.
팀은 성능 개선이 99%로 데이터베이스 업그레이드 비용이 정당하다고 판단한 후 C 해결책을 선택했습니다. 구현으로 인해 실시간 포도당 경고가 가능해지고 서버 CPU 사용량이 94% 감소했습니다. 결과적으로 클리닉은 지연이나 중요한 포도당 급증의 누락 없이 50,000명의 동시 환자를 성공적으로 모니터링했습니다.
질문 1: IGNORE NULLS 없이 LAST_VALUE가 왜 NULL을 반환합니까?
기본적으로 LAST_VALUE는 현재 행을 포함하는 프레임을 평가합니다. 현재 행이 NULL을 포함하고 프레임이 CURRENT ROW까지 확장되기 때문에 함수는 이 NULL을 창의 마지막 값으로 봅니다. 후보자들은 종종 함수가 무한히 뒤로 스캔한다고 잘못 가정하지만, IGNORE NULLS가 없으면 NULL을 유효한 값으로 취급합니다. 창 프레임인 ROWS UNBOUNDED PRECEDING은 현재 행을 포함하므로 LAST_VALUE는 명시적으로 NULL을 무시하도록 지시하지 않는 한 현재 행의 값과 동일합니다.
질문 2: IGNORE NULLS 없이 2011 연도 이전의 ANSI SQL에서 LOCF를 구현하는 방법과 ROW_NUMBER 차이 대신 COUNT를 사용하는 논리적 오류는 무엇입니까?
COUNT(non_null_col) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING)를 사용하여 비NULL 값이 나타날 때만 증가하는 그룹화 식별자를 생성할 수 있습니다. 모든 후속 NULL은 이 수를 공유하여 캐리 그룹을 형성합니다. 후보자는 때때로 ROW_NUMBER() OVER (ORDER BY ...)에서 ROW_NUMBER() OVER (PARTITION BY non_null_flag ORDER BY ...)를 빼는 것을 시도합니다. 이는 비NULL 간격마다 새로운 그룹을 만들어 이전 그룹을 앞으로 확장하는 것보다 실패합니다. COUNT 방법이 유효한 이유는 마지막 알려진 값 기간 전체에 대한 안정적인 식별자를 생성하기 때문입니다.
질문 3: 중복이 있는 타임스탬프에 대해 LOCF에 ROWS 대신 RANGE 프레이밍을 사용할 때 결과가 비결정적일 수 있는 이유는 무엇입니까?
RANGE 프레이밍은 동일한 ORDER BY 값이 있는 행을 동료 그룹으로 그룹화하여 단일 단위로 처리합니다. 여러 센서 판독값이 동일한 밀리초 타임스탬프를 공유하는 경우, RANGE UNBOUNDED PRECEDING은 물리적 순서를 구별할 수 없습니다. 일부 중복이 NULL을 포함하고 다른 일부가 값을 포함하는 경우, 윈도우 함수는 실행 계획에 따라 동료 그룹 중에서 무작위로 선택할 수 있습니다. ROWS 프레이밍은 물리적 행 순서를 처리하여 결정적인 결과를 보장하며 삽입의 특정 순서가 앞으로 전달되는 값을 결정합니다. 이 차이는 마이크로초가 중요한 고주파 거래 데이터에 대해 매우 중요합니다.