Sorunun geçmişi
IQR kavramı, 1970'lerde John Tukey tarafından geliştirilen keşifsel veri analizi metodolojisinden kaynaklanmaktadır; bu, uç değer tespiti için sağlam bir istatistik sağlayarak aşırı değerlere karşı dayanıklıdır. Veri ambarları geliştikçe, analistler prosedürel istatistik paquetesinden set tabanlı SQL sorgularına geçiş yaptı, bu da bu hesaplamaların yerel veritabanı uygulamalarını gerektiriyordu. ANSI SQL:2003 ters dağılım fonksiyonlarını tanıttı, daha sonra SQL:2011'de rafine edildi ve yüzde hesaplamaları veritabanı motoru içinde harici işleme olmadan doğrudan yapılmasına olanak tanıdı.
Sorun
Sorun, her alt grup için ilk çeyrek (Q1, %25'lik persentil) ve üçüncü çeyreği (Q3, %75'lik persentil) hesaplamayı gerektirir ve böylece IQR'yi (Q3 eksi Q1) elde eder. Belirlendikten sonra, istatistiksel uç değer sınırları Q1 − 1.5×IQR ve Q3 + 1.5×IQR olarak tanımlanır. Karmaşıklık, bu istatistiksel hesaplamaları tek bir set tabanlı işlemde bölümlere uygulayarak kesinliği korumak ve orijinal veri kümesini bu dinamik olarak hesaplanan sınırlarla filtrelemek gerekliliğindedir; işlem katmanları veya uygulama katmanında işleme başvurulmadan.
Çözüm
PERCENTILE_CONT(0.25) ve PERCENTILE_CONT(0.75)'yi, gruplama sütununa göre bölümlenmiş sıralı küme penceresi fonksiyonları olarak kullanın; bu fonksiyonlar, kesin çeyrek değerlerini belirlemek için doğrusal interpolasyon yapar. Common Table Expression (CTE) içinde IQR'yi ve sınır koşullarını hesaplayın, ardından bu sonuçla birleştirerek veya karşılaştırma ölçümlerini hesaplanan sınırlarla karşılaştıran WHERE koşulları kullanarak doğrudan filtreleyin.
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;
Bir ilaç şirketi, 200 tesis bölgesi boyunca aşı envanterini depolayan ultra düşük sıcaklıklı dondurucuları izliyor. Her bölge günde 10.000 sıcaklık okumaya sahiptir. Basit standart sapma uç değer tespiti, zaman zaman meydana gelen elektrik şebekesi dalgalanmalarının neden olduğu aşırı zirveler nedeniyle başarısız oldu ve bu da ortalamayı çarpıtıp ince ekipman arızaları için yanlış negatif sonuçlar doğurdu. Kalite ekibi, sadece bölgenin tipik çalışma aralığından anlamlı şekilde sapan okumaları işaret edecek sağlam bir istatistiksel yöntem talep etti ve bunu doğrudan PostgreSQL veri ambarlarında uygulayarak gerçek zamanlı Tableau panolarına beslemiştir.
Çözüm 1: Python ve Pandas kullanarak uygulama katmanı işleme
Tüm tarihsel verileri ODBC aracılığıyla bir Python hizmetine çıkarın, groupby().quantile() kullanarak çeyrek hesaplayın, ardından sonuçları filtreleyip geri yazın. Artılar: Son derece esnek istatistik kütüphaneleri, adım adım yürütme ile kolay hata ayıklama ve veri bilimcileri için tanıdık sözdizimi. Eksiler: Milyonlarca satırın aktarılması nedeniyle büyük ağ yükü, bellek kısıtlamaları nedeniyle uygulama sunucularında hatalara neden olan durumlar ve 45 dakikalık işleme pencereleri nedeniyle sonuçların tamamen tamamlandıktan sonra güncelliğini yitirmesi.
Çözüm 2: PERCENTILE_CONT pencere fonksiyonlarını kullanarak yerel ANSI SQL
Sorguyu, facility_zone'a göre parça parça bölümlenmiş PERCENTILE_CONT olarak sıralı küme toplamları ile uygulayın. Artılar: Veri aktarımı yok, mevcut B-tree indekslerini kullanır, işleme süresini 15 saniyenin altına indirir ve BI araçları tarafından doğrudan tüketilebilecek gerçek zamanlı sonuçlar sağlar. Eksiler: SQL:2003/2011 uyumlu bir veritabanı gerektirir (eski MySQL versiyonlarında mevcut değildir), yürütme sırasında CPU'yu artıran geçici sıralama işlemleri oluşturur ve birçok uygulama geliştiricisi için tanıdık olmayan karmaşık sözdizimine sahiptir.
Çözüm 3: NTILE(4) kullanarak yaklaşık hesaplama
Her bölgenin okumalarını NTILE pencere fonksiyonu kullanarak dört eşit sepet halinde ayırın, ardından Q1 ve Q3 sınırlarını yaklaşık olarak hesaplamak için 1. ve 4. sepet üzerinde MIN() ve MAX() uygulayın. Artılar: Ters dağılım fonksiyonları eksik olan eski veritabanı versiyonları ile uyumludur, yaklaşık hesaplamalar nedeniyle daha hızlı yürütülür. Eksiler: Düzenleyici uyum için uygun olmayan yalnızca yaklaşık sınırlar üretir, küçük örnek boyutlarında veya ağır bağ değerlerinde iflas eder ve ayrık sensör okumaları arasında sınırlar düştüğünde belirsiz davranışlar sergileyebilir.
Seçilen çözüm ve sonuç
Ekip, ilaç düzenlemeleri kesin istatistiksel hesaplamaları gerektirdiğinden Çözüm 2'yi (PERCENTILE_CONT yaklaşımı) seçti. Veritabanı yöneticisi, (facility_zone, temperature) üzerinde bileşik indeksler oluşturdu ve sıralama işlemlerini ortadan kaldırdı. Sonuç olarak sorgu, okumaların %0,03'ünü gerçek istatistiksel uç değerler olarak tanımladı ve bu da yaklaşık 2 milyon dolarlık yıllık envanter kaybını önleyen otomatik dondurucu denetimlerini tetikledi ve Python ETL katmanını kaldırarak altyapı maliyetlerini düşürdü.
PERCENTILE_CONT'in çeyrekleri hesaplarken PERCENTILE_DISC'den farklı sonuçlar üretmesinin nedeni nedir ve IQR için hangisi kullanılmalıdır?
PERCENTILE_CONT (sürekli) iki en yakın değer arasında doğrusal interpolasyon yapar ve istenen persentil konumunu çevreleyen bu değerlerin arasında bulunan hesaplanan bir değeri döndürür; bu değer, orijinal veri kümesinde mevcut olmayabilir. PERCENTILE_DISC (kesirli) ise, persentilin daha büyük veya eşit olduğu en küçük kümülatif dağılım değerini döndürerek, aslında gözlemlenmiş bir ölçüm seçer. Uç değer tespitindeki IQR hesaplamaları için genellikle PERCENTILE_CONT tercih edilir çünkü, kesirli örnekleme artifaktlarına karşı daha az duyarlı sürekli bir ölçek sağlar; ancak PERCENTILE_DISC, uç değer sınırlarının fiziksel olarak gözlemlenen değerlerle eşleşmesi gerektiğinde gerekli olur.
IQR'nin matematiksel olarak sıfıra düştüğü veya tanımsız hale geldiği dörtten az farklı değere sahip grupları nasıl ele alırsınız?
Bir bölme, birbirinin aynı değerlere sahip olduğunda veya dört veri noktasından daha azına sahip olduğunda, PERCENTILE_CONT Q1 ve Q3 için aynı değerleri döndürür; bu da sıfır IQR anlamına gelir. Bu, uç değer sınırlarının medyan değerine çökmesine neden olur ve bu da her farklı gözlemi uç değer olarak işaretleyebilir. Adaylar, sıfır IQR senaryolarını algılamak için NULLIF kontrolleri veya CASE ifadeleri uygulamalıdır; ya uç değer durumu için NULL döndürmeli, ya da küçük gruplar için standart sapma yöntemlerine geri dönmeli ya da iş kurallarına göre uç değer analizinden COUNT(DISTINCT value) < 4 olan grupları açıkça hariç tutmalıdır.
Milyonlarca satırı yüksek kardinalite kategorilerine göre işlerken ters dağılım fonksiyonlarının performansını optimize eden hangi indeksleme stratejisi?
PERCENTILE_CONT, her bölüm için persentil konumlarını belirlemek üzere sıralama yapmayı gerektirdiğinden, adaylar genellikle (category, measurement) üzerine bileşik indekslerin gerekliliğini göz ardı eder. Böyle indeksler, veritabanı motorunun önceden sıralanmış indeks yaprak sayfalarını taramasına olanak tanır ve disk üzerindeki maliyetli dış sıralama işlemlerini ortadan kaldırır. Bu indeksler olmadan, veritabanı her bölüm için ayrı sıralama yapar ve ciddi I/O çalkantısına ve geçici disk alanı tükenmesine neden olur. Ayrıca, adaylar seçici WHERE koşullarını erken CTE'lere itmenin, maliyetli persentil hesaplamalarından önce çalışma kümesini azaltacağını gözden kaçırır; çünkü ters dağılım fonksiyonları, toplama aşaması başladığında indeksleri kullanamazlar.