질문에 대한 답변

이력: 시간적 데이터 웨어하우징에서 가장 최근 관측값 전파(LOCF) 기법은 결측값 보간에 우세하며, 이전의 유효 레코드를 사용하여 간격을 채운다. 그러나 특정 분석 도메인—예를 들어, 장내 금융 거래에 대한 기말 조정을 적용하거나 실험실 확인을 이전의 임시 진단으로 되돌리는 경우—에서는 역의 다음 관측값 후방 전파(NOCB) 접근 방식이 필요하다. 역사적으로 NOCB는 상관 하위 쿼리 또는 절차적 커서를 통해 구현되었으며, 두 방법 모두 O(n²) 복잡성을 가지고 최신 집합 기반 최적화를 활용하지 못한다.

문제: 완전 정렬된 시퀀스(예: event_time)가 주어질 때, 각 NULL 값은 시퀀스에서 후에 발생하는 가장 가까운 비-NULL 값으로 대체되어야 한다. 유효 레코드 앞에 있는 연속적인 NULL 값은 동일한 후속 값을 가져야 한다. 표준 함수인 LEAD()는 즉각적인 다음 행만 접근할 수 있어, 비-NULL 앵커 앞에 여러 개의 연속적인 NULL이 있을 경우 실패한다. 자기 조인과 재귀 CTE는 성능 제약으로 금지된다.

해결책: 해결책은 COUNT(expression)의 NULL 무시 의미론을 활용한다. 현재 행에서 파티션의 끝까지 비-NULL 값을 세어(ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 두 개의 비-NULL 앵커 사이의 모든 행에 대해 동일한 안정적인 "버킷 식별자"를 생성한다. 각 버킷 내에서 MAX(val)—또한 NULL을 무시하여—앵커 값을 검색하고 해당 그룹의 모든 행에 전파한다.

WITH bucketed AS (
  SELECT 
    record_id,
    event_time,
    status_code,
    COUNT(status_code) OVER (
      ORDER BY event_time, record_id
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS bucket_id
  FROM audit_log
)
SELECT 
  record_id,
  event_time,
  COALESCE(
    MAX(status_code) OVER (
      PARTITION BY bucket_id 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ),
    'UNKNOWN'
  ) AS confirmed_status
FROM bucketed;

실제 상황

맥락 및 문제 설명: 고주파 거래 회사는 마이크로초 수준의 주식 거래를 캡처하는 execution 테이블을 유지한다. 거래소 보고 프로토콜로 인해, 주어진 1분의 최종 "통합 가격"은 분이 끝난 후 30초 후에 도착하며, 경계에서만 기재된다(예: 14:30:00.000). 규제 TWAP (시간 가중 평균 가격) 계산을 위해 이 1분의 모든 밀리초는 최종 통합 가격을 반영해야 하며, 모든 이전 14:29:00.000 - 14:29:59.999 기록을 다시 채워야 한다. 일일 볼륨은 5000만 개 이상의 행에 달하며, 배치 시간은 10분이다.

해결책 1: 상관하는 스칼라 하위 쿼리. 이 접근 방식은 각 행에 대해 consolidated_price IS NOT NULL인 미래 행의 MIN(event_time)을 찾기 위해 스칼라 하위 쿼리를 사용한 다음, 해당 가격을 검색하기 위해 조인한다.

장점: 절차적 배경이 있는 개발자에게는 개념적으로 간단하다.

단점: O(n²) 비교를 수행한다. 생산 데이터에서는 쿼리 실행 시간이 45분을 초과하여 배치 시간을 위반했다. 연속적인 NULL을 처리하려면 추가 로직이 필요하여 복잡성과 오류율이 증가한다.

해결책 2: 재귀 CTE Traversal. 재귀 CTE가 행을 따라 비-NULL 가격을 후방으로 전파하며 또 다른 비-NULL을 만날 때까지 진행한다.

장점: 모든 ANSI SQL 준수 데이터베이스에서 작동할 것을 보장한다.

단점: 재귀 CTE는 많은 엔진(예: PostgreSQL)에서 행을 순차적으로 처리하여 단일 스레드 실행과 깊은 파티션에서의 스택 오버플로우를 유발할 수 있다. 벤치마크에서는 높은 메모리 압박으로 20분의 실행 시간을 보여주어, 생산 SLA에는 적합하지 않다.

해결책 3: 윈도우 함수 버킷화 (선택된 방법). COUNT와 MAX 패턴을 구현한다. 후방 조회 COUNT는 동일한 미래 값을 요구하는 모든 행에 대해 동일한 버킷을 만들며, MAX는 그 버킷 내에서 해당 값을 전파한다.

장점: 완전히 집합 기반이며 병렬화 가능하며 정렬 작업으로 인해 O(n log n) 시간에 실행된다. 볼륨에 따라 선형적으로 확장 가능하며, PostgreSQL, SQL Server, Oracle, DB2 전반에 걸쳐 표준 ANSI SQL을 사용한다.

단점: 데이터에 대해 두 번의 패스를 요구하지만(첫 번째는 CTE, 두 번째는 외부 쿼리) 현대의 최적화기는 종종 이를 결합한다. 전체 정렬을 요구하며, 중복 타임스탬프는 결정성을 보장하기 위한 동점 해제 열을 요구한다.

결과: 파이프라인 실행 시간이 5000만 행 데이터스트에서 45분에서 8초로 단축되었다. 회사는 불안정한 Python 백필 스크립트를 제거하여 인프라 복잡성을 줄이고, 규제 보고서가 준수 시간 내에 생성되도록 보장했다.

후보자가 종종 놓치는 점

그룹 키를 구성할 때 COUNT(column)을 사용하는 이유는 무엇인가요? COUNT(*) 또는 ROW_NUMBER()를 사용하지 않고?

많은 후보자가 직관적으로 COUNT(*) 또는 ROW_NUMBER()를 사용하여 데이터를 분할할 수 있다고 생각한다. COUNT(*)는 NULL 상관없이 모든 행을 세므로, 역 프레임 내 각 행에 대해 고유하며 단조롭게 변하는 값을 생성하여 안정적인 그룹 형성을 방해한다. ROW_NUMBER()는 각 행에 고유 식별자를 할당하여 마찬가지로 그룹을 파괴한다. 오직 COUNT(column) 만이 비-NULL 값이 나올 때만 증가하여 모든 이전 NULL에 동일한 "버킷 ID"를 할당한다. 이 구별은 집계 윈도우 함수의 NULL 무시 의미론을 활용하여 절차적 논리 없이 "앞을 보는" 기능을 시뮬레이션하는데 중요하다.

파티션이 끝날 때 트레일링 NULL 값이 있다면 쿼리는 어떻게 작동하며, 미래 관측이 존재하지 않을 때 결 determinist 를 보장하기 위해 어떤 수정을 해야 하나요?

정렬된 파티션의 마지막 행이 NULL인 경우, COUNT(status_code)는 해당 행에 대해 0으로 평가된다. 따라서 MAX(status_code)는 NULL을 반환하는데, 이는 논리적으로 맞다—후방으로 전파할 수 있는 미래 관측이 없기 때문이다. 후보자는 종종 이 점을 후속 비즈니스 로직에서 처리하는 것을 잊는다. 기본값(예: 정적 자리 표시자 또는 외부 조회에서의 값)을 제공하기 위해 결과를 COALESCE로 감싸야 한다. 또한 데이터 품질 모니터링을 위해 "채워진 NULL"과 "채울 수 없는 NULL"을 구분해야 하므로, 원본 값과 채워진 값을 비교해야 한다: CASE WHEN status_code IS NULL AND bucket_id = 0 THEN 'UNCONFIRMED' END.

ORDER BY 절에 중복 값이 포함되면 어떤 결정론적 문제가 발생하며, ROWS에서 RANGE로 전환하는 것이 문제를 어떻게 악화시키나요?

정렬 키에 중복(타이) 값이 포함된 경우 창 프레임 정의가 모호해진다. ROWS(물리적 오프셋)를 사용하면 테이블의 물리적 순서에 따라 그룹이 할당되므로, 고유한 추가 정렬 키가 제공되지 않는 한 이는 임의적이다. RANGE(논리적 값 범위)로 전환하면 동일한 정렬 값을 가진 모든 행이 동료로 처리되어 동일한 프레임을 공유하게 된다. 이 해결책에서 여러 행이 동일한 event_time을 공유하는 경우, RANGE는 잘못하게 비-NULL 행과 NULL 행을 동일한 타임스탬프에서 그룹화하거나 예측할 수 없게 그룹을 나누게 될 수 있다. 후보자는 결정론적 버킷 할당을 보장하기 위해 정렬 절에 고유 키(예: record_id)를 추가해야 한다: ORDER BY event_time, record_id로 모든 ANSI SQL 구현에서 결정론적 버킷 할당을 보장해야 한다.