Cevap.

Sorunun geçmişi.

SQL:2016 standartlarından önce, sıralı veri kümesi içinde çok satırlı ardışık desenleri tespit etmek karmaşık kendine bağlantılı sorgular, işaretçi tabanlı prosedürel mantık veya sonlu durum makinelerini simüle eden yinelemeli CTE'ler gerektiriyordu. Bu yaklaşımlar kombinatoryal patlama, düşük performans ve bakım sorunları ile karşı karşıyaydı. MATCH_RECOGNIZE ifadesinin tanıtılması, ardışık desen tanıma için düzenli ifadeler temelinde matematiksel olarak sıkı bir sözdizimi sağlayarak karmaşık olay işleme yeteneği kazandırdı.

Sorun.

Belirli değişken uzunlukta dizileri tespit etmek, her satırı birçok önceki ve sonraki ile karşılaştırmayı gerektirirken, tüm dizi boyunca bağlamsal durumu korumak zorundadır. Standart pencere işlevleri yalnızca sabit kaydırmaları referans alabilir (örneğin, LAG 1, LEAD 1), bu da bacak sürelerinin değişken olduğu desenleri yönetme konusunda yetersiz kalır. Yinelemeli CTE'ler, durum geçişlerini teorik olarak izleyebilir fakat çok aşamalı desenleri katı sıralama kısıtlamaları ile yönetirken hesaplama açısından maliyetli ve sözdizimsel olarak uzun hale gelir.

Çözüm.

MATCH_RECOGNIZE, desen değişkenlerinin tanımını boolean koşulları ile yapmayı, hedef deseni düzenli ifade sözdizimi ile belirtmeyi (örneğin, A B+ C+ D+ E+) ve eşleşen satırlar üzerindeki toplu ölçümleri hesaplamayı sağlar. Bölme, sıralama ve navigasyon işlevlerini (PREV, NEXT, FIRST, LAST) yerel olarak yönetir.

SELECT *
FROM stock_ticks
MATCH_RECOGNIZE (
  PARTITION BY symbol
  ORDER BY tick_time
  MEASURES
    STRT.price AS start_price,
    FINAL LAST(DOWN1.price) AS first_trough,
    FINAL LAST(UP1.price) AS middle_peak,
    FINAL LAST(DOWN2.price) AS second_trough,
    FINAL LAST(UP2.price) AS end_price,
    MATCH_NUMBER() AS pattern_id
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO LAST UP2
  PATTERN (STRT DOWN1+ UP1+ DOWN2+ UP2+)
  DEFINE
    DOWN1 AS DOWN1.price < PREV(DOWN1.price),
    UP1   AS UP1.price > PREV(UP1.price),
    DOWN2 AS DOWN2.price < PREV(DOWN2.price) 
        AND DOWN2.price < FIRST(UP1.price),  -- Orta zirve altına inmeli
    UP2   AS UP2.price > PREV(UP2.price)
) AS pattern_matches;

Gerçek hayattan bir durum

Bağlam.

Bir nicel ticaret firması, yüksek frekanslı döviz verileri (tik-tik) içindeki W şeklinde çift dip desenlerini tespit etmek için uzun pozisyonlar için giriş noktalarını otomatikleştirmek istedi. Desen, iki ayrı çukurların bir zirve ile ayrıldığı ve her bacağın en az %0.5 fiyat hareketi temsil ettiği bir yapı gerektiriyordu.

Sorun.

Veri kümesi günlük olarak 50 döviz çiftinde 10 milyon satır içeriyordu. Python tabanlı tespit, saatlik gigabaytlarca veri aktarımı sırasında ağ gecikmesine ve bellek kısıtlamalarına yol açtı. Birden fazla LAG()/LEAD() kendine bağlantılı sorguları kullanarak standart SQL yaklaşımları, W deseninin dört bacağını ilişkilendirmeye çalışırken Kartaş ürünleri oluşturdu ve sorgular 10 dakika sonra zaman aşımına uğradı.

Çözüm 1: İstemci tarafında Python işlemesi.

Ekip, zirveleri ve çukurları tespit etmek için özel döngü mantığı ile pandas kullandı. Artılar: Zengin analitik kütüphaneler, kolay birim testi. Eksiler: Büyük veri aktarım darboğazı (saatlerce gecikme), tam piyasa geçmişini işlerken uygulama sunucusunda bellek tükenmesi ve gerçek zamanlı tepki verememe durumu.

Çözüm 2: Yinelemeli CTE durum makinesi.

Beş durumu (0=başlangıç arayışı, 1=ilk düşüş, 2=ilk artış, 3=ikinci düşüş, 4=ikinci artış) izleyen bir yinelemeli CTE denemesi yaptılar. Artılar: Saf SQL, mantıksal olarak sıkı. Eksiler: Veritabanı motorunda tek iş parçacıklı yürütme, derin yineleme ile üstel yavaşlama ve değişken dizileri üzerinde yığın taşması hatalarına eğilimli 300+ satırlık anlaşılmaz SQL.

Çözüm 3: MATCH_RECOGNIZE uygulaması.

Ekip yukarıda belirtilen SQL:2016 desen eşleştirme sorgusunu uyguladı. Artılar: Yerel motor optimizasyonu (vektörlü yürütme), matematiksel desen tanımı ile tam olarak örtüşen 25 satırlık özlü sorgu, değişken uzunlukta bacakların otomatik yönetimi (+ ile) ve yeniden üst üste gelen eşleşmeleri önlemek için etkili atlama işlevi. Eksiler: SQL:2016 özelliklerini destekleyen Oracle 19c'ye veritabanı göçü gerektirdi ve SQL'deki düzenli ifade sözdizimi ile tanımayan geliştiriciler için başlangıç eğitimi gerekti.

Seçilen çözüm ve sonuç.

çözüm, geçmiş testlerdeki alt-saniye performansı nedeniyle seçildi. AFTER MATCH SKIP TO LAST UP2 ifadesi, bir W-deseni tamamlandığında taramanın desenin sonundan devam etmesini sağladı ve yeniden üst üste gelen tespitlerin önüne geçti. Sistem, manuel olarak doğrulanan W-deseni örneklerinin %99.8'ini başarıyla tespit etti ve tespiti gecikmeyi Python'dan 45 dakikadan 800 milisaniyeye düşürdü, bu da gerçek zamanlı algoritmik ticareti sağladı.

Adayların sıklıkla atladığı konular

AFTER MATCH SKIP ifadesi eşleşmeden sonraki devam noktasını nasıl belirler ve neden SKIP TO NEXT ROW ile SKIP PAST LAST ROW'un üst üste gelen desenler için önemi vardır?

AFTER MATCH SKIP, desen eşleştiricinin taramaya devam ettiği yeri belirler. SKIP PAST LAST ROW (varsayılan) mevcut eşleşmenin son satırının ardından devam eder, böylece hiçbir satır birden fazla eşleşmede yer almaz - ayrı olay tespit için uygundur. Tersine, SKIP TO NEXT ROW, eşleşmenin başlangıç satırının hemen ardından devam eder, üst üste gelen eşleşmelere olanak tanır. Bu, bir çukurun iki ardışık W-deseni medianın tabanı olabileceği finansal zaman serilerinde kritik öneme sahiptir (üst üste gelen pencereler). Adaylar genellikle standart atlamaya varsayılan olarak geçiyorlar ve istemeden geçerli olan üst üste gelen sinyalleri süzüyorlar ve algılama hassasiyetini azaltıyorlar.

MEASURES ifadesindeki RUNNING ve FINAL anlamları arasındaki fark nedir ve bu, değişken uzunluktaki desenler içinde toplu hesaplamaları nasıl etkiler?

RUNNING, eşleşme oluşturulurken her ardışık satırda bir ifadeyi değerlendirir (örneğin, düşüş bacağında hareketli ortalama hesaplayarak). FINAL, ifadenin yalnızca tamamlanmış eşleşmenin son satırında bir kez değerlendirilmesini sağlar ve tüm desen değişkenleri için son bağlı değerleri kullanır (örneğin, desenin başlangıcından sonuna kadar toplam yüzde değişimi hesaplayarak). Adaylar sıklıkla desen genelindeki ölçümler gibi MAX(leg_price) - MIN(leg_price) hesaplarken FINAL anahtar kelimesini atlıyorlar, bu da tamamlanmamış eşleşmelerden ara değerlerin döndürülmesine neden oluyor ve yanlış ticaret sinyali hesaplamalarına yol açıyor.

Boş eşleşmeleri nasıl işlersiniz ve eşleşmemiş satırların hata ayıklama amacıyla çıktıda görünmesini nasıl sağlarsınız?

Varsayılan olarak, MATCH_RECOGNIZE herhangi bir eşleşmede yer almayan satırları süzüyor. Eşleşmemiş satırları da dahil etmek (bazı dizilerin neden desen kriterlerine uymadığını denetlemek için gerekli) için ALL ROWS PER MATCH ile SHOW EMPTY MATCHES belirtilmelidir. Bu modda, her girdi satırı çıktı üretir ve desen ölçüleri eşleşmeler dışında kalan satırlar için NULL döner. Ayrıca, MATCH_NUMBER() eşleşmemiş satırlar için NULL döner. Adaylar genellikle "kaybolan veriler" hata ayıklama sürecinde zorluk çekiyorlar ve katı DEFINE koşullarının geçerli satırları filtrelediğinin farkında olmadan, hangi belirli boolean koşulunun (örneğin, ikinci çukurun birincisinden daha düşük olmaması) desen reddine neden olduğunu teşhis etmek için SHOW EMPTY MATCHES kullanmamayı ihmal ediyorlar.