Tarihçe: Zamansal veri ambarlama alanında, Son Gözlem İleri Taşıma (LOCF) tekniği kaybolan değerlerin doldurulmasında baskındır; önceki geçerli kayıtları kullanarak boşlukları doldurur. Ancak, belirli analitik alanlar (örneğin, gün sonu uzlaştırmalarının gün içi finansal işlemlere uygulanması veya laboratuvar onaylarının önceki geçici tanılara geri yayılması) ters Sonraki Gözlem Geri Taşıma (NOCB) yaklaşımını gerektirir. Tarihsel olarak, NOCB, korelasyonlu alt sorgular veya prosedürel numaralandırıcılar aracılığıyla uygulanmıştır ve her iki yöntem de O(n²) karmaşıklığı sergilemekte ve modern set tabanlı optimize edicileri kullanamamaktadır.
Sorun: Tamamen sıralı bir dizide (event_time gibi), her NULL değeri, dizide sonra gerçekleşen en yakın geçerli NULL değer ile değiştirilmelidir. Geçerli bir kayıttan önceki ardışık NULL değerler, sonraki aynı değerle doldurulmalıdır. LEAD() gibi standart işlevler yalnızca bir sonraki satıra erişir ve birden fazla ardışık NULL değerle karşılaştığında başarısız olur. Kendi kendine birleştirmeler ve yeniden işlem CTE'leri performans kısıtlamaları nedeniyle yasaktır.
Çözüm: Çözüm, COUNT(expression)'ın NULL'u göz ardı eden semantiğini kullanır. Mevcut satırdan bölümlerin sonuna kadar geçerli NULL değerlerin sayılmasını sağlayarak (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), iki geçerli NULL değer arasında bulunan tüm satırlar için aynı olacak kararlı bir "kova kimliği" üretilir. Her kovada, MAX(val)—aynı zamanda NULL'ları da göz ardı eder—kova içindeki tüm satırlara yayılacak olan değerleri alır.
WITH bucketed AS ( SELECT record_id, event_time, status_code, COUNT(status_code) OVER ( ORDER BY event_time, record_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS bucket_id FROM audit_log ) SELECT record_id, event_time, COALESCE( MAX(status_code) OVER ( PARTITION BY bucket_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ), 'UNKNOWN' ) AS confirmed_status FROM bucketed;
Bağlam ve Sorun Tanımı: Yüksek frekanslı bir ticaret firması, mikro saniye seviyesinde hisse senedi işlemlerini yakalayan bir execution tablosu tutar. Borsa raporlama protokolleri nedeniyle, herhangi bir dakika için nihai "birleştirilmiş fiyat"—teminat kasası tarafından doğrulanan—dakikanın bitiminden 30 saniye sonra gelir ve yalnızca sınırda damgalanır (örn. 14:30:00.000). Düzenleyici TWAP (Zaman Ağırlıklı Ortalama Fiyat) hesaplamaları için, o dakikadaki her milisaniye, nihai birleştirilmiş fiyatı yansıtmalıdır ve bu, önceki 14:29:00.000 - 14:29:59.999 kayıtlarına geri doldurulmasını gerektirir. Günlük veri hacmi 50 milyon satırı aşmakta ve toplama penceresi 10 dakikadır.
Çözüm 1: Korele Ölçeklü Alt Sorgu. Bu yaklaşım, her satır için gelecekteki consolidated_price IS NOT NULL olan satırların MIN(event_time)'ini bulmak için bir ölçekli alt sorgu kullanır ve ardından o fiyatı geri almak için birlikte katılır.
Artılar: Prosedürel geçmişi olan geliştiriciler için kavramsal olarak anlaşılır.
Eksiler: O(n²) karşılaştırmalarının çalıştırılmasını sağlar. Üretim verilerinde, sorgu süresi 45 dakikayı aşmış ve toplama penceresini ihlal etmiştir. Birden fazla ardışık NULL işlemi ek bir mantık gerektirdiğinden karmaşıklığı ve hata oranlarını artırmaktadır.
Çözüm 2: Yeniden İşlem CTE Geçişi. Yeniden işlem CTE'si, her satırı geri doğru yineleyerek, bir non-NULL değer ile karşılaşana kadar NULL olmayan fiyatı geri taşır.
Artılar: Herhangi bir ANSI SQL uyumlu veritabanında çalışacağından emin olunmaktadır.
Eksiler: Yeniden işlem CTE’leri birkaç motor (örn. PostgreSQL) üzerinde satırları ardışık olarak işler. Bu nedenle, tek iş parçacıklı yürütme ve derin bölümlerde yığın taşması riski taşır. Performans ölçümlerinde, 20 dakikalık bir çalışma süresi gözlemlenmiş ve yüksek bellek yüklemesi nedeniyle üretim SLA'ları için uygun olmamıştır.
Çözüm 3: Pencere Fonksiyonu Kovalaşması (Seçilen). COUNT ve MAX desenini uygular. Geriye dönük COUNT, aynı gelecekteki değeri gerektiren tüm satırlar için aynı buckete oluşturur, bu sırada MAX bu değeri kova içinde yayar.
Artılar: Tamamen set tabanlı, paralelleştirilebilir ve sıralama işlemi nedeniyle O(n log n) zamanında çalışır. Hacimle doğrusal olarak ölçeklenir ve PostgreSQL, SQL Server, Oracle ve DB2'ye taşınabilir standart ANSI SQL kullanır.
Eksiler: Veriler üzerinde iki geçiş gerektirir (CTE ve dış sorgu), ancak modern optimize ediciler genellikle bunları birleştirir. Tam bir sıralama gerektirir; tekrarlanan zaman damgaları belirleyicilere ihtiyaç duyar.
Sonuç: Boru hattı çalışma süresi, 50 milyon satırlık veri kümesinde 45 dakikadan 8 saniyeye düştü. Firma, kırılgan bir Python geri doldurma betiğini ortadan kaldırmayı başardı ve altyapı karmaşıklığını azalttı ve düzenleyici raporların uyum penceresinde üretilmesini sağladı.
Gruplama anahtarı oluşturulurken neden COUNT(column) kullanılmalıdır, COUNT(*) veya ROW_NUMBER() yerine?
Birçok aday, verileri segmentleyeceğini düşünerek COUNT(*) veya ROW_NUMBER() kullanır. COUNT(*), NULL'lar dahil her satırı sayar ve geriye taşınan çerçevede her satır için benzersiz, monotonik bir değeri ortaya çıkarır; bu durum kararlı grupların oluşmasını engeller. ROW_NUMBER() her satıra benzersiz bir tanımlayıcı atar ve benzer şekilde gruplamayı bozar. Yalnızca COUNT(column), yalnızca geçerli NULL değerlerle karşılaştığında artış gösterir, bu da tüm önceki NULL'lar için aynı "kova ID" atar; bu ayrım, prosedürel mantık olmadan bir "ileri bakma" simüle etmek için agregat pencere işlevlerinin NULL'u göz ardı eden semantiğinden yarar.
Partisyon, sona eren ardışık NULL değerleri varsa sorgu nasıl davranır ve gelecekteki gözlem olmadığında belirleyici bir şekilde ele almak için hangi değişiklik gereklidir?
Eğer sıralı partisyonun son satırları NULL ise, COUNT(status_code) bu satırlar için sıfır olarak değerlendirilir. Sonuç olarak MAX(status_code) NULL döndürür; bu, mantıksal olarak doğrudur—geri taşınacak bir gelecek gözlem yoktur. Adaylar genellikle bu durumu alt akış iş mantığında ele almayı unutur. Varsayılan bir değer sağlamak (örneğin, statik bir yer tutucu veya dış bir arama değerinden bir değer) için, sonucu COALESCE ile sarmak gerekir. Ayrıca, veri kalitesi izleme için "doldurulmuş NULL" ve "doldurulamaz NULL" arasındaki farkı belirlemek için orijinal ve doldurulmuş değerleri karşılaştırmalısınız: CASE WHEN status_code IS NULL AND bucket_id = 0 THEN 'UNCONFIRMED' END.
ORDER BY ifadesinde tekrarlanan değerler varsa hangi deterministik sorun ortaya çıkar ve ROWS'dan RANGE'ye geçmek bu sorunu nasıl artırır?
Sıralama anahtarlarında tekrarlanan değerler (bağlantılar) olduğunda, pencereler çerçeve tanımı belirsiz hale gelir. ROWS (fiziksel ofsetler) kullanmak, grupları fiziksel tablo sırasına göre atar; bu, ikincil bir benzersiz sıralama anahtarı sağlanmadıkça keyfi bir durumdur. RANGE'ye (mantıksal değer aralıkları) geçmek, aynı sıralama değerine sahip tüm satırların eşit muamele görmesine neden olur; bu da onları aynı çerçeveyi paylaşmaya zorlar. Bu çözüme göre, eğer birden fazla satır aynı event_time'ı paylaşıyorsa, RANGE yanlış bir şekilde NULL satırlarını aynı zaman damgasından non-NULL satırlarla gruplandırabilir veya grupları öngörülemez bir şekilde bölebilir. Adaylar, tüm ANSI SQL uygulamalarında belirleyici kova ataması sağlamak için ORDER BY ifadesine benzersiz bir anahtar (örneğin record_id) eklemelidir: ORDER BY event_time, record_id.