Boşluklar ve adalar: ardışık serileri bulmak (gaps and islands)

Problem

“Müşteri kaç ay üst üste alışveriş yaptı?”, “Sensör hangi aralıklarda veri göndermedi?” gibi sorular boşluklar ve adalar (gaps and islands) problemidir. Ada: kesintisiz bir seri; boşluk: iki seri arasındaki kesinti.

ROW_NUMBER farkı yöntemi

Ardışık değerlerden sıra numarasını çıkarırsan, aynı serideki tüm satırlar aynı sabiti verir:

WITH m AS (               -- 1 numaralı müşterinin sipariş verdiği aylar (ay numarası)
  SELECT DISTINCT CAST(strftime('%Y', order_date) AS INTEGER) * 12
                + CAST(strftime('%m', order_date) AS INTEGER) AS ay_no,
         strftime('%Y-%m', order_date) AS ay
  FROM orders WHERE customer_id = 1
), g AS (
  SELECT ay, ay_no - ROW_NUMBER() OVER (ORDER BY ay_no) AS grp FROM m
)
SELECT MIN(ay) AS baslangic, MAX(ay) AS bitis, COUNT(*) AS ay_sayisi
FROM g
GROUP BY grp
ORDER BY baslangic;

Boşlukları bulmak: LEAD

WITH m AS (
  SELECT DISTINCT order_date AS gun FROM orders
  WHERE order_date >= '2024-12-01' AND order_date < '2025-01-01'
)
SELECT gun AS son_dolu, LEAD(gun) OVER (ORDER BY gun) AS sonraki_dolu
FROM m;

Sonraki dolu gün ile aradaki fark 1'den büyükse arada boşluk vardır.

Derinleş: takvim günü mü, kayıt mı?

“Art arda gün” sorularında önce veriyi gün düzeyine indir (DISTINCT), sonra sıra numarası farkını uygula. Aynı gün birden çok kayıt varsa ROW_NUMBER iki kez artar ve seri kırılmış görünür. Saatli zaman damgalarında date(...) ile gün çıkar; gün farkı hesaplarken de saatleri at: julianday(date(b)) - julianday(date(a)).