Sorunun geçmişi
Medyan mutlak sapma (MAD), 1816'da Gauss tarafından istatistiksel yayılmanın sağlam bir ölçüsü olarak tanıtıldı ve daha sonra 1970'lerde Hampel tarafından aykırı değerlere dayanıklı analizler için resmileştirildi. Aykırı değerlere karşı oldukça hassas olan standart sapmanın aksine, MAD, %50'ye kadar kirlenmiş veriye karşı bozulma olmadan tolerans gösterir. ANSI SQL içinde, MAD hesaplanması SQL:2003 standardı ile pratik hale geldi; bu, prosedürel döngüler olmadan açık medyan hesaplamalarına olanak tanıyan PERCENTILE_CONT gibi sıralı küme toplama işlevlerini tanıttı.
Sorun
MAD hesaplamak, iç içe geçmiş bir medyan işlemi gerektirir: önce veri kümesinin medyanını belirlemek, ardından her gözlem ile o medyan arasındaki mutlak farkların medyanını bulmak. ANSI SQL içinde, bu, SELECT ifadesi içindeki bir toplama sonucuna atıfta bulunmanın bir kendine bağlanma veya ilişkili alt sorgu gerektirmesi nedeniyle zordur; her iki durum da büyük zaman serisi veri kümesi üzerinde performansı düşürür. Ayrıca, standart STDDEV işlevleri, sensör verileri iletimi sıçramaları veya kalibrasyon hataları içerdiğinde şişmiş eşikler üretir, bu da doğru anomali tespiti için sağlam MAD'ı gerekli kılar.
Çözüm
Hesaplamayı mantıksal aşamalara ayırmak için bir Ortak Tablo İfadesi (CTE) hattı kullanın. Öncelikle, grup başına medyanı hesaplamak için PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY category) ifadesini kullanın. İkinci olarak, her satırın kendi grup medyanına göre mutlak sapmasını hesaplayın. Son olarak, bu sapmalara tekrar PERCENTILE_CONT uygulayarak MAD'ı elde edin. Bu yöntem tamamen küme bazlıdır, veritabanı motorunun pencere işlevleri için optimizasyonunu kullanır ve satır bazında işlem yapmaktan kaçınır.
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;
Bir imalat tesisi, yatak arızalarını tahmin etmek için konveyör bantlarına binlerce titreşim sensörü yerleştirdi. Statik uyarı eşikleri, kış sıcaklıkları yazdan daha doğal olarak düşük alt sınırlar oluşturduğundan, soğuk aylarda yalancı pozitifler ve sıcak aylarda kaçırılan uyarılar ile sonuçlandı. Mühendislik ekibi, her sensörün eşsiz tarihsel dağılımına uyum sağlayan istatistiksel bir yöntem aradı ve bu yöntemin zaman zaman meydana gelen iletim hatalarından etkilenmemesi gerekti.
Ekip, üç mimari yaklaşımı değerlendirdi.
İstemci tarafında istatistiksel işlem , günlük CSV dökümlerini Python kullanarak Pandas ve SciPy kütüphanelerine aktarmayı içeriyordu. Bu, zengin istatistiksel fonksiyonlar ve hızlı prototipleme sağladı ancak 24 saat veri gecikmesine neden oldu ve hassas operasyonel verileri SQL veritabanı güvenlik duvarının dışına taşımakla birlikte güvenlik riski yarattı.
Prosedürel SQL çözümleri, her sensörün geçmişinde yinelemelerle geçmek için işaretleyiciler ve geçici tablolar kullandı, değerleri sıralayarak orta satırı belirledi. Bu yaklaşım, modern pencere işlevlerinden yoksun miras sistemlerde çalıştı, ancak O(n²) karmaşıklığı ve aşırı kilitlenme rekabeti nedeniyle ciddi performans düşüklüğü yaşadı; bir milyon satırı işlemek 45 dakikadan fazla sürdü.
ANSI SQL pencere fonksiyonları, PERCENTILE_CONT kullanılarak küme bazında medyanları hesapladı. Bu çözüm, 50 milyon kayda karşılık olarak veritabanı motoru içinde 800 ms'den daha kısa bir sürede tamamen çalıştı, ağ yükünü minimize etti ve optimizasyon paralelliğini kullandı; ancak SQL:2003 veya daha yeni bir uyumluluğa ihtiyaç duyuyordu.
Ekip, verilerin dışa aktarımını yasaklayan sıkı veri yönetimi gereksinimleri ile gerçek zamanlı performansı dengeleyen ANSI SQL pencere işlevi yaklaşımını seçti. Elde edilen MAD değerleri, median ± 3 * MAD değerini aşan herhangi bir okumanın derhal bakım uyarısını tetikleyeceği dinamik eşikler oluşturdu. Bu, yalancı pozitifleri %94 oranında azalttı ve önceki statik sistemin iki gün önce üç yaklaşan yatak arızasını tespit etti.
SQL tabanlı telemetri sistemlerinde anomali tespiti için neden MAD, standart sapmadan daha çok tercih ediliyor?
Standart sapma, ortalamadan olan ortalama kare sapmanın karekökünü hesaplar, bu metrik, aykırı değerler olduğunda patlar çünkü kare alma büyük mesafeleri şişirir. Buna karşılık, MAD, aşırı aykırı değerlerin %50'sine kadar göz ardı eden, dayanıklılık gösteren bir medyan kullanır. ANSI SQL uygulamaları için bu, bir sensörün 9999 değerini göndermesi durumunda STDDEV'in büyük ölçüde şişeceği ancak MAD'in hemen hemen aynı kalacağı anlamına gelir; bu, gelecekteki ince anomali tespitini gizleyen yanlış eşik şişirmesini engeller.
PERCENTILE_CONT ve PERCENTILE_DISC medyanları hesaplama sırasında nasıl farklıdır ve MAD için hangisini kullanmalısınız?
PERCENTILE_CONT(0.5), satır sayısı çift olduğunda iki merkezi değer arasında lineer içe aktarma yapar ve tablonuzda var olmayan varsayımsal bir değer döndürür (örneğin, 20 ile 30'u ortalayıp 25 döndürmek). PERCENTILE_DISC(0.5) ise, kümülatif dağılımı 0.5'ten büyük veya eşit olan veri kümesinden en küçük gerçek değeri döndürür. Kesirli sapmaları yorumlama karmaşıklığından kaçınmak adına, MAD hesaplaması için PERCENTILE_DISC genellikle daha güvenlidir çünkü eşik değerinin gerçek gözlemlenen bir ölçüm ile eşleşmesini garanti eder.
MAD, CTE'ler olmadan tek bir kendine bağlanma ile hesaplanabilir mi ve performans ticaretleri nelerdir?
Evet, ancak verimsizdir. Medyanı bulmak için her satırı diğer bütün satırlar ile karşılaştırmak üzere tabloyu sensor_id üzerinden kendine bağlayabilirsiniz; ancak bu, O(n²) karmaşıklığına yol açar. Alternatif olarak, medyanı ilk olarak hesaplamak için bir türev alt sorgusu kullanmak, sonra sapmaları hesaplarken geri bağlanmak, veritabanının ara sonuçları materyalleştirmesi veya tabloyu birden fazla kez yeniden taramasını zorlayacaktır. CTE'ler optimizasyoncunun, medyan hesaplamasını bir spool veya çalışma tablosu olarak ele almasını sağlar; bu, genellikle tek bir sıralama işlemi ve lineer O(n log n) karmaşıklığını sağlarken daha etkili hale gelir. Adaylar genellikle ANSI SQL optimizasyonlarının CTE'leri dahili çalışma tablolarına dönüştürebileceğini unutur, bu da onları SELECT listesinde ilişkili alt sorgulardan daha verimli hale getirir.