Bu soru, SQL standartlarının SQL-92'den SQL:2003'e evrimi ile ortaya çıktı; bu dönemde pencere işlevleri standartlara resmi olarak dahil edildi. Bu ilerlemeden önce, geliştiriciler sıralı problem çözme için prosedürel işaretçiler ya da hesaplama açısından pahalı öz-kendi birleşimleri kullanıyordu. Boşluklar ve adalar modeli, prosedürel algoritmalardan deklaratif, küme tabanlı mantığa geçişin bir temsilidir; bu, sonuç kümesini tanımlarken işlem adımlarını değil.
Zaman damgaları, kimlikler veya tarihler gibi sıralı değerler içeren tablolarla çalışırken, ardışık değerleri kesintisiz bloklar (adaletler) halinde gruplandırmanız ve bunları boşluklardan ayırmanız gerekir. Temel zorluk, ANSI SQL tablolarının sıralı matematiksel kümeleri temsil etmesi iken, dizileri tespit etmek açık sıralama gerekliliğidir. Geleneksel GROUP BY ifadeleri benzer değerleri toplar, ancak ardışıklıkları tanımlamak için gerekli olan ilişkiyi bozar.
Tüm veri kümesi üzerindeki ROW_NUMBER() ile grup anahtarı tarafından bölümlenmiş ROW_NUMBER() arasındaki aritmetik farkı kullanın, sabit bir ada tanımlayıcı oluşturun. Bu teknik, aynı kesintisiz dizideki tüm satırlar için aynı hesaplanmış değerleri oluşturur ve standart toplama işlemleri adaları yeniden yapılandırır.
WITH numbered AS ( SELECT event_date, ROW_NUMBER() OVER (ORDER BY event_date) AS rn_global, event_date - ROW_NUMBER() OVER (ORDER BY event_date) AS island_grp FROM events ) SELECT MIN(event_date) AS island_start, MAX(event_date) AS island_end, COUNT(*) AS consecutive_days FROM numbered GROUP BY island_grp;
Bir perakende analitiği ekibi, PostgreSQL'de depolanan tıklama verilerinden müşteri alışveriş oturumlarını yeniden yapılandırmak zorundaydı. Sistem, user_id ve event_time içeren milyonlarca olayı kaydetti, ancak önceden hesaplanmış oturum tanımlayıcıları yoktu. İş gereksinimleri, bir oturumu 30 dakikadan fazla bir boşluk olmadığı bir olay dizisi olarak tanımladı.
İlk yaklaşım, her olayın hemen önceki olayını bulmak için korrele edilmiş bir alt sorgu ile bir self-join kullanmayı düşündü. Bu yöntem, O(n²) satır karşılaştırmaları gerektirdi ve günlük beş milyondan fazla satır içeren toplu veriler işlenirken sorgu zaman aşımına neden oldu, ancak modern pencere işlevlerinden yoksun eski SQL-92 sistemleri ile uyumlu kalıyordu.
Ekip daha sonra, oturum durumunu prosedürel değişkenlerde korurken satır bazında olayları yinelemek için pl/pgSQL işaretçilerini değerlendirdi. Bu yaklaşım uygulama geliştiricilerine tanıdık bir mantık sunsa da, küme tabanlı işlem ilkelerini terk etti ve günlük toplu verileri tamamlamak için dört saatin üzerinde bir süre gerektirdi, bu da kabul edilemez ETL gecikmesi ve önemli tablo kilitleme sorunları yarattı.
Seçilen çözüm yalnızca ANSI SQL pencere işlevlerini kullandı. Kullanıcı başına önceki zaman damgasını yakalamak için LAG() uygulayarak ve zaman farklarını hesaplayarak, 30 dakikayı aşan boşlukların olduğu oturum sınırlarını tanımladı. Koşullu bir artan toplam, benzersiz oturum tanımlayıcıları üretti ve küme tabanlı toplamayı mümkün kıldı. Bu yöntem, tüm veri setini sekiz dakikada işledi, hacimle doğrusal olarak ölçeklendi ve Oracle, SQL Server ve PostgreSQL arasında taşınabilir kaldı, tedarikçi özel sözdiziminde değişiklikler gerektirmedi.
Neden zaman damgalarını saat başına kesip bu değere göre gruplandırarak oturumları bulamıyorum?
Zaman damgalarını DATE_TRUNC veya benzeri işlevler kullanarak kesmek, yapay sınırlamaları saat başlarında zorlar. 10:55 ve 11:05'te meydana gelen iki olay, yalnızca 10 dakika uzaklıkta olmalarına rağmen farklı gruplara ayrılacak, 10:01 ve 10:59'daki olaylar ise 58 dakikalık bir boşluk olmasına rağmen bir araya gelecektir. Gerçek oturum tespiti, her olayın hemen öncesinden intervali hesaplamayı gerektirir, takvim sınırlamalarına bağlı olmamak gerekir.
LAG veya LEAD kullanırken sıralama sütunundaki NULL değerleri ada tespitini nasıl etkiler?
LAG ve LEAD, her bölümdeki ilk ve son satırlar için sırasıyla NULL döner. Boşlukları hesaplamak için lagged zaman damgasını mevcut zaman damgasından çıkardığınızda, NULL ile yapılan aritmetik NULL sonuçlar üretir ve bu da tüm adaların toplamdan kaybolmasına neden olabilir. Ada parçalanmasını önlemek için, LAG'deki isteğe bağlı default parametresini (örneğin, LAG(event_time, 1, event_time) OVER (...)) kullanmalısınız veya açıkça NULL ile COALESCE ile başa çıkmalısınız.
Birden fazla kategori arasında aynı anda ada tespiti yaparken neler değişir, örneğin kullanıcı veya cihaz başına?
Adaylar genellikle pencere işlevlerinde PARTITION BY ifadesini dahil etmeyi atlarlar ve ROW_NUMBER'ı tüm tablo üzerinde küresel olarak hesaplarlar; kategori başına değil. user_id veya eşdeğer gruplama sütunları ile bölümlendirilmeden, farklı kullanıcıların adaları, dizilerinin zamanla örtüştüğü durumlarda yanlış bir şekilde birleşir. Ada hesaplamasında yer alan her pencere işlevi, her ayrık entite için aritmetiğin sıfırlanmasını sağlamak üzere PARTITION BY user_id içermelidir, böylece her bölümde bağımsız ada tespitini korur.