비정형 센서 판독값의 시간 가중 평균을 계산하는 쿼리를 작성하시오. 각 값을 후속 샘플까지 유효한 기간으로 가중치 부여하며, 절차적 논리를 사용하지 않고 ANSI SQL 윈도우 함수만 사용합니다.

질문에 대한 답변

질문의 역사

시간 가중 평균은 산업 IoT 및 금융 시계열 분석에서 중요한 메트릭으로 등장했습니다. 단순 산술 평균은 센서 값이 다음 측정까지 지속되기 때문에 현실을 왜곡합니다. ANSI SQL:2003 표준 이전에는 이러한 평균을 계산하기 위해 절차적 커서나 비용이 많이 드는 자기 조인이 필요했습니다. 이러한 방법은 O(n²) 시간 복잡도로 수행되었습니다.

LEAD 및 LAG 윈도우 함수의 도입은 이 분야에 혁신을 가져왔습니다. 이 함수들은 단일 패스의 집합 기반 간격 계산을 가능하게 하며 O(n) 시간에 실행할 수 있습니다. 이를 통해 데이터베이스 계층에서 수십억 행에 대한 실시간 분석을 수행할 수 있게 되었습니다.

문제

device_id, ts(타임스탬프), value 열을 가진 readings 테이블이 주어졌을 때, 가중 평균을 계산하는 것이 목표입니다. 각 행은 다음 판독값까지의 시간 델타에 비례하여 기여해야 합니다. 수학적으로 이는 $\frac{\sum (value_i \times (ts_{i+1} - ts_i))}{\sum (ts_{i+1} - ts_i)}$로 표현됩니다.

마지막 행은 경계 조건을 나타냅니다. 후속 타임스탬프가 없으므로 그 간격은 0으로 정의하거나 현재 시간으로 외삽하거나 알려진 종료 시간으로 제한해야 합니다. 솔루션은 커서, 사용자 정의 함수 또는 자기 조인을 피하여 순수하게 선언적이어야 합니다.

해결책

LEAD 윈도우 함수를 사용하여 다음 타임스탬프를 현재 행에 투영합니다. 에포크 차이를 계산하여 가중치를 도출합니다. 그런 다음 표준 가중 평균 공식을 적용합니다.

WITH weighted AS (
  SELECT
    device_id,
    value,
    ts,
    COALESCE(
      EXTRACT(EPOCH FROM (LEAD(ts) OVER (PARTITION BY device_id ORDER BY ts) - ts)),
      0
    ) AS duration_seconds
  FROM readings
)
SELECT
  device_id,
  SUM(value * duration_seconds) / NULLIF(SUM(duration_seconds), 0) AS time_weighted_avg
FROM weighted
GROUP BY device_id;

이 접근 방식은 PARTITION BY를 사용하여 각 장치별로 윈도우가 초기화되도록 합니다. 이는 서로 다른 센서의 타임스탬프가 얽히는 것을 방지합니다. COALESCE는 최종 행을 처리하여 가중치를 0으로 할당함으로써 분모에서 효과적으로 제외됩니다.

실제 상황

제약 제조 라인은 200개의 생물반응기를 모니터링합니다. 각 반응기는 가열 단계 동안 10초마다, 대기홀드 동안은 30분마다 온도 데이터를 방출합니다. 품질 팀은 준수를 보장하기 위해 매일 시간 가중 평균을 요구했습니다. 단순 평균은 빠른 가열 샘플에 대해 과도한 비중을 두고, 안정적인 홀드에 대해서는 경시하여 위험한 온도 변화를 감추게 될 수 있습니다.

제안된 해결책 중 하나는 모든 데이터를 Python pandas DataFrame으로 추출하는 것이었습니다. 엔지니어들은 타임스탬프의 diff()를 계산하고 가중 평균을 계산해야 했습니다. 유연하긴 했지만, 이 접근법은 기가바이트의 데이터를 네트워크를 통해 전송했습니다. 또한 90일의 고주파 데이터에 대한 분기말 보고서를 처리할 때 분석 워크스테이션이 중단되었습니다.

또 다른 대안으로는 각 장치의 현재 행보다 큰 MIN(ts)를 찾기 위한 상관 서브쿼리를 사용하는 방법이 있었습니다. 이는 1,000행의 테스트 세트에서는 올바르게 실행되었지만, 1개의 반응기의 전체 이력을 처리하는 데 45분이 걸리는 이차적 성능 저하가 나타났습니다.

팀은 ANSI SQL 윈도우 함수 접근법을 선택했습니다. PostgreSQL 클러스터 내에서 계산을 유지하여 쿼리는 병렬 순차 스캔을 활용하고 네트워크 오버헤드를 방지했습니다. 최종 구현은 모든 반응기에 대해 5천만 행을 12초 이내에 처리했습니다. 이를 통해 운영자들이 몇 분 안에 온도 드리프트를 포착할 수 있는 실시간 대시보드 업데이트가 가능해졌습니다.

후보자들이 자주 놓치는 점

각 파티션의 최종 관찰치에서 후속 타임스탬프가 없을 때 간격 가중치를 어떻게 처리하나요?

후보자들은 경계 조건을 자주 생략합니다. 이는 최종 행의 간격이 NULL로 평가되어 SQL 집계에서 무시됩니다. 결과적으로 마지막 판독값의 기여는 떨어지게 되어 평균에 왜곡을 초래합니다. 올바른 접근법은 평균이 CURRENT_TIMESTAMP와 같은 알려진 종료 시간으로 연장되어야 할 경우 COALESCE를 사용하여 0 또는 EXTRACT(EPOCH FROM (boundary_time - ts))로 대체하는 것입니다.

SUM(value * duration) / SUM(duration) 공식을 사용한 시간 가중 평균을 수학적으로 어떻게 나타내며, AVG(value)를 사용할 경우 어떤 일이 발생합니까?

이는 지속 시간이 가중치 $w_i$로 작용하는 가중 산술 평균을 계산합니다. 후보자들은 종종 이를 기하 평균으로 혼동하거나 AVG(value * duration)를 사용해 정규화 없이 곱의 합을 생성하려고 합니다. AVG(value)를 사용하면 각 행이 동등하게 취급되며, 균일한 시간 단계로 가정하는데, 이는 지속 시간이 더 오래 지속되는 값이 더 큰 영향을 미쳐야 한다는 요구사항을 위반하게 됩니다.

동일한 파티션 내에서의 시간 간격이나 중복 타임스탬프가 LEAD 함수의 동작에 어떻게 영향을 미치고 PARTITION BY device_id가 필수적인 이유는 무엇인가요?

후보자들은 때때로 LEAD가 윈도우 사양 내의 행의 물리적 순서에 따라 작동한다는 것을 잊습니다. PARTITION BY device_id가 없으면 이 함수는 서로 다른 센서 간의 간격을 계산하여 비논리적인 부정적 또는 거대한 시간을 초래합니다. 또한 중복 타임스탬프가 존재할 경우 LEAD는 다음의 고유한 행을 반환하므로 0초 간격이 생성될 수 있습니다. 후보자들은 DISTINCT 또는 ROW_NUMBER() 필터링을 사용하여 사전에 중복을 제거할 것인지 결정해야 합니다.