Hesaplama, en küçük kareler yöntemine dayanır. Eğim (β), bağımsız değişken X ile bağımlı değişken Y arasındaki kovaryansın X'in varyansına bölünmesi olarak tanımlanır. Kesişim (α), Y'nin ortalamasından eğimin ve X'nin ortalamasının çarpımının çıkarılmasıyla elde edilir. ANSI SQL'de bu cebirsel tanımları SUM, AVG ve COUNT toplamları kullanarak uygularsınız, genellikle bir GROUP BY ifadesi içinde veya OVER ifadesiyle pencere fonksiyonları olarak. Sorgunun, son katsayıları belirlemek için çapraz ürünlerin toplamını (Σ(X - X̄)(Y - Ȳ)) ve X'in kare sapmalarının toplamını (Σ(X - X̄)²) açıkça hesaplaması gerekir.
Bir perakende analitik ekibi, dinamik fiyatlandırma stratejilerini optimize etmek için her ürün kategorisinde talep fiyat esnekliğini belirlemek zorundaydı. Onların unit_price ve quantity_sold içeren bir işlem tablosu vardı ve her bir ayrı category_id için fiyat ile satılan miktarın nasıl değiştiğini nicelendirerek bir trend çizgisine ihtiyaç duyuyorlardı.
Önerilen çözümlerden biri, günlük toplamları dış bir Python betiğine aktararak scikit-learn kullanarak regresyon modellerini uygulamaktı. Bu yaklaşım, uygulama kolaylığı ve zengin istatistiksel tanılara erişim sağladı. Ancak, önemli veri gecikmeleri yaratıyordu, hassas satış verilerinin dış kopyalarını oluşturarak katı veri yönetimi politikalarını ihlal ediyordu ve otomatik fiyatlandırma algoritmaları için gerekli olan gerçek zamanlı gösterge panellerinin güncellemelerini engelliyordu.
Değerlendirilen bir diğer seçenek, veritabanı motorunda bir kullanıcı tanımlı toplam fonksiyonu (UDAF) oluşturmaktı; bu da REGRESS_SLOPE(price, quantity) gibi bir sözdizimi kullanılmasına olanak tanıyacaktı. Şık ve yeniden kullanılabilir olmasına rağmen, farklı veritabanı sistemleri arasında taşınabilirliği feda ediyordu ve dağıtmak için yükseltilmiş yönetim ayrıcalıkları gerektiriyordu, bu da kilitli, çok kiracılı bir bulut veri ambarı ortamı için uygun hale getirmiyordu.
Seçilen çözüm, standart toplamları kullanarak doğrudan ANSI SQL’de cebirsel formülleri uygulamaktı. Ekip, gerekli kovaryans ve varyans terimlerini verilerin üzerinde tek bir geçişte hesaplamak için category_id ile bölümlenmiş SUM ve AVG pencere fonksiyonlarını kullandı. Bu yaklaşım, hesaplamayı verilerle birlikte tuttu, çıkartma-dönüştürme-yükleme (ETL) gecikmelerini ortadan kaldırdı ve özel uzantılar olmaksızın taşınabilir ANSI SQL standartlarına katı bir şekilde uydu. Sonuç, yeni işlemler geldikçe otomatik olarak güncellenen, alt bir saniye gecikmeli bir fiyat esnekliği gösterge panosuydu ki bu da otomatik fiyatlandırma algoritmalarının marjları gerçek zamanlı olarak ayarlamasını sağladı.
X veya Y'deki NULL değerlerini, grubun hesaplamasını geçersiz kılmadan nasıl ele alırsınız?
Adaylar genellikle ANSI SQL toplam fonksiyonlarının NULL değerlerini görmezden geldiğini unutur ancak NULL içeren aritmetik işlemler NULL döner. Kovaryans terimini hesaplarken, belirli bir satırda x veya y NULL olursa, çarpım NULL olur ve bu satır toplamdan çıkarılır. Bu etkin bir şekilde ikili silme gerçekleştirir, genellikle istenen bir durumdur, ancak varyans hesaplamalarındaki serbestlik derecelerinde kullanılan COUNT değerinin NULL olmayan çiftleri yansıttığından emin olmak gerekir, tüm satırları değil. Çözüm, bir alt sorguda WHERE x IS NOT NULL AND y IS NOT NULL filtrelemek ya da filtrelemeden sonra COUNT(x) (bu COUNT(y) ile eşit olur) kullanmak, tüm toplam terimleri arasında tutarlı paydalar sağlamak için gerekir.
Bir popülasyon üzerinde regresyon hesaplamakla bir örnek üzerinde hesaplamak arasındaki fark nedir ve bu SQL sorgunuzu nasıl etkiler?
Birçok aday, kovaryans formülüyle tutarsız bir şekilde örnek varyans formülünü (n - 1 ile bölme) uygular. ANSI SQL'de, VAR_POP ve VAR_SAMP gibi yerleşik fonksiyonlar bu ayrımı yönetir, ancak varyansı SUM(POWER(x - avg_x, 2)) / COUNT(*) olarak manuel şekilde hesapladığınızda, paydada bilinçli bir seçim yapmanız gerektiğini bilmelisiniz. Eğim hesaplaması için, eğer X'in varyansını payda olarak manuel olarak hesaplıyorsanız, bunu kovaryans hesaplamasındaki bölümle eşleştirmeniz gerekir. Karıştırmak (örneğin, örnek kovaryansı popülasyon varyansına bölmek), eğim için yanlı bir sonuç verir. Düzeltici yaklaşım, istatistiksel çerçeveyi (popülasyon ile örnek arasında) belirlemek ve her iki kovaryans payı ve varyans paydası için aynı bölüm mantığını (n veya n-1) uygulamaktır.
Aynı sorguda uyumun iyiliğini ölçmek için karar katsayısını (R²) nasıl hesaplayabilirsiniz?
Adaylar genellikle doğrulama metriklerini atlar. R², SS_res'in kareler toplamı (Σ(y - ŷ)²) ve SS_tot'un toplam kareler toplamı (Σ(y - ȳ)²) ile 1 - (SS_res / SS_tot) olarak hesaplanır. ŷ'yi (tahmin edilen y) hesaplamak, önceki adımlarda hesaplanan eğimi ve kesişimi gerektirir. ANSI SQL'de, bunu dizilmiş Ortak Tablo İfadeleri (CTE'ler) kullanarak hesaplayabilirsiniz: önce ortalamaları hesapla, sonra ikinci CTE'de eğimi ve kesişimi hesapla ve son olarak dış sorguda gerçek ve tahmin edilen değerler arasındaki kare farklarını hesapla. Yaygın bir hata, hesaplanan eğimi, hesaplandığı aynı toplama seviyesinde referans almayı denemektir; bu, mantıksal işleme sırasını ihlal eder. Çözüm, hesaplanan katsayıların son aggretasyon için sabitler olarak yeniden kullanılmasına olanak tanımak için mantığı ardışık CTE'lere ayırmaktır.