Tarihsel Bağlam
ANSI SQL:2011 standardı, pencere fonksiyonu söz dizimine çerçeve dışlama ifadelerini tanıtarak, pencere çerçevelerinin zorunlu olarak mevcut satırı içerme kısıtlamasını ele aldı. Bu geliştirmeden önce, geliştiriciler, odak satırını hariç tutacak toplamları hesaplamak için karmaşık bir şekilde kendileriyle birleştirmelere veya cebirsel işlemlere (mevcut değeri toplamdan çıkarmak gibi) başvurmak zorunda kalıyorlardı. Standart, sıralı parçalar içinde küme işlemleri için deterministik anlamlar sağlayan dört dışlama seçeneği tanımlar: EXCLUDE NO OTHERS, EXCLUDE CURRENT ROW, EXCLUDE GROUP ve EXCLUDE TIES.
Sorun
Rekabetçi metrikleri analiz ederken—örneğin, bir ürünün kendisini hariç tutarak benzer ürünlerin ortalama satış fiyatını hesaplamak—bir sorgunun, mevcut olanlar hariç tüm ilgili satırları kapsayan bir pencerede tanımlanması gerekir. AVG() OVER (PARTITION BY category) gibi geleneksel pencere fonksiyonları mevcut satırı içerdiğinden, sonucu çarpıtır. Bu durumu alt sorgular veya birleştirmeler ile uygulamak, büyük parçalanmış veri kümeleri ile başa çıkarken gereksiz karmaşıklık ve performans düşüklüğü yaratır; çünkü kartezyen çarpanlar veya ilişkili alt sorgular uygun fiyatlı olmayabilir.
Çözüm
Pencere spesifikasyonunda çerçeve dışlama ifadesini kullanın: AVG(price) OVER (PARTITION BY category ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW). Bu sözdizimi, SQL motorunun önce tam parçayı kurmasını, ardından toplamı hesaplamadan önce mevcut satırı mantıksal olarak çıkarmasını talimatlandırır. Tüm bağlara (örneğin, aynı fiyat noktasındaki tüm ürünler) dışlama gerektiren senaryolar için, EXCLUDE GROUP hem mevcut satırı hem de sıralama arkadaşlarını kaldırırken, EXCLUDE TIES mevcut satırı korur ancak tekrar eden sıralama değerlerini ortadan kaldırır.
Bir e-ticaret analiz ekibi, "Pazar Pozisyonu" raporu oluşturmak zorundadır. Her satıcının bir elektronik cihaz listelemesinde, o satıcının fiyatını aynı model cihazı sunan diğer tüm satıcıların ortalama fiyatıyla birlikte göstermeleri gerekmektedir.
Öncelikle kendi kendine birleştirme yaklaşımı prototiplenmiştir, burada listeleme tablosu, eşleşen birincil anahtarları hariç tutarak model_id üzerinde kendisiyle birleştirilmiştir. Artılar: Tüm SQL lehçeleri tarafından evrensel olarak desteklenir ve kavramsal olarak anlaşılması kolaydır. Eksiler: En kötü durumda O(n²) karmaşıklığı gösterir, milyonlarca satırda üstel yavaşlamaya neden olur; ayrıca, sorgu optimizasyoncusu genellikle eşitsiz bir birleştirme şartı ile başa çıkmada zorluk çeker ve verimsiz yürütme planları oluşturur.
Ayrıca, küresel toplam ve her model başına sayım hesaplayarak, ardından diğerlerinin ortalamasını (SUM(price) - current_price) / (COUNT(*) - 1) ile türeterek cebirsel bir çözüm yolu da değerlendirildi. Artılar: Birleştirme gerektirmeden ve yalnızca tek bir pencere fonksiyonu taraması gerektirir. Eksiler: COUNT(*) = 1 (sıfıra bölme) durumunda felakete uğrar veya fiyatlar NULL olduğunda, geniş açıklamalı CASE muhafızlarına ihtiyaç duyar; ayrıca, MEDIAN veya MODE gibi cebirsel olmayan toplamlar için uygulanamaz.
Ekip nihayetinde EXCLUDE CURRENT ROW çerçeve spesifikasyonunu seçti. Gerekçesi: Belirleyici olup, boş çerçeveler için doğal olarak NULL döndürerek NULL kontrolü gerektiren CASE ifadelerinden kurtarır; ayrıca minimum bellek yükü ile O(n) zamanında tek sıralı geçişle çalışır. Ortaya çıkan sorgu, rapor üretimini on iki dakikadan on saniyenin altına düşürdü.
Sonuç: Üretim raporu artık her gün 50 milyon listeleme için rakip ölçütlerini doğru bir şekilde hesaplamakta, nadir satıcılarla ilgili nadir ürünleri NULL (bu, "Rakipsizlik" olarak yorumlanır) göstererek yönetmektedir; böylece hatalar veya sıfır değerleri oluşmaz.
EXCLUDE CURRENT ROW, RANGE tabanlı ve ROWS tabanlı pencere çerçeveleri ile kullanıldığında, özellikle arkadaş grupları ile nasıl bir davranış sergiler?
Eğer pencere çerçevesi ROWS kullanıyorsa, EXCLUDE CURRENT ROW tam olarak bir fiziksel satırı — mevcut satırı — dışlar. Ancak, RANGE kullanıldığında (örneğin, RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING), "mevcut satır" kavramsal olarak mevcut satır ile aynı sıralama değerine sahip tüm satırları temsil eder. Bu bağlamda, EXCLUDE CURRENT ROW yalnızca spesifik satır örneğini kaldırır ve diğer arkadaşları (bağlar) çerçevede bırakır. Tersine, EXCLUDE GROUP mevcut satırı ve tüm arkadaşları çerçeve biriminden bağımsız olarak kaldırırken, EXCLUDE TIES tüm arkadaşları hariç tutar. Adaylar çoğunlukla bunu karıştırır ve RANGE ile EXCLUDE CURRENT ROW kullanıldığında, durumun EXCLUDE GROUP gibi davrandığını varsayarak, eşleşen sıralama anahtarları olduğunda yanlış toplamlar elde ederler.
EXCLUDE CURRENT ROW kullanıldığında tek satırlık bir parçanın NULL döndürmesinin nedeni nedir ve bu, manuel çıkarma yöntemlerinden nasıl farklılık gösterir?
ANSI SQL standardı, boş bir küme üzerindeki bir toplamın NULL döndürdüğünü tanımlar. EXCLUDE CURRENT ROW, yalnızca bir satır içeren bir parçaya uygulandığında, çerçeve boş hale gelir ve AVG, SUM veya COUNT otomatik olarak NULL sonuç verir. Bunun tersine, (SUM(col) - col) / (COUNT(*) - 1) gibi manuel yöntemler, aritmetikte sıfıra bölme veya NULL propagasyon sorunlarıyla karşılaşır ve tekil parçaların güvenli bir şekilde işlenmesi için açık CASE ifadelerine ihtiyaç duyar. Adaylar genellikle bu otomatik NULL işleme davranışını gözden kaçırır, sıfır veya mevcut değeri bekleyerek ve EXCLUDE'nun sınır koşulları için daha iyi bir null-güvenliği sağladığını takdir etmezler.
EXCLUDE, kayar pencereler gibi keyfi çerçeve boyutlarıyla (örneğin, ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) birleştirilebilir mi ve performans etkileri nelerdir?
Evet, EXCLUDE ifadeleri her türlü çerçeve boyutuyla geçerlidir, kayar pencereler de dahildir. Örneğin, AVG(val) OVER (ORDER BY time ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW) iki öncekini ve iki sonrakini ortalamayı hesaplar, böylece mevcut noktayı dışlayarak 4 noktalık hareketli bir ortalama oluşturur. Performans açısından, modern optimizatörler bunu bir akış algoritması ile halka tamponunda veya deque’de uygular, her bir parçanın O(n) karmaşıklığını korur. Adaylar sıklıkla EXCLUDE'nun parçanın tam malzemesini gerektirdiğini veya yalnızca UNBOUNDED çerçevelerle çalıştığı varsayımında bulunur, ancak bunun sınırlı hareketli pencerelerle, merkezi döngüsel korelasyonlar veya istatistiği etkilemeyecek şekilde dışlama sağlamada sorunsuz bir şekilde entegre olduğunu kaçırır.