質問の歴史
中央値絶対偏差（MAD）は1816年にガウスによって導入され、外れ値に強い統計的分散測定として後に1970年代にハンペルによって正式化されました。標準偏差とは異なり、偏差を二乗するため、極端な値に過剰に敏感ですが、MADは50％までの汚染データを歪曲なしで許容します。ANSI SQLでは、SQL:2003標準で、手続き的ループなしで宣言的中央値計算を可能にする順序付き集計関数 PERCENTILE_CONT が導入されたため、MADの計算が実用的になりました。
問題
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は、PERCENTILE_CONTを使用して中央値をセットベースで計算しました。この解決策は、50ミリオンのレコードに対して800ミリ秒以内でデータベースエンジン内で完全に実行され、ネットワークオーバーヘッドを最小限に抑え、オプティマイザーの並列性を活用しましたが、SQL:2003またはそれ以降の準拠が必要でした。
チームは、データのエクスポートを禁止する厳格なデータガバナンス要件とリアルタイム性能をバランスさせるためにANSI SQLウィンドウ関数アプローチを選択しました。結果として得られたMAD値は、median ± 3 * MADを超える読み取り値が即時メンテナンスアラートを引き起こす動的閾値を確立しました。これにより偽陽性が94％減少し、前回の静的システムよりも2日前に3件の迫り来るベアリング故障を検出しました。
なぜ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を内部作業テーブルに変換できることを忘れ、これが相関サブクエリよりも効率的であることに気づいていません。