질문의 역사
**중앙치 절대 편차 (MAD)**는 1816년에 가우스에 의해 도입되어 통계적 분산의 강력한 척도로 사용되며, 1970년대에 햄펠에 의해 이상치 저항 분석을 위해 정식화되었습니다. 표준 편차와는 달리, 편차를 제곱하기 때문에 극단적인 값에 매우 민감한 것과 달리, MAD는 왜곡 없이 최대 50%의 오염된 데이터를 허용합니다. ANSI SQL에서 MAD를 계산하는 것은 SQL:2003 표준이 도입한 정렬 집합 집계 함수인 PERCENTILE_CONT 덕분에 절차적 루프 없이 선언적으로 중앙값 계산이 가능해졌습니다.
문제
MAD를 계산하려면 중첩된 중앙값 작업이 필요합니다: 먼저 데이터셋의 중앙값을 결정한 다음 각 관측치와 그 중앙값 사이의 절대 차이의 중앙값을 찾습니다. ANSI SQL에서는 동일한 SELECT 절 내에서 집계 결과를 참조하여 개별 편차를 계산하려면 자체 조인 또는 연관 서브쿼리가 필요하여 대규모 시계열 데이터셋에서 성능이 저하됩니다. 또한, 표준 STDDEV 함수는 센서 데이터에 전송 스파이크 또는 보정 오류가 포함된 경우 비율을 부풀려 강력한 MAD가 정확한 이상 감지를 위해 필수적입니다.
해결책
공통 테이블 표현식 (CTE) 파이프라인을 사용하여 계산을 논리적 단계로 나누십시오. 먼저, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY category)를 사용하여 그룹별 중앙값을 계산합니다. 두 번째로, 그룹 중앙값에 대해 각 행의 절대 편차를 계산합니다. 마지막으로, 이러한 편차에 대해 다시 PERCENTILE_CONT를 적용하여 MAD를 도출합니다. 이 방법은 순수하게 집합 기반이며 데이터베이스 엔진의 최적화기를 활용하여 윈도우 함수를 사용하고 행 단위 처리를 피합니다.
WITH group_medians AS ( SELECT sensor_id, reading, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY reading) OVER (PARTITION BY sensor_id) AS median_val FROM telemetry ), deviations AS ( SELECT sensor_id, ABS(reading - median_val) AS abs_dev FROM group_medians ) SELECT DISTINCT sensor_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY abs_dev) OVER (PARTITION BY sensor_id) AS mad FROM deviations;
제조 공장은 컨베이어 벨트에 수천 개의 진동 센서를 배치하여 베어링 고장을 예측했습니다. 정적 알림 임계값은 겨울철 온도가 여름보다 자연스럽게 낮은 기준선으로 인해 잘못된 긍정 결과와 더운 달의 알림 누락이 발생하여 실패했습니다. 엔지니어링 팀은 간헐적인 전송 글리치에 의해 왜곡되지 않는 통계적 방법이 필요했습니다.
팀은 세 가지 아키텍처 접근 방식을 고려했습니다.
클라이언트 측 통계 처리는 매일 CSV 덤프를 Python을 사용하여 Pandas와 SciPy 라이브러리로 내보내는 것을 포함했습니다. 이는 풍부한 통계 함수를 제공하고 신속한 프로토타입을 가능하게 했지만, 24시간 데이터 지연을 초래하고 민감한 운영 데이터를 SQL 데이터베이스 방화벽 외부로 이동시켜 보안 위험을 초래했습니다.
절차적 SQL 솔루션은 커서를 사용하고 임시 테이블을 사용하여 각 센서의 이력을 반복하면서 값을 정렬하여 중앙 행을 식별했습니다. 이 방법은 현대 윈도우 함수가 없는 구형 시스템에서 작동했지만, O(n²) 복잡성과 과도한 잠금 경합 때문에 심각한 성능 저하의 문제를 겪어야 했으며, 백만 행 처리에 45분 이상 걸렸습니다.
ANSI SQL 윈도우 함수는 CTE를 통해 세트 기반으로 중앙값을 계산했습니다. 이 솔루션은 데이터베이스 엔진 내에서 50백만 레코드에 대해 800밀리초 이내에 실행되었으며, 네트워크 오버헤드를 최소화하고 최적화 병렬성을 활용했습니다. 그러나 SQL:2003 이상 호환이 필요했죠.
팀은 실시간 성능과 데이터 내보내기를 금지하는 엄격한 데이터 거버넌스 요구 사항 간의 균형을 맞추기 위해 ANSI SQL 윈도우 함수 접근 방식을 선택했습니다. 결과적으로 MAD 값이 median ± 3 * MAD를 초과하는 모든 판독값에 대해 즉각적인 유지보수 경고를 발동하는 동적 임계값이 설정되었습니다. 이로 인해 잘못된 긍정이 94% 감소하였고 이전의 정적 시스템보다 이틀 빨리 세 건의 베어링 고장을 감지했습니다.
왜 MAD가 SQL 기반 텔레메트리 시스템에서 이상 감지에 대해 표준 편차보다 선호됩니까?
표준 편차는 평균으로부터의 평균 제곱 편차의 제곱근을 계산합니다. 이는 이상치가 존재할 때 제곱으로 인해 큰 거리를 증폭시키므로, 폭발적으로 증가하게 됩니다. 반면, MAD는 중앙값을 사용하여 50%의 데이터량까지 극단적인 이상치의 크기를 무시하는 파손점 저항 추정기입니다. ANSI SQL 구현의 경우, 이는 단일 센서 기능 고장이 9999 값을 전송하면 STDDEV가 크게 부풀려지지만 MAD는 거의 변하지 않아 향후 미세한 이상치를 감추는 잘못된 임계값 부풀림을 방지합니다.
떨어지는 센서 판독을 위한 중앙값을 계산할 때 PERCENTILE_CONT와 PERCENTILE_DISC의 차이점은 무엇이며, MAD에는 무엇을 사용하는 것이 좋습니까?
PERCENTILE_CONT(0.5)는 행 수가 짝수일 때 두 중앙값 간의 선형 보간을 수행하여 테이블에 존재하지 않을 수 있는 가상의 값을 반환합니다 (예: 20과 30을 평균하여 25를 반환). 반면에 PERCENTILE_DISC(0.5)는 누적 분포가 0.5 이상인 데이터셋의 최소 실제 값을 반환합니다. 정수 센서 판독에 대해 MAD 계산에서는 PERCENTILE_DISC를 사용하는 것이 더 안전한 경우가 많습니다. 이는 임계값이 실제 관측 측정에 해당하도록 보장하여 해석을 복잡하게 하는 분수 편차를 피합니다.
CTE 없이 단일 자기 조인을 사용하여 MAD를 계산할 수 있습니까? 성능의 거래는 무엇입니까?
예, 하지만 비효율적입니다. 테이블에서 sensor_id를 기준으로 자기 조인하여 모든 행을 서로 비교하여 중앙값을 찾을 수 있지만, 이는 O(n²) 복잡도를 초래합니다. 대안으로 중앙값을 먼저 계산하는 유도 하위 쿼리를 사용하고 다시 조인하여 편차를 계산하도록 강제하면 데이터베이스가 중간 결과를 물질화하거나 테이블을 여러 번 다시 스캔해야 합니다. CTE를 사용하면 최적화기가 중앙값 계산을 스풀 또는 작업 테이블로 처리하도록 허용하여 한 번 계산하고 재사용하는 결과를 주로 한 번의 정렬 작업과 선형 O(n log n) 복잡도로 줄이는 것을 가능하게 합니다. 후보자들은 종종 ANSI SQL 최적화기가 CTE를 내부 작업 테이블로 변환할 수 있음을 잊게 되며, 이는 동일한 SELECT 목록의 연관 서브쿼리보다 더 효율적입니다.