질문에 대한 답변

질문의 역사

IQR의 개념은 1970년대 존 튜키의 탐색적 데이터 분석 방법론에서 유래하며, 극단 값에 저항력이 있는 이상치 탐지를 위한 강건한 통계량을 제공합니다. 데이터웨어하우징이 발전함에 따라 분석가들은 절차적 통계 패키지에서 집합 기반 SQL 쿼리로 이동하게 되었고, 이러한 계산의 네이티브 데이터베이스 구현이 필요해졌습니다. ANSI SQL:2003은 역분포 함수를 도입했으며, 이후 SQL:2011에서 개선되어 데이터베이스 엔진 내에서 외부 처리 없이 백분위수 계산을 가능하게 했습니다.

문제

이 문제는 데이터 세트 내 각 하위 그룹에 대한 첫 번째 사분위수(Q1, 25번째 백분위수) 및 세 번째 사분위수(Q3, 75번째 백분위수)를 계산하여 IQR(Q3에서 Q1을 뺀 값)을 유도하는 것을 요구합니다. 한 번 설정되면, 통계적 이상치 울타리는 Q1 − 1.5×IQR 및 Q3 + 1.5×IQR에 정의됩니다. 복잡성은 이러한 통계 계산을 단일 집합 기반 작업 내에서 정확성을 유지하며 수행한 다음, 원본 데이터 세트를 동적으로 계산된 경계와 비교하여 필터링하는 데 있습니다. 절차적 루프나 애플리케이션 레이어 처리를 할 필요 없이.

해결책

PERCENTILE_CONT(0.25) 및 **PERCENTILE_CONT(0.75)**를 집계 열 함수로 사용하여 그룹화 열을 기준으로 파티션을 나눈 후, 선형 보간법을 사용하여 정확한 사분위 값을 결정합니다. 공간 테이블 표현(CTE) 내에서 IQR 및 경계 조건을 계산한 후, 이 결과에 대해 조인하거나 WHERE 절을 사용하여 계산된 울타리와 비교하는 방식으로 필터링합니다.

WITH quartiles AS (
  SELECT 
    facility_zone,
    temperature,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY temperature) 
      OVER (PARTITION BY facility_zone) AS q1,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY temperature) 
      OVER (PARTITION BY facility_zone) AS q3
  FROM sensor_readings
),
bounds AS (
  SELECT 
    facility_zone,
    temperature,
    (q3 - q1) AS iqr,
    q1 - 1.5 * (q3 - q1) AS lower_fence,
    q3 + 1.5 * (q3 - q1) AS upper_fence
  FROM quartiles
)
SELECT facility_zone, temperature, iqr
FROM bounds
WHERE temperature < lower_fence 
   OR temperature > upper_fence;

실제 상황

한 제약회사는 200개 시설 구역에 걸쳐 백신 재고를 보관하는 초저온 냉동고의 모니터링을 수행합니다. 각 구역은 매일 10,000개의 온도 판독값을 생성합니다. 간단한 표준 편차 이상치 탐지는 간헐적인 전력망 변동으로 인해 극단적인 스파이크가 발생하여 평균이 왜곡되어, 미세한 장비 결함에 대한 잘못된 부정확한 결과를 초래했습니다. 품질 팀은 구역의 일반적인 운영 범위에서 크게 벗어난 판독치만 경고하는 강력한 통계적 방법이 필요했으며, 이를 통해 실시간 Tableau 대시보드를 제공해야 했습니다.

해결책 1: 애플리케이션 레이어 처리를 통한 Python과 Pandas 사용

ODBC를 통해 모든 과거 데이터를 Python 서비스로 추출한 후, groupby().quantile()을 사용하여 사분위수를 계산하고 필터링하여 결과를 다시 작성합니다. 장점: 매우 유연한 통계 라이브러리, 단계별 실행을 통한 쉬운 디버깅, 데이터 과학자에게 친숙한 구문. 단점: 수백만 개의 행을 전송해야 하는 대규모 네트워크 오버헤드, 애플리케이션 서버에서의 메모리 제약으로 인한 오류 발생, 45분 처리 시간 동안 결과가 구식이 되는 데이터 낡음.

해결책 2: PERCENTILE_CONT 창 함수 사용한 네이티브 ANSI SQL

PERCENTILE_CONT를 사용하여 시설 구역별로 파티션한 주문 집계로 쿼리를 구현합니다. 장점: 데이터 전송 없음, 구역 식별자에 대한 기존 B-tree 인덱스를 활용, 처리 시간을 15초 이내로 단축하고 BI 도구에서 직접 소비 가능한 실시간 결과 제공. 단점: SQL:2003/2011 호환 데이터베이스 필요(구형 MySQL 버전에서는 사용 불가), 실행 중 CPU를 급증시키는 임시 정렬 작업 생성, 많은 애플리케이션 개발자가 익숙하지 않은 복잡한 구문 포함.

해결책 3: NTILE(4)을 이용한 근사화

각 구역의 판독값을 NTILE 창 함수를 사용해 네 개의 동등한 버킷으로 나눈 후, 버킷 1과 4에서 MIN() 및 **MAX()**를 사용하여 Q1 및 Q3 경계를 근사합니다. 장점: 역분포 함수가 부족한 구형 데이터베이스 버전과 호환, 근사 계산 때문에 실행 속도 향상. 단점: 규제 준수에 부적합한 근사 경계만 생성, 작은 샘플 크기나 큰 동일 값으로 인해 치명적인 오류 발생, 명확한 센서 판독값 간의 경계가 발생할 때 비결정론적 동작이 발생.

선택한 해결책 및 결과

팀은 제약 규정이 근사치가 아닌 정확한 통계 계산을 요구하므로 해결책 2(PERCENTILE_CONT 접근법)를 선택했습니다. 데이터베이스 관리자는 **(facility_zone, temperature)**에 대한 복합 인덱스를 생성하여 정렬 작업을 제거했습니다. 결과 쿼리는 판독값의 0.03%를 진짜 통계적 이상치로 식별했으며, 이는 약 200만 달러의 연간 재고 손실을 방지하는 자동 냉동고 검사를 유도했습니다.

지원자가 자주 놓치는 점

PERCENTILE_CONT가 PERCENTILE_DISC와 다르게 사분위를 계산하는 이유는 무엇이며, IQR에는 어떤 것을 사용해야 합니까?

PERCENTILE_CONT(연속)는 요청된 백분위 위치 주위의 두 가장 가까운 값 사이에서 선형 보간법을 수행하여 원 데이터 세트에 존재하지 않을 수도 있는 계산 값을 반환합니다. PERCENTILE_DISC(이산)는 백분위수보다 크거나 같은 가장 작은 누적 분포 값을 반환하여 실제 관측된 측정을 효과적으로 선택합니다. 이상치 탐지에서 IQR 계산을 위해서는 일반적으로 PERCENTILE_CONT가 선호됩니다. 이는 이산 샘플링 아티팩트에 덜 민감한 연속 스케일을 제공하기 때문입니다. 그러나 이상치 경계가 수학적 보간이 아닌 실제 관측값에 대응해야 하는 경우 PERCENTILE_DISC가 필요하게 됩니다.

IQR이 수학적으로 0이 되거나 정의되지 않는 네 개 미만의 고유 값을 포함하는 그룹은 어떻게 처리합니까?

파티션에 동일한 값이 포함되거나 데이터 포인트가 네 개 미만인 경우, PERCENTILE_CONT는 Q1 및 Q3에 대해 동일한 값을 반환하여 IQR이 0이 됩니다. 이는 이상치 울타리가 중앙값으로 수축되어 모든 고유 관측치를 이상치로 표시할 수 있습니다. 지원자는 NULLIF 검사나 CASE 표현식을 구현하여 0 IQR 시나리오를 감지해야 하며, 이상치 상태에 대해 NULL을 반환하거나 소규모 그룹에 대해 표준 편차 방법으로 되돌리거나 비즈니스 규칙에 따라 이상치 분석에서 COUNT(DISTINCT value) < 4인 그룹을 명시적으로 제외해야 합니다.

수십억 행을 고카디널리티 카테고리로 파티셔닝할 때 역분포 함수를 최적화하는 인덱싱 전략은 무엇입니까?

PERCENTILE_CONT는 각 파티션을 정렬하여 백분위 위치를 결정해야 하므로 지원자는 종종 **(category, measurement)**에서 복합 인덱스의 필요성을 간과합니다. 이러한 인덱스를 통해 데이터베이스 엔진은 미리 정렬된 인덱스 리프 페이지를 스캔할 수 있어, 디스크에서 비용이 많이 드는 외부 정렬 작업을 제거합니다. 이러한 인덱스가 없으면 데이터베이스는 각 파티션에 대해 별도의 정렬을 수행하여 심각한 I/O 트래싱과 임시 디스크 공간 소모를 초래합니다. 또한, 지원자는 선택적 WHERE 절을 초기 CTE로 밀어넣으면 비용이 많이 드는 백분위수 계산을 시작하기 전에 작업 세트를 줄일 수 있다는 점을 간과합니다.