Bu desen, finansal veritabanlarından kaynaklanan bir as-of join veya en yakın önceki eşleşme olarak bilinir; işlem olaylarının, yürütme zamanında geçerli en son alıntıyla eşleştirilmesi gerektiği yerlerde kullanılır. IoT sensör kalibrasyonları veya çalışan departman geçmişi gibi, ayrık olaylar ve yavaşça değişen boyutlarla herhangi bir alana genelleştirilir. Zorluk, set tabanlı performanstan ödün vermeden zamansal dolaşım yapmaktır.
Naif bir yaklaşım, her satır için alt sorguyu çalıştırmaya zorlayan bir ORDER BY ve FETCH FIRST 1 ROW ONLY ile ilişkili bir ölçekli alt sorgu kullanır (RBAR), bu da O(n²) karmaşıklığına ve kötü önbellek yerelliğine yol açar. Alternatif olarak, olaylar ve referans noktaları arasındaki bir eşitsizlik bağlantısı (<=), büyüklüğünü filtrelemeden önce patlayan yarı Kartezyen ürün oluşturarak büyük veri kümesi üzerinde disk taşmalarına neden olabilir. Her iki yaklaşım da milyonlarca satır işlenirken zaman aşımına neden olma riski taşır.
Sağlam bir çözüm, zaman damgası anahtarları üzerinde eşitsizlik bağlantısı kullanarak, ardından olay Kimliği ile bölümlenmiş ve referans zamanının azalan sırasına göre sıralanan ROW_NUMBER() pencere işlevini kullanmaktadır. row_num = 1 için filtreleme, yalnızca en yakın önceki eşleşmeyi tutarak işlemi, optimizasyonların hash veya birleştirici bağlantılarla gerçekleştirebileceği set tabanlı bir sıralama ve filtreleme haline getirir.
WITH matches AS ( SELECT e.event_id, e.event_time, e.reading, r.calibration_value, ROW_NUMBER() OVER ( PARTITION BY e.event_id ORDER BY r.valid_from DESC ) AS rn FROM events e JOIN reference r ON r.sensor_id = e.sensor_id AND r.valid_from <= e.event_time ) SELECT event_id, event_time, reading, calibration_value FROM matches WHERE rn = 1;
Bir üretim tesisi, her saniye 5,000 sensörden titreşim verisi toplar ve vibration_logs içinde saklar. Her bir sensör için kalibrasyon katsayıları düzensiz bir şekilde sensor_calibrations içinde güncellenir (yaklaşık olarak ayda bir kez). Analitik ekip, her ham okumanın geçerli olduğu mikro saniyede aktif kalibrasyon faktörü tarafından ayar yapılması gerektiğini hissetmektedir, ancak naif ilişkili alt sorgu her partide 3 dakikadan fazla sürer ve alım borusunu tıkar.
Çözüm A (İlişkili Alt Sorgu): Bu yaklaşım, her titreşim kaydı satırı için en son kalibrasyonu seçmek amacıyla ilişkili bir ölçekli alt sorguya dayanmaktadır. Veritabanı motoru, dış satır başına bir kez bu alt sorguyu değerlendirir ve genellikle eşleşen kayıtları bulmak için calibrated_at zaman damgasında bir B-ağaç indeks araması kullanır. Bu doğru sonucu geri döndürse de, optimizasyonun hash veya birleştirme bağlantılarını kullanmasını engeller ve gömülü bir döngü oluşturur.
Çözüm B (Eşitsizlik Bağlantısı ve Pencere İşlevi ile): Bu yöntem, belirli bir sensörün olay bölümündeki her olası kalibrasyon eşleşmesine sıralı bir rütbe atamak için bir eşitsizlik bağlantısı ve ROW_NUMBER() pencere işlevi kombinasyonu kullanmaktadır. Bağlantı, tüm aday çiftleri ürettiğinde, pencere işlevi bunları kalibrasyon zamanına göre azalan sırada sıralar ve rütbe 1 için filtre uygular. Bu, mantığı toplu işleme uygun olan set tabanlı bir işleme dönüştürür.
Çözüm C (Koşullu Mantık ile Union-All): Bu strateji, iki tabloyu UNION ALL yoluyla tek bir kronolojik akışta birleştirir ve ardından ardından gelen olay satırları aracılığıyla en son bilinen kalibrasyonu taşımak için LAST_VALUE(... IGNORE NULLS) kullanmaya çalışır. Bu yaklaşım teorik olarak her tabloyu yalnızca bir kez tarar ve birleştirme patlaması olmaz.
IGNORE NULLS katı bir ANSI SQL değildir (opsiyonel özellik T611); olmadan, mantık karmaşık hale gelir ve sayısal olmayan öznitelikler için başarısız olur; birleşik akışı sıralamak gerektirir.Seçilen Çözüm: Çözüm B, PostgreSQL sorgu optimizatörünün Partial Merge Join ile pencere işlevi için bir Sort operatörü gerçekleştirebileceğini doğruladıktan sonra seçildi. Ara bağlantının malzeme alımının 2GB RAM ile 10 milyon satır için kabul edilebilir olduğu kabul edildi. Ayrıca, bu yaklaşım, Çözüm A'da görülen gömülü döngülerin belirsiz performansını önledi.
Sonuç: Sorgu yürütme süresi, üretim veri setinde 45 saniyeden 1.2 saniyeye düştü. Boru hattı şimdi, sürekli alım akışını engellemeksizin gerçek zamanlı olarak saatlik partileri işler. Bu, analitik ekibin yalnızca beş dakikalık bir gecikme ile kalibre edilmiş titreşim raporları üretmesine olanak tanıdı.
Eşitsizlik bağlantısının ROW_NUMBER() ile ilişkili alt sorgu ile aynı O(n²) performansına neden olmamasının sebebi nedir, kavramsal olarak büyük bir ara set üretmesine rağmen?
İlişkili alt sorgu bağımlıdır; her dış satır için yeniden değerlendirilmeli ve genellikle bir gömülü döngü ile sonuçlanır. Eşitsizlik bağlantısı ise ** bağımsızdır**; optimizatör, Kartezyen benzeri ürünü üretmek için bir hash join veya merge join seçebilir ve ardından pencere işlevini uygulayabilir. Kritik olarak, modern motorlar, ROW_NUMBER() = 1 filtreleri için top-N optimizasyonu uygular; bu, her bölüm için ilk satırı bulduktan sonra sıralamayı durdurur, işlemi bir dizin arama veya her olay için hash probuna dönüştürerek tüm tarihçeyi sıralamaktan kurtarır.
Bir kalibrasyon kaydı var olmadan önce meydana gelen olaylarla nasıl başa çıkarsınız, böylece bunlar atılmak yerine bir varsayılan değer alırlar?
Eşitsizlik bağlantısı (<=), birleşim koşulu başarısız olduğundan en az referans zamanından önceki olayları doğal olarak dışlar. Bunları dahil etmek için bir LEFT JOIN kullanabilir, ardından referans değerini COALESCE içinde sarmalayarak varsayılan bir değer koyabilirsiniz. Ayrıca, referans tablosuna valid_from = '1900-01-01' olan bir sentinelli satır ekleyerek her olayın en az bir önceki eşleşmeye sahip olmasını sağlayabilirsiniz. Bu, ilişkisel kapanışı garanti eder ve son filtreleme mantığı gerektirmez.
Bu problem, her iki veri seti de tek bir birleşik tabloda bulunuyorsa, tabloları birleştirmeden yalnızca PENCERE işlevinde RANGE ifadesi kullanılarak çözülebilir mi?
Hayır. RANGE ifadesi, sıralama sütununun değerine dayanarak mevcut sonuç kümesinin satırları üzerinde çalışır; fiziksel olarak ayrı bir tablodan değerleri seçerek arama yapamaz. Her iki tabloyu UNION ALL ile birleştirseniz bile, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW tüm önceki satırları, diğer olaylar dahil olmak üzere kapsar, yalnızca kalibrasyon satırlarını değil. Sadece kalibrasyon satırlarını izole etmek için, LAST_VALUE ile birlikte IGNORE NULLS kullanmalısınız, bu da katı bir ANSI SQL değildir (opsiyonel özellik T611). Bu nedenle, iki ayrı ilişkisel kaynağı birleştirmek için birleştirme işlemi zorunludur.