Üstel hareketli ortalama (EMA), 20. yüzyılın ortalarında teknik analizde daha fazla ağırlık veren bir düzleme alma tekniği olarak ortaya çıkmıştır. Basit hareketli ortalamaların aksine, EMA hesabı, her bir değerin önceki hesaplanan EMA değerine bağlı olduğu ve vektörleştirmeye direnç gösteren bir bağımlılık zinciri yarattığı için bir yeniden hesaplama matematiksel özelliğine sahiptir. Bu özellik, standart pencere fonksiyonlarının statik çerçevelerde çalıştığı için SQL'de uygulamasını son derece zorlaştırır. Görüşmeciler, bu soruyu, bir adayın ANSI SQL'in rekürsif yeteneklerini anlama kabiliyetini ve yinelemeli algoritmaları deklaratif küme mantığına dönüştürme becerisini değerlendirmek için soruyor.
Matematiksel olarak, zaman t’de EMA şöyle tanımlanır: EMAt = α × Price_t + (1-α) × EMA{t-1}, burada α, düzleme alma faktörüdür (tipik olarak N- dönem ortalaması için 2/(N+1)). Temel durum, ilk dönemin fiyatını başlangıç EMA'sı olarak kullanır. Bir veritabanı bağlamında, zaman damgasına göre sıralanmış milyonlarca satır arasında bu sürekli hesaplamayı sürdürme zorluğuyla karşılaşırız; her bir satır, önceki satırın hesaplanan sonucuna erişim gerektirir. Standart ANSI SQL toplu fonksiyonları SUM veya AVG bu rekürsif bağımlılığı ifade edemez ve ROWS veya RANGE kısıtlamalarıyla pencere fonksiyonları yalnızca ham giriş değerlerine erişir, daha önceki satırlardan hesaplanan çıktılara değil.
Bunu, sıralı veri kümesini ardışık olarak geçiş yapan bir rekürsif CTE (Ortak Tablo İfadesi) kullanarak uygularız. İlk olarak, boşlukları veya düzensiz zaman damgalarını ele almak için deterministik bir satır sırası belirliyoruz ve ROW_NUMBER() kullanıyoruz. Temel üye, her bir bölüm için ilk satırı seçer (örneğin,hisse senedi sembolü), başlangıç EMA'sını ilk fiyatla eşitler. Rekürsif üye, ardından CTE'yi bir sonraki ardışık satıra (satır_numarası = önceki + 1) bağlar ve önceki yinelemenin hesaplanan değerini kullanarak EMA formülünü uygular. Bu yaklaşım kesinlikle ANSI SQL:1999 standartlarına uygun olup, tek bir küme tabanlı işlem olarak çalışır.
WITH RECURSIVE numbered_trades AS ( SELECT symbol, price, trade_time, ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY trade_time) AS rn FROM trades ), ema_series AS ( -- Temel: her sembol için ilk satır SELECT symbol, price, rn, price AS ema -- İlk EMA ilk fiyata eşit FROM numbered_trades WHERE rn = 1 UNION ALL -- Rekürsif: takip eden satırlar için EMA hesapla SELECT t.symbol, t.price, t.rn, 0.2 * t.price + 0.8 * e.ema AS ema -- α = 0.2 için 9-dönem EMA FROM ema_series e JOIN numbered_trades t ON t.symbol = e.symbol AND t.rn = e.rn + 1 ) SELECT symbol, price, ema, rn FROM ema_series ORDER BY symbol, rn;
Bir nicel ticaret firması, yeni bir algoritmayı doğrulamak için 5,000 hisse senedi sembolü üzerinde beş yıl süresince tarihi tik verileri için EMA göstergelerini geri doldurması gerekiyordu. Veri kümesi, yüksek frekanslı piyasa verilerinin 250 milyon satırını içeriyordu ve mevcut Python Pandas çözümü, ağ üzerinden gigabaytlarca veri aktarmak zorunda kaldı, bu da analiz istasyonunda sık sık zaman aşımına ve bellek hatalarına yol açtı.
Ekip ilk olarak, Pandas ewm() yöntemini kullanarak bir Python ön işleme betiği uygulamayı düşündü. Bu yaklaşım hızlı prototipleme ve nicel analistler için tanıdık bir sözdizimi sundu ve rekürsif hesaplamayı yerel olarak optimize edilmiş C uzantılarıyla halletti. Ancak, PostgreSQL veritabanı ile uygulama sunucusu arasında önemli veri transferi yükü getirdi, milyonlarca satırı belleğe yüklemeyi gerektirdi ve EMA hesaplamasını kesit sınırları boyunca devam ettirecek karmaşık parça mantığı gerektirdi.
İkincisi, her bir satırın son 200 dönemde tüm önceki satırlara katıldığı ve geometrik ağırlıkların uygulandığı SELF JOIN ile saf bir küme tabanlı yaklaşımı incelediler. Bu yöntem tamamen rekürsiyonu ortadan kaldırdı ve teorik olarak veritabanı optimizasyoncunun işlemi paralelleştirmesine izin verdi. Ancak, 200 dönemlik bakış penceresi ile orantılı olarak O(n²) karmaşıklığına sahipti, bu da yüksek frekanslı tik verilerini işlerken tempdb'yi aşırı yükleyen büyük ara sonuç kümesi oluşturdu ve sonlu pencere kesilmesi nedeniyle yalnızca gerçek EMA'nın bir yaklaşık değerini sağladı.
Üçüncüsü, ANSI SQL standart sözdizimi kullanan rekürsif CTE çözümünü değerlendirdiler. Bu yaklaşım tamamen veritabanı motoru içinde çalışarak ağ transfer yükünü ortadan kaldırdı ve matematiksel olarak kesin EMA'yı hesapladı. Aşırı uzun sembol geçmişlerinde rekürsiyon derinliği limitlerine ulaşma riski taşımakla birlikte ve çoğu ANSI SQL uygulamasında sembol başına tek iş parçacığında çalışmasına rağmen, bellek açısından verimliydi ve kendinden birleşim yönteminin kareli patlamasını önledi.
Ekip, veri hareketini ortadan kaldırdığı, Pandas ile birebir sayısal hassasiyet sağladığı ve harici bağımlılıklar olmadan veritabanı yerel malzeme görseli yenileme olarak programlanabileceği için rekürsif CTE yaklaşımını seçti. DBA, en uzun sembol tarihini (sembol başına yaklaşık 50,000 tik) karşılamak için max_recursive_iterations parametresini yapılandırdı.
Uygulama, 250 milyon satırlık veri kümesini yaklaşık 12 dakika içinde işledi. Elde edilen EMA değerleri, Pandas hesaplamalarıyla kayan nokta hassasiyetine kadar eşleşerek SQL uygulamasının matematiksel doğruluğunu doğruladı. Firma, daha sonra sorguyu geceye özgü bir malzeme görseli yenileme olarak üretime aldı ve harici Python betiklerine olan ihtiyacı ortadan kaldırarak veri boru hattı karmaşıklığını önemli ölçüde azalttı.
Kaynak tablo sıralamada boşluklar veya mükemmel bir tam sayı sırası oluşturmayan düzensiz zaman damgaları içeriyorsa hesaplamayı nasıl ele alırsınız?
Birçok aday, trade_time veya bir ID sütununun rn = e.rn + 1 birleşimleri için uygun bir yoğun sıra sağladığını varsayar. Gerçekte, kaybolan tıklar veya silinen kayıtlar, rekürsiyon zincirini kırarak boşluklar oluşturur. Çözüm, rekürsif CTE'den önce yoğun bir sıralama gerçekleştirerek ROW_NUMBER() veya DENSE_RANK() oluşturmayı gerektirir, bu da zaman damgalarındaki boşluklara rağmen ardışık tam sayıları sağlar. Bu, mantıksal sıralamayı fiziksel anahtar değerlerinden sıyırır ve ardışık dizinin kesintisiz bir şekilde ilerlemesine izin verirken doğru zamansal sıralamayı korur.
Peki, rekürsif CTE yaklaşımı, son derece uzun zaman serileri (örn. 100,000+ satır) için neden başarısız olabilir ve bunu ANSI SQL kısıtları içinde nasıl hafifletirsiniz?
Adaylar, ANSI SQL standardının sonsuz rekürsiyon derinliğini zorunlu kılmadığını ve PostgreSQL 'nin 1000 yineleme varsayılanı, SQL Server'ın ise 100 olduğunu gözden kaçırıyor. Bu limitlerin aşılması, sorgunun iptal edilmesine neden olur. Hafifletme, bir kontrol tablosu veya yinelemeli yaklaşım kullanarak toplu işleme ile sağlanır ancak katı şekilde ANSI SQL içinde, oturumun rekürsiyon limitini artırmalı (non-ANSI) veya sabit bakış pencereleri (örn. 200 dönem) üzerinden yaklaşık EMA'yı hesaplamak için pencere fonksiyonlarını kullanarak hibrit bir yaklaşım uygulamalısınız ve burada üstel düşüş eski katkıları önemsiz hale getirir. Kesin hesaplamalar için, platformun rekürsiyon limitinin maksimum dizi uzunluğunuzu aşmasını sağlamalı veya bu soru kısıtlamaları içinde bir saklı prosedür döngüsü (yasak) kullanmalısınız.
Birden fazla bağımsız zaman serisi (örn. farklı hisse senedi sembolleri) için EMAları aynı anda tek bir rekürsif sorguda hesaplarken nasıl çapraz kontaminasyonu önlersiniz?
Yaygın bir hata, rekürsif birleşim ön koşusundan bölüm anahtarını atlamaktır. Adaylar t.rn = e.rn + 1 yazıp t.symbol = e.symbol eklemeyi unutur, bu da satır numaraları uyum sağladığında rekürsiyonun farklı sembollere geçmesine neden olur. Doğru uygulama, hem temel hem de rekürsif üyeler aracılığıyla bölüm anahtarını taşıyarak, hem sıralama numarası artışında hem de bölüm eşitliğinde kesinlikle birleştirme yapılmasını gerektirir. Bu, rekürsiyon ağacının her sembol için izole kalmasını sağlar ve etkili bir şekilde tek CTE yürütmesi içinde ayrı hesaplama bağlamları oluşturur.