질문에 대한 답변

역사적으로, 데이터 엔지니어들은 누락된 센서 판독값을 처리하는 문제에 직면했습니다. 기록을 삭제하거나 상수 보간을 사용하는 두 가지 방법 모두 분석 결과를 왜곡시킵니다. 선형 보간은 두 개의 알려진 데이터 포인트 사이의 직선 경로를 가정하는 지속적인 물리적 프로세스에 대해 통계적으로 선호되는 방법으로 등장했습니다.

이 문제는 유순한 시퀀스에서 NULL 값을 재구성하는 것으로, 주변 유효 측정치의 시간적 거리 기반으로 비례 값을 계산해야 합니다. 이는 절차적 루프나 테이블을 자기 조인하지 않고, 순수 집합 기반 논리를 유지하여 수행해야 합니다.

해결책은 경계 앵커를 설정하기 위해 조건부 윈도우 함수를 활용합니다. MAX는 한계를 벗어난 모든 선행 행까지의 프레임을 가지며 현재 간격 이전의 가장 최근의 비 NULL 값과 타임스탬프를 포착합니다. 반면에 MIN은 한계를 벗어난 모든 후행 행까지의 프레임을 가지며 간격 이후의 다음 비 NULL 값과 타임스탬프를 포착합니다. 보간 공식은 이러한 경계 간 경과된 시간의 비율에 기반하여 가중 평균을 계산합니다.

WITH boundaries AS (
  SELECT 
    device_id,
    reading_time,
    reading,
    MAX(CASE WHEN reading IS NOT NULL THEN reading_time END) 
      OVER (PARTITION BY device_id ORDER BY reading_time 
            ROWS UNBOUNDED PRECEDING) as prev_time,
    MAX(CASE WHEN reading IS NOT NULL THEN reading END) 
      OVER (PARTITION BY device_id ORDER BY reading_time 
            ROWS UNBOUNDED PRECEDING) as prev_val,
    MIN(CASE WHEN reading IS NOT NULL THEN reading_time END) 
      OVER (PARTITION BY device_id ORDER BY reading_time 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as next_time,
    MIN(CASE WHEN reading IS NOT NULL THEN reading END) 
      OVER (PARTITION BY device_id ORDER BY reading_time 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as next_val
  FROM sensor_readings
)
SELECT 
  device_id,
  reading_time,
  COALESCE(
    reading,
    prev_val + (next_val - prev_val) * 
      (EXTRACT(EPOCH FROM (reading_time - prev_time)) / 
       NULLIF(EXTRACT(EPOCH FROM (next_time - prev_time)), 0))
  ) as interpolated_reading
FROM boundaries;

실제 상황

한 제약 회사는 IoT 온도 센서를 사용하여 백신 냉장 저장 장치를 모니터링하여 매 분마다 보고했습니다. 운영 피크 시간 동안 네트워크 혼잡으로 인해 3-5분 동안 간헐적인 NULL 판독값이 발생했습니다. 규제 FDA 준수에 따라 간격 없이 완전한 온도 기록이 요구되었으며, 단순 삭제는 연속 모니터링 프로토콜을 위반했습니다.

NULL 기록 삭제가 처음 고려되었습니다. 이 접근 방식은 관측된 값만 저장하여 사실적 무결성을 유지했습니다. 그러나 이는 중요한 온도 임계값을 초과한 총 시간을 계산하는 것과 같은 기간 기반 집계의 비연속성을 초래하여 데이터 세트를 감사 기준에 비준수하게 만들었습니다.

**마지막 관측값 지속 (LOCF)**은 LAST_VALUE 윈도우 함수를 사용한 계산적으로 간단한 대안으로 평가되었습니다. 성능은 좋았지만, 이 방법은 정전 중에 온도가 일정하게 유지된다고 가정하여 냉장 시스템의 점진적인 열 드리프트라는 열역학 원칙을 위반하여 분산 및 추세 분석에서 상당한 편향을 도입했습니다.

선형 보간이 궁극적으로 선정되었습니다. 이 방법은 측정값 간의 현실적인 열 관성을 모델링하여 1차 경향을 보존하고 네트워크 단속에서 일반적인 단기 간격에 대해 수학적으로 방어적인 추정치를 제공합니다. 비록 단조로운 선형 변화를 가정했지만, 이는 백신 저장의 압축기 사이의 짧고 안정적인 기간 동안 정확한 것으로 입증되었습니다.

이 구현은 Q3 준수 감사 중 99.2%의 누락된 판독값을 성공적으로 재구성했습니다. 보간된 데이터 세트는 물리적으로 기록된 값과 0.1°C 이내의 온도 변동을 유지하여 비싼 하드웨어 업그레이드나 중복 센서 네트워크 없이 FDA 요구사항을 충족했습니다.

후보자들이 자주 놓치는 점

어떻게 하면 간격의 맨 처음이나 맨 끝에서 발생하는 NULL 값을 처리할 수 있나요? 이 경우 보간을 위한 경계 값이 하나만 존재합니다.

후보자들은 종종 prev_val 또는 next_val이 정의되지 않은 경우 선행 또는 후행 간격에 대해 계산이 NULL을 반환하는 것을 고려하지 않고 보간 공식을 구현합니다. 해결책은 CASE 표현식으로 논리를 포장하는 것입니다: prev_time IS NULL인 경우는 next_val을 사용하고 (후방 외삽); next_time IS NULL일 경우는 prev_val을 사용하고 (전방 외삽 또는 LOCF); 그렇지 않으면 전체 보간 공식을 적용합니다. 이를 통해 쿼리가 전체 데이터 세트에 결과를 반환할 수 있도록 합니다.

표현식 (next_val - prev_val) / (next_time - prev_time)가 엄격한 ANSI SQL에서 실패할 가능성이 있는 이유는 무엇이며, 숫자 정확성을 보장하는 수정 사항은 무엇인가요?

ANSI SQL 날짜 및 시간 수학은 INTERVAL 유형을 반환하며, 숫자 스칼라와 간격 간의 산술 나누기를 시도하거나 간격과 소수를 혼합하는 것은 유형 불일치 오류를 발생시킵니다. 또한 정수 나누기는 분수 초를 잘라내어 정밀도를 파괴합니다. 후보자는 EXTRACT(EPOCH FROM (next_time - prev_time))를 사용하여 숫자 표현을 얻어야 합니다. 또한 연속 유효 판독 값이 배치 삽입으로 인해 동일한 타임스탬프를 공유할 때 0으로 나누는 오류를 방지하기 위해 분모를 NULLIF(..., 0)으로 포장해야 합니다.

이 윈도우 함수 접근 방식과 상관된 서브쿼리를 사용하는 자기 조인 방법 간의 기본 계산 복잡성 차이는 무엇이며, 이 차이가 고주파 스트리밍 데이터 세트에 중요한 이유는 무엇인가요?

윈도우 함수 접근 방식은 O(n log n) 시간에 실행되며, 이는 정렬 작업에 의해 지배됩니다. 이 접근 방식은 파티션 크기에 비례하여 선형 메모리 사용을 유지합니다. 반면에 스칼라 서브쿼리가 포함된 자기 조인 접근 방식(예: MIN(time) WHERE time > current 사용)은 각 행이 이웃을 찾기 위해 테이블을 스캔하므로 O(n²)로 저하됩니다. 이는 비효율적인 중첩 루프 조인을 생성합니다. 수백만 개의 행을 생성하는 고주파 텔레메트리의 경우, 윈도우 함수 방식은 정렬된 인덱스 스캔과 단일 패스 실행을 활용하는 반면, 자기 조인은 카르테시안 곱과 메모리 유출을 초래합니다. 후보자들은 종종 큰 파티션에 대해 UNBOUNDED FOLLOWING 프레임이 디스크 스풀링이 필요할 수 있다는 것을 간과하지만, 이는 여전히 이차 복잡도에 비해 점근적으로 우수합니다.