İleri desenler: gaps & islands, ardışık seriler

Bu sayfa dersin okuma sürümüdür. Metin bir kutuyu çalıştırmanı ya da adıma geçmeni söylüyorsa dersi uygulamada aç.

Gaps and islands (boşluklar ve adalar)

“Müşterinin aralıksız sipariş verdiği ay serileri”, “sunucunun kesintisiz çalıştığı dönemler” gibi sorular bu kalıpla çözülür. Fikir: Ardışık değerlerden sıra numarası çıkarıldığında, aynı adadaki satırlar aynı sabit değeri verir.

WITH aylar AS (
  SELECT DISTINCT customer_id,
         CAST(strftime('%Y', order_date) AS INTEGER) * 12 + CAST(strftime('%m', order_date) AS INTEGER) AS ay_no
  FROM orders
  WHERE customer_id = 24
),
grup AS (
  SELECT customer_id, ay_no,
         ay_no - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ay_no) AS ada
  FROM aylar
)
SELECT customer_id, MIN(ay_no) AS bas, MAX(ay_no) AS son, COUNT(*) AS ardisik_ay
FROM grup
GROUP BY customer_id, ada
ORDER BY bas;

Aylar 1, 2, 3, 5, 6 ise sıra numaraları 1, 2, 3, 4, 5; farklar 0, 0, 0, 1, 1 olur: iki ada.

Diğer ileri kalıplar

Oturumlaştırma (sessionization): Olaylar arasında 30 dakikadan fazla boşluk varsa yeni oturum. LAG ile fark al, eşiği aşanlara 1 ver, kümülatif topla.
Aralık birleştirme: Çakışan tarih aralıklarını tek aralığa indirmek (gaps & islands'ın bir türü).
Kümülatif ayrık sayım: “Bugüne kadar kaç farklı müşteri?” için ilk görülme tarihine göre say.