Kapsama dönemlerini etkili sürekli bloklara konsolide etme bağlamında, bu aralıkları ayrık, çakışmayan aralıklara nasıl birleştirirsiniz? Bunu yalnızca ANSI SQL pencere işlevlerini kullanarak ve prosedürel döngüler olmadan gerçekleştirin.

Soruya yanıt

Soru tarihi. Çakışan geçici aralıkları konsolide etme gereksinimi, Allen’ın aralık cebirinden (1983) ve erken ilişkisel veritabanı araştırmalarından kaynaklanmaktadır. Sigorta sistemleri, otel rezervasyon platformları ve kaynak zamanlama uygulamaları, birden fazla kapsama döneminin, rezervasyonun veya bakım penceresinin çakışması gerektiğinde sıklıkla bu zorlukla karşılaşmaktadır ve bunların ayrışmış, sürekli bloklar halinde normalize edilmesi gerekmektedir, bu da doğru kullanılabilirlik raporlaması veya faturalama için gereklidir. Basit toplama işleminden farklı olarak, bu işlem düzen ve sürekliliği anlamayı gerektirir ve gelişmiş ANSI SQL pencere işlevleri ustalığının standart bir testidir.

Problem. start_date ve end_date sütunları ile tanımlanan bir tarih aralıkları tablosu verildiğinde, tüm çakışan veya bitişik aralıkları minimum sayıda çakışmayan aralıklara birleştirmek amacı vardır. Prosedürel bir yaklaşım, her satırı mevcut birleştirilmiş aralıkla karşılaştırarak sürekli bir tampon tutar, ancak bu SQL’nin set tabanlı paradigmasını ihlal eder. Temel zorluk, kendi kendine birleştirmeler veya özyinelemeli CTE olmadan süreklilik “adalarının” tanımlanmasındadır, özellikle de transitif çakışmalar (A aralığı B ile, B aralığı C ile çakışır, ancak A ve C doğrudan temas etmez) mevcut olduğunda.

Çözüm. Her yeni adanın başlangıcını tespit etmek için ANSI SQL pencere işlevlerini kullanın; mevcut satırın start_date değerini aynı bölmede bulunan tüm önceki satırların maksimum end_date değeri ile karşılaştırın. start_date önceki maksimum bitiş tarihini aşıyorsa, yeni bir ada başlar; aksi takdirde mevcut satır mevcut adayı uzatır. Bu “kırılma” bayraklarının sürekli toplamını island_id olarak atayın, ardından toplulaştırılmış min(start_date) ve max(end_date) hesaplamak için bu tanımlayıcıya göre gruplandırın. Bu yaklaşım, tek geçişli sıralama ve toplama ile O(n log n) karmaşıklığını elde eder.

Gerçek yaşam durumu

Problem tanımı. Çok uluslu bir sağlık hizmeti sağlayıcısı, hastaların birden fazla çakışan sigorta poliçesi tutmasına neden olan bir talepler işleme veritabanı tutmaktaydı—birincil kapsama 1 Ocak - 31 Mart, ikincil 15 Şubat - 15 Nisan ve üçüncül ise 1 Mayıs itibarıyla başlıyordu. Mevcut sistem, bunları birer ayrı aktif dönem olarak tedavi ettiğinden, yinelenen talep reddi üretiyordu; oysaki tek sürekli kapsama bloğu olarak 1 Ocak’tan 15 Nisan’a kadar ve ardından Mayıs uzantısını içeriyordu. İş, “yinelenen ödeme yok” kurallarını uygulayacak konsolide bir görünüm sağlamak ve orijinal poliçe kayıtlarının denetim izlerini korumak zorundaydı.

Çözüm 1: Prosedürel imleç tabanlı yineleme. İlk öneri, start_date değerine göre sıralanmış bir imleç kullanan bir saklama prosedürü içindi; @current_start ve @current_end değişkenlerini koruyarak her satır için start_date ≤ @current_end ise kod @current_end değerini max(@current_end, end_date) olarak günceller; aksi takdirde mevcut aralığı iletir ve değişkenleri sıfırlar. Artılar: İleri düzey yazılımcılar için kavramsal olarak anlaşılır; adım adım hata ayıklama kolaydır. Eksiler: PL/pgSQL veya T-SQL prosedürel uzantıları gerektirir; satır bazında O(n) bellek kullanır ancak zayıf I/O performansına sahiptir; herhangi bir uyumlu motor üzerinde çalışacak saf bildirisel ANSI SQL gereksinimini ihlal eder.

Çözüm 2: Öz-birleştirme ile transitif kapama tespiti. Diğer bir yaklaşım, hemen çakışmaları bulmak için t1 JOIN t2 ON t1.start_date <= t2.end_date AND t1.end_date >= t2.start_date şeklinde bir öz-birleştirme gerçekleştirmiş, ardından çakışma grafiğini yürümek ve bağlı bileşenleri tanımlamak için özyinelemeli bir CTE kullanmıştır. Artılar: Pencere işlevleri olmadan karmaşık transitif ilişkileri teorik olarak doğru işler. Eksiler: Özyineleme öncesinde O(n²) ara satır üretir; büyük veri kümesi için hesaplama açısından patlayıcıdır; özyinelemeli CTE’lere bağımlıdır. Bu CTE’ler ANSI SQL standart olmasına rağmen, bu belirli doğrusal sıralama problemi için pencere işlevlerinden daha az performanslıdır.

Çözüm 3: Pencere işlevi boşluk tespiti (seçilen). Ekip saf bir pencere işlevi çözümi uyguladı: is_new_island = CASE WHEN start_date > MAX(end_date) OVER (PARTITION BY patient_id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN 1 ELSE 0 END bayrağını işaretleyerek, ardından island_id = SUM(is_new_island) OVER (PARTITION BY patient_id ORDER BY start_date) hesaplandı. Nihai toplama, patient_id, island_id ile gruplandı. Artılar: ANSI SQL standart sözdizimini kullanan tek geçişli yürütme; sıralama ile sınırlı O(n log n) karmaşıklığı; transitif çakışmaları koşullu akışla yönlendirilen maksimum üzerinden dolaylı olarak işler. Eksiler: NULL bitiş tarihlerinin (belirsiz kapsama) dikkatli işlenmesi ve bitişik aralık anlamlarının (temas eden aralıkların birleştirilip birleştirilmeyeceği) gerektirir.

Sonuç. Dağıtım, standart donanımda 2.3 milyon poliçe kaydını 890.000 sürekli kapsama bloğuna 12 saniyeden kısa bir sürede konsolide etti ve 45 dakikalık imleç tabanlı toplu işi değiştirdi. Sorgu bir görünüm olarak kapsüllenerek, gerçek zamanlı uygunluk kontrollerine imkan sağlandı ve sonraki çeyrekte yinelenen talep reddinin %99’unu ortadan kaldırdı.

WITH coverage_flags AS (
    SELECT patient_id, start_date, end_date,
           CASE 
             WHEN start_date > MAX(end_date) OVER (
                 PARTITION BY patient_id 
                 ORDER BY start_date 
                 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
             ) THEN 1 
             ELSE 0 
           END AS is_new_island
    FROM insurance_periods
),
islands AS (
    SELECT patient_id, start_date, end_date,
           SUM(is_new_island) OVER (
               PARTITION BY patient_id 
               ORDER BY start_date
           ) AS island_id
    FROM coverage_flags
)
SELECT patient_id, 
       MIN(start_date) AS consolidated_start,
       MAX(end_date) AS consolidated_end
FROM islands
GROUP BY patient_id, island_id;

Adayların genellikle gözden kaçırdığı şey

Sınır noktalarında dokunan bitişik aralıkları, örneğin [1 Ocak-10] ve [11 Ocak-20] nasıl ele alırsınız ve hangi predikat değişikliği gereklidir?

Adaylar genellikle bitişik aralıkları ayrı adalar olarak ele alan katı bir eşitsizlik start_date > previous_end_date kullanır. Sağlık kapsaması veya sürekli zamanlama için, bitişik dönemler genellikle kesintisiz hizmeti temsil eder ve birleştirilmelidir. Predikat, aralık türüne uyacak şekilde ayarlanmalıdır: kapalı aralıklar (dahil başlangıç ve bitiş) için start_date > previous_end_date + INTERVAL '1' DAY kullanın. Yarım açık aralıklar [start, end) (bitiş ayrı) için start_date > previous_end_date durumu, bitişiklerin doğal olarak birleşmesini sağlar çünkü 11 Ocak, 11 Ocak'a eşittir. ANSI SQL doğrudan aralık aritmetiğini destekler, bu nedenle çözüm CASE WHEN start_date > MAX(end_date) OVER (...) + INTERVAL '1' DAY THEN 1 ELSE 0 END gerektirir.

Giriş, NULL değerlerin bulunduğu durumlarda MAX(end_date) pencere işlevi neden yanlış ada sınırları üretir?

ANSI SQL topluluk pencere işlevleri gibi MAX() NULL değerleri çerçevedeki göz ardı eder. Eğer bir poliçenin bitiş tarihi yoksa (NULL “şu anki” anlamında), o zaman önceki satırlardaki MAX(end_date), sonraki aralıkların yeni bir ada başlatabilmesi gerektiğini düşündüren en son geçerli olmayan NULL tarihi döndürür. Adaylar, NULL'ların özel muamele gerektirdiğini tanımalı: ya önceden bir CTE'de filtrelenmeleri gerekir ya da belirsiz kapsamanın sonsuzluğa uzandığını düşünmek için COALESCE(end_date, DATE '9999-12-31') kullanmayı düşünebilirler. Alternatif olarak, NULL'u zor bir kırılma olarak ele almak için CASE WHEN end_date IS NULL THEN 0 ELSE 1 END mantığını kullanarak bir sonraki satırın yeni bir ada başlatmasını sağlamak mümkündür.

Bu mantığı, her bir hasta kimliği ve sigorta türü kombinasyonu için aralıkları ayrı ayrı konsolide ederek çok boyutlu yerleştirmeye nasıl genişletirsiniz?

Birçok aday, alt sorgular veya öz-birleştirmelerle manuel olarak parçalanmayı dener. Doğru yaklaşım, ANSI SQL pencere işlevlerinde PARTITION BY ifadesini kullanmaktır. MAX(end_date) ve SUM(is_new_island hesaplamalarında çerçeve tanımını PARTITION BY patient_id, insurance_type olarak değiştirin. Bu, koşullandırma maksimumu ve ada kimliği sayacının her belirgin grup için sıfırlanmasını sağlar, böylece parçalar arasında O(n log n) performansını koruyabilirsiniz. Doğru parçalamayı sağlamamak, bir hastanın zaman çizelgesinde bir boşluğun, başka bir hasta için yeni bir ada başlatmasını yanlış bir şekilde tetiklemesi anlamına gelir ve birleştirme mantığını bozar.