Aktiflik boşlukları sabit bir intervali aşan süreler temelinde sürekli zaman damgası dizilerini oturumlara nasıl bölersiniz? Sadece ANSI SQL pencere işlevlerini kullanarak, yine de yinelemeli CTE'ler veya prosedürel mantığa başvurmadan?

Sorunun Cevabı

Olayları aktiflik boşluklarına dayalı olarak oturumlar haline dönüştürmek için ANSI SQL kullanarak, öncelikle zamansal boşlukları mantıksal grup tanımlayıcılarına dönüştürmeniz gerekir. Verilerinizi kullanıcı tanımlayıcısına göre bölümlendirin ve kronolojik olarak sıralayın, ardından her satırın bu bölümdeki hemen önceki zaman damgasını almak için LAG işlevini kullanın. Mevcut ve önceki zaman damgaları arasındaki farkı hesaplayın; bu aralık belirlediğiniz eşiği aşarsa, yeni bir oturum sınırını gösteren bir ikili bayrak oluşturun.

SELECT 
    user_id,
    event_timestamp,
    SUM(is_new_session) OVER (
        PARTITION BY user_id 
        ORDER BY event_timestamp 
        ROWS UNBOUNDED PRECEDING
    ) AS session_id
FROM (
    SELECT 
        user_id,
        event_timestamp,
        CASE 
            WHEN event_timestamp - LAG(event_timestamp) OVER (
                PARTITION BY user_id ORDER BY event_timestamp
            ) > INTERVAL '30' MINUTE 
            THEN 1 ELSE 0 
        END AS is_new_session
    FROM user_events
) t;

Oturum tanımlayıcısını oluşturmak için bu ikili bayrağın kümülatif SUM'unu uygulayın; bu, sınır işaretlerini sürekli tam sayı aralıklarına dönüştürerek farklı oturumları temsil eder. Bu teknik, her kullanıcının olay akışını bağımsız zamansal adalar olarak ele alır ve prosedürel yinelemeden kaçınarak küme tabanlı toplamayı sağlar. Sonuçta elde edilen sorgu, PostgreSQL, Oracle ve diğer standartlara uygun motorlar üzerinde verimli bir şekilde çalışır.

Hayattan bir durum

Mobil analiz platformumuz, milyonlarca kullanıcıdan yüksek hızda olay akışları aldı ve aktiflik eşiklerine dayalı olarak etkileşim oturumlarını tanımlamak için kritik bir gereksinim ortaya çıktı. Ürün analitiği ekibi, sürekli gezinme etkinliği ile yeni ziyaret başlangıçlarını ayırt etmek zorundaydı; özellikle, aynı kullanıcıdan gelen ardışık eylemler arasında 30 dakikayı aşan herhangi bir boşluk, oturum sonlandırıcı olarak tanımlandı. Bu zorluk, çok sayıda tarihi kaydı işleyebilen bir çözüm gerektiriyordu; pahalı prosedürel yinelemelere veya platforma özgü özelliklere başvurulmaksızın.

Üç potansiyel uygulama stratejisini değerlendirdik. İlk öneri, her olayı zaman açısından komşuları ile karşılaştıran bir self-join deseni kullanıyordu. İşlevsel olarak doğru olsa da, bu yaklaşım, sorgu yürütme sürelerinin veri setimizde 45 dakikayı aşmasına ve yoğun analitik iş yükleri sırasında aşırı bellek kaynakları tüketmesine neden olan kare O(n²) zaman karmaşıklığı gösteriyordu.

İkinci aday çözümü, olay dizisini yinelemeli olarak gezmek için bir recursive CTE kullandı ve eşik aşılana kadar zaman farklarını biriktirdi. Akademik olarak ilginç olmasına rağmen, bu yöntem, daha uzun kullanıcı oturumlarında yığın derinliği sınırlamalarını tetikledi ve temelde, SQL'nin küme tabanlı felsefesine aykırı olarak satır bazında çalıştı; bu da büyük ölçekli verilerle kabul edilemez bir performans düşüşüne neden oldu.

Sonuçta, LAG ve kümülatif SUM kullanan ANSI SQL pencere işlevi yaklaşımını uyguladık. Bu teknik, sıralanmış indeks taramaları kullanarak ve join yükünü ortadan kaldırarak, 50 milyon satırlık veri setinin tamamını 8 saniyeden kısa bir sürede işledi. Çözüm, sıçrama oranları ve oturum süresi için doğru metrik hesaplamaları sağlamak amacıyla kesin oturum tanımlayıcıları sağladı ve PostgreSQL analitik düğümleri ve MySQL işlem mağazalarından oluşan heterojen altyapımızda tam veri tabanı taşınabilirliğini sağladı.

Adayların genellikle unuttuğu noktalar

LAG işlevinde varsayılan değer parametresinin atlanması, her kullanıcı oturumunun ilk olayını neden yanlış sınıflandırmasına neden olur?

LAG, bir partisyonun ilk satırıyla karşılaştığında, belirli bir kullanıcının sıralı dizisinde hiçbir önceki satır mevcut olmadığından NULL döner. Adaylar, genellikle isteğe bağlı varsayılan değeri belirtmeyi unuturlar (örneğin, mevcut satırın zaman damgası), bu da sonraki boşluk hesaplamalarının NULL değil sıfır vermesine neden olarak yeni oturumları tanımlayan koşullu mantığı bozar. Doğru işleme, boşlukları doğru şekilde sıfır veya negatif değerler olarak hesaplayacak şekilde uygun kırılma gerektirir; bu, yanlış oturum başlangıçlarını tetiklemez.

Tekrar eden zaman damgaları mevcut olduğunda, pencere çerçevesindeki ROWS ve RANGE arasındaki seçim, oturum ID'sinin atanmasını nasıl etkiler?

RANGE ifadesi, aynı sıralama değerlerine sahip tüm satırları eşit kabul eder; bu, oturum bayrağı üzerinde kümülatif SUM'in, tüm eş zamanlı olaylara aynı artışı uygulayacağı anlamına gelir; böylece, sıra numaralarını atlar ve kesintisiz oturum kimlikleri oluşturur. ROWS ise, zaman damgalarının çakışmasına bakılmaksızın fiziksel satır sırasını işler; bu, her olayın zaman damgaları eşleşse bile, farklı oturum tanımlayıcıları almasını sağlar. Adaylar, genellikle bu ayrımı atlar ve eş zamanlı eylemlerin tek mantıksal oturumlara birleştirilmesine veya aşağı akış toplamayı bozan belirsiz gruplama anahtarları almasına neden olan ince hatalarla sonuçlanır.

Kümülatif SUM pencere işlevinin doğru oturum tanımlayıcıları oluşturmak için OVER spesifikasyonunun içinde ORDER BY ifadesini içermesi neden gereklidir?

Açık bir sıralama olmadan, SUM, tüm partisyon boyunca sabit bir toplam haline gelir ve bir kullanıcının geçmişindeki her satıra aynı oturum sayısını atar. Adaylar, pencere işlevlerinin, birikim sırasını belirlemek için ORDER BY gerektirdiğini sıkça unutur; bunu atlamak, bir kullanıcının tüm yaşam boyu etkinliğini kapsayan tek bir oturum ID'si üretir. Doğru sentaks, sadece tespit edilen sınırlarda artış gösteren çalışır toplam oluşturmak için SUM(flag) OVER (PARTITION BY user_id ORDER BY timestamp ROWS UNBOUNDED PRECEDING) gerektirir.