Tarihçe: ANSI SQL:2003'ten önce analitik fonksiyonları tanıtmadan, seyrek zaman serisi verileri doldurmak verimsiz kendi kendine birleştirme veya satırları bireysel olarak işleyen prosedürel işaretleyiciler gerektiriyordu. LOCF deseni SAS ve R gibi istatistik paketlerinde ortaya çıktı ve son bilinen gözlemi ileri taşımak standart bir veri temizleme tekniği oldu. Daha sonra veritabanı satıcıları bu mantığı SQL içinde pencere fonksiyonlarıyla uyguladı ve IGNORE NULLS ifadesi ANSI SQL:2011'de bu tür boşlukları belirsiz olarak ele almak için özel olarak resmileştirildi.
Sorun: Sensör ağları ve finansal ticaret sistemleri, iletim hataları veya işlem yapılmayan saatler nedeniyle sıklıkla NULL değerleri üretir. Basit LAG fonksiyonları, hemen önceki değeri döndürdüğü için başarısız olur; bu öncelik de NULL olabilir ve hesaplanan metriklerde boşluklar yaratır. Zorluk, performansı kare olarak düşüren kendi kendine birleştirmeleri kullanmadan, sıralı bir bölmede geriye doğru en son bulunan null olmayan değeri bulmayı gerektiriyor.
Çözüm: IGNORE NULLS seçeneği ile LAST_VALUE pencere fonksiyonunu ve çerçeve tanımını, bölüm başlangıcından mevcut satıra kadar uzanan bir çerçeve belirleyeceğiz. Bu yapı, motoru NULL değerleri geri alırken, geçerli son gözlemi almak için null olmayan değerlerin sürekli bir tamponunu koruyacak şekilde yönlendirir. IGNORE NULLS olmayan sistemler için, stabil gruplar oluşturmak için null olmayanların COUNT kullanılarak bir çözüm yolu oluşturulursa, bu teknik olarak bir alt sorgu içerir.
SELECT device_id, reading_time, temperature, LAST_VALUE(temperature IGNORE NULLS) OVER ( PARTITION BY device_id ORDER BY reading_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS locf_temperature FROM sensor_readings;
Bir sağlık bilişim platformu, giyilebilir cihazlar kullanarak diyabetik hastalar için sürekli glukoz seviyelerini izler. Bluetooth parazitleri nedeniyle, yaklaşık %12 oranında okuyucu NULL olarak gelir, fakat klinik uzmanlar, interpolasyonun tıbbi olarak tehlikeli olabileceği durumlarda insülin doz hesaplamaları için eksiksiz eğriler gerektirir. Doğru LOCF mantığı kritik öneme sahiptir çünkü uyku veya öğün sırasında kaybolan değerler yanlış hipoglisemi uyarıları yaratabilir.
Çözüm A: İmleç tabanlı prosedürel güncelleme. Bir PL/SQL saklı yordamı, hasta kayıtlarını kronolojik olarak döngüye alır, son geçerli glukoz okumasını saklamak için bir oturum değişkeni korur ve NULL satırlarını hemen günceller. Avantajları: pencere fonksiyonu desteğinden önceki eski Oracle sürümleriyle uyumlu; zorunlu programlama geçmişine sahip geliştiriciler için anlaşılması kolay. Dezavantajları: satır bazında işleme aşırı G/Ç ve tablo kilitlenmesine neden olur; 10 milyon satırı işlemek 45 dakika sürer, bu da gerçek zamanlı panoları imkansız hale getirir.
Çözüm B: Kendi kendine birleştirme ile ilişkili alt sorgu. Sorgu, glukozun NULL OLMADIĞI mevcut satırdan daha önceki maksimum zaman damgasını bulmak için sol bir birleştirme gerçekleştirir ve her boşluk için bir önceki değeri sorgular. Avantajları: prosedürel kod olmadan açıklayıcı SQL; ANSI SQL-92 uyumlu sistemlerde çalışır. Dezavantajları: O(n²) karmaşıklığı nedeniyle üssel yavaşlamaya neden olur; sorgu, tekrarlanan tam tablo taraması nedeniyle üretim veri setlerinde 6 saat sonra zaman aşımına uğrar.
Çözüm C: IGNORE NULLS ile pencere fonksiyonu. Zamanı dikkate alarak hasta bazında bölümlenmiş ve sıralı LAST_VALUE(glucose IGNORE NULLS) uygulayarak dizin üzerinden tek bir geçiş gerçekleştirir. Avantajları: O(n log n) karmaşıklığı aynı 10 milyon satırlık veri setinde 28 saniyede çalışır; minimal bellek ayak izi ve kilitlenme sorunu yok. Dezavantajları: ANSI SQL:2011 desteği gerektirir, bu da mevcut PostgreSQL 9.5 örneğinden bir veritabanı güncellemesi gerektirir.
Ekip, veritabanı güncelleme maliyetinin %99 performans arttırımı ile haklı olduğuna karar verdikten sonra Çözüm C'yi seçti. Uygulama, gerçek zamanlı glukoz uyarılarını sağladı ve sunucu CPU kullanımını %94 azaltarak, poliklinik 50.000 eşzamanlı hastayı herhangi bir gecikme veya kritik glukoz dalgalanmalarını kaçırmadan izledi.
Soru 1: IGNORE NULLS olmadan LAST_VALUE neden mevcut olmayan bir değer varken NULL döndürür?
Varsayılan olarak LAST_VALUE, mevcut satırı da içeren çerçeveyi değerlendirir. Mevcut satır NULL içeriyorsa ve çerçeve CURRENT ROW'a kadar uzanıyorsa, fonksiyon bu NULL'yu pencere içindeki son değer olarak görür. Adaylar genellikle fonksiyonun sonsuz geriye taradığını varsayıyor; ancak IGNORE NULLS olmadan, NULL'ları geçerli değerler olarak değerlendirir. Pencere çerçevesi ROWS UNBOUNDED PRECEDING, mevcut satırı içerdiğinden LAST_VALUE, açıkça NULL'ları dikkate almadan, mevcut satırın değeri ile eşdeğerdir.
Soru 2: 2011 öncesi ANSI SQL'de IGNORE NULLS olmadan LOCF nasıl uygulanabilir ve COUNT yerine ROW_NUMBER farklılıkları kullanmanın mantıksal hatası nedir?
COUNT(non_null_col) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING) kullanarak yalnızca null olmayan değerlere rastlandığında artan bir tanımlayıcı oluşturabilirsiniz. Tüm sonraki NULL'lar bu sayıyı paylaşır ve bir taşıma grubu oluşturur. Adaylar bazen ROW_NUMBER() OVER (ORDER BY ...)'dan ROW_NUMBER() OVER (PARTITION BY non_null_flag ORDER BY ...) çıkarma denemesi yaparlar. Bu, yeni gruplar oluşturduğu için başarısız olur; bu, NULL'lar arasındaki boşluklar için önceki grubu ileri taşımak yerine her boşluk için yeni gruplar oluşturur. COUNT yöntemi, son bilinen değer dönemi için stabil bir tanımlayıcı ürettiği için çalışır.
Soru 3: Duplicatelerle zaman damgaları için LOCF’de ROWS yerine RANGE çerçevesi kullanırken, neden sonuçlar belirsiz hale gelebilir?
RANGE çerçevesi, aynı ORDER BY değerlerine sahip satırları eş grup olarak toplar ve onları tek bir birim olarak değerlendirir. Eğer birden fazla sensör okuması aynı milisaniye zaman damgasını paylaşıyorsa, RANGE UNBOUNDED PRECEDING onların fiziksel sırasını ayırt edemez. Bazı duplicateler NULL olup diğerleri değer içerdiğinde, pencere fonksiyonu uygulama planına bağlı olarak eş grup içinden rastgele seçebilir. ROWS çerçevesi, fiziksel satır sırasını işleyerek belirleyici sonuçları garanti eder ve belirli bir yerleştirmenin sırasının hangi değerin ileri taşındığını belirlemesini sağlar. Bu ayrım, mikro saniyelerin önem taşıdığı yüksek frekanslı ticaret verileri için kritik öneme sahiptir.