Bir öğrenme uygulaması yazıyor: “5 gün üst üste ders çalıştın!” Bir muhasebeci soruyor: “Hangi fatura numaraları eksik?” Bir yönetici bilmek istiyor: “Sunucu bu ay hangi dönemlerde çalışmadı?” Bu üç soru aynı klasik SQL görevidir: boşluklar ve adalar (gaps and islands). İş görüşmelerinde de sık sorulur. İki araca ihtiyacımız var: «Pencere fonksiyonları» dersindeki ROW_NUMBER, LAG ve LEAD ile «CTE: WITH ve özyinelemeli sorgular» dersindeki özyinelemeli CTE.
Boşluk ve adaTanım
Sıralı bir dizide ada, kesintisiz ilerleyen değerlerin en uzun parçasıdır (1, 2, 3); boşluk ise iki ada arasındaki eksik değerlerdir (4, 5). “Kesintisiz” bir adımla tanımlanır: tam sayılar için 1, tarihler için 1 gün, olaylar için ise örneğin 30 dakikadan az.
Boşluklar: ne eksik
En basit yol, her satırı sonrakiyle karşılaştırmaktır: LEAD(id) sonraki numarayı verir, fark 1'den büyükse arada bir boşluk vardır. İptal edilen 4, 5 ve 9 numaralı siparişleri silip kontrol edelim:
DELETE FROM orders WHERE id IN (4, 5, 9); -- cancelled orders
SELECT id + 1 AS gap_start, next_id - 1 AS gap_end
FROM (SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id FROM orders)
WHERE next_id - id > 1;Beklenen çıktı
gap_start | gap_end 4 | 5 9 | 9
Her eksik numara ayrı bir satır olsun isteniyorsa özyinelemeli bir CTE ile 1'den en büyük id'ye kadar bütün sayıları üretip var olanları çıkarırız (NOT EXISTS). Bu yöntem sondaki bir “kaybı” göremez: veritabanı 12'den sonra ne gelmesi gerektiğini bilmez.
DELETE FROM orders WHERE id IN (4, 5, 9);
WITH RECURSIVE ids(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM ids WHERE n < (SELECT MAX(id) FROM orders)
)
SELECT n AS missing_id
FROM ids
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE id = n);Beklenen çıktı
missing_id 4 5 9
Tarihlerde de aynı fikir geçerli: bir takvim CTE'si dönemin her gününü verir, LEFT JOIN girişleri bağlar ve NULL kalan günler kaçırılan günlerdir. Bu derste logins (student_id, login_date) tablosunu kullanıyoruz: 1 Aysel, 2 Murad, 3 Leyla. Aysel'in 1–10 Ekim arasında kaçırdığı günler:
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
(1, '2025-10-01'), (1, '2025-10-02'), (1, '2025-10-03'),
(1, '2025-10-05'), (1, '2025-10-06'), (1, '2025-10-09'),
(2, '2025-10-02'), (2, '2025-10-03'), (2, '2025-10-03'),
(2, '2025-10-04'), (2, '2025-10-05'), (2, '2025-10-08'),
(3, '2025-10-06'), (3, '2025-10-07'), (3, '2025-10-08'), (3, '2025-10-09');
WITH RECURSIVE calendar(day) AS (
SELECT '2025-10-01'
UNION ALL
SELECT date(day, '+1 day') FROM calendar WHERE day < '2025-10-10'
)
SELECT c.day AS missed_day
FROM calendar AS c
LEFT JOIN logins AS l ON l.login_date = c.day AND l.student_id = 1
WHERE l.student_id IS NULL;Beklenen çıktı
missed_day 2025-10-04 2025-10-07 2025-10-08 2025-10-10
WHERE içinde değil ON içinde; yoksa LEFT JOIN sıradan bir JOIN olurdu.Adalar: ROW_NUMBER farkı hilesi
Değerleri artan sırayla numaralayalım. Bir adanın içinde hem değer hem ROW_NUMBER her adımda 1 artar; bu yüzden farkları sabit kalır. Bir boşluktan sonra değer numaradan daha fazla sıçrar ve fark büyür. Demek ki fark, adanın “adıdır”:
Burada
- value
- dizideki değer (bir id, bir gün numarası)
- ROW_NUMBER()
- sıralı satırın numarası: 1, 2, 3, …
- grp
- ada anahtarı: aynı adada aynı değer
Sonra GROUP BY grp her adayı tek bir satıra indirir: MIN başlangıç, MAX son, COUNT uzunluktur.
| value | ROW_NUMBER | value − ROW_NUMBER | Ada |
|---|---|---|---|
| 1 | 1 | 0 | A |
| 2 | 2 | 0 | A |
| 3 | 3 | 0 | A |
| 6 | 4 | 2 | B |
| 7 | 5 | 2 | B |
| 9 | 6 | 3 | C |
Hileyi elle dene
Problem
Değerler: 3, 4, 5, 8, 9, 12. value − ROW_NUMBER ne olur ve kaç ada çıkar?
Çözümü gösterÇözümü gizle
Çözüm
2) Farklar: 3 − 1 = 2, 4 − 2 = 2, 5 − 3 = 2, 8 − 4 = 4, 9 − 5 = 4, 12 − 6 = 6.
3) Farklı değerler 2, 4, 6; yani 3 ada: 3–5 (3 değer), 8–9 (2), 12 (1).
Tarihlerde satır numarasını gün olarak çıkarırız: date(login_date, '-' || rn || ' days'). Ardışık günler için sonuç aynı “çapa” tarihtir. WINDOW w AS (ORDER BY login_date) pencereye bir kez ad verir; böylece birkaç fonksiyon onu OVER w olarak kullanabilir. Aysel'in girişleri:
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
(1, '2025-10-01'), (1, '2025-10-02'), (1, '2025-10-03'),
(1, '2025-10-05'), (1, '2025-10-06'), (1, '2025-10-09');
SELECT login_date,
ROW_NUMBER() OVER w AS rn,
date(login_date, '-' || ROW_NUMBER() OVER w || ' days') AS grp
FROM logins
WINDOW w AS (ORDER BY login_date)
ORDER BY login_date;Beklenen çıktı
login_date | rn | grp 2025-10-01 | 1 | 2025-09-30 2025-10-02 | 2 | 2025-09-30 2025-10-03 | 3 | 2025-09-30 2025-10-05 | 4 | 2025-10-01 2025-10-06 | 5 | 2025-10-01 2025-10-09 | 6 | 2025-10-03
Bütün öğrenciler için numaralama her öğrencide yeniden başlamalı (PARTITION BY student_id), tekrarlanan girişler de önce DISTINCT ile atılmalıdır. Sonra GROUP BY her seriyi bir tarih aralığına çevirir:
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
(1, '2025-10-01'), (1, '2025-10-02'), (1, '2025-10-03'),
(1, '2025-10-05'), (1, '2025-10-06'), (1, '2025-10-09'),
(2, '2025-10-02'), (2, '2025-10-03'), (2, '2025-10-03'),
(2, '2025-10-04'), (2, '2025-10-05'), (2, '2025-10-08'),
(3, '2025-10-06'), (3, '2025-10-07'), (3, '2025-10-08'), (3, '2025-10-09');
WITH days AS (SELECT DISTINCT student_id, login_date FROM logins),
grouped AS (
SELECT student_id, login_date,
date(login_date, '-' || ROW_NUMBER() OVER (
PARTITION BY student_id ORDER BY login_date) || ' days') AS grp
FROM days
)
SELECT student_id, MIN(login_date) AS from_day, MAX(login_date) AS to_day,
COUNT(*) AS days
FROM grouped
GROUP BY student_id, grp
ORDER BY student_id, from_day;Beklenen çıktı
student_id | from_day | to_day | days 1 | 2025-10-01 | 2025-10-03 | 3 1 | 2025-10-05 | 2025-10-06 | 2 1 | 2025-10-09 | 2025-10-09 | 1 2 | 2025-10-02 | 2025-10-05 | 4 2 | 2025-10-08 | 2025-10-08 | 1 3 | 2025-10-06 | 2025-10-09 | 4
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
(2, '2025-10-02'), (2, '2025-10-03'), (2, '2025-10-03'),
(2, '2025-10-04'), (2, '2025-10-05'), (2, '2025-10-08');
SELECT login_date,
date(login_date, '-' || ROW_NUMBER() OVER w || ' days') AS by_row_number,
date(login_date, '-' || DENSE_RANK() OVER w || ' days') AS by_dense_rank
FROM logins
WINDOW w AS (ORDER BY login_date)
ORDER BY login_date;Beklenen çıktı
login_date | by_row_number | by_dense_rank 2025-10-02 | 2025-10-01 | 2025-10-01 2025-10-03 | 2025-10-01 | 2025-10-01 2025-10-03 | 2025-09-30 | 2025-10-01 2025-10-04 | 2025-09-30 | 2025-10-01 2025-10-05 | 2025-09-30 | 2025-10-01 2025-10-08 | 2025-10-02 | 2025-10-03
LAG ve kümülatif toplam: oturumlar
ROW_NUMBER hilesi yalnızca adım tam olarak 1 olduğunda çalışır. Sayfa görüntülemelerinde “ardışık” başka türlü ölçülür: iki görüntüleme arasında 30 dakikadan fazla geçmediyse aynı oturumdadırlar. Burada ikinci, daha genel bir yöntem gerekir:
Ara
Önceki olayın zamanını
LAGile al ve farkı dakika olarak hesapla (unixepochsaniye verir).Bayrak
Ara 30 dakikadan fazlaysa ya da önceki olay yoksa (
NULL),new_session = 1, değilse 0.Numara
Bayrakların kümülatif toplamı (
SUM(...) OVER (ORDER BY ...)) oturum numarasıdır.Özet
Oturum numarasına göre
GROUP BY: başlangıç, son, görüntüleme sayısı.
CREATE TABLE page_views (student_id INTEGER, viewed_at TEXT);
INSERT INTO page_views VALUES
(1, '2025-10-01 09:00'), (1, '2025-10-01 09:10'), (1, '2025-10-01 09:35'),
(1, '2025-10-01 11:00'), (1, '2025-10-01 11:20'),
(2, '2025-10-01 10:00'), (2, '2025-10-01 10:50');
WITH gaps AS (
SELECT student_id, viewed_at,
(unixepoch(viewed_at) - unixepoch(LAG(viewed_at) OVER w)) / 60 AS gap_min
FROM page_views
WINDOW w AS (PARTITION BY student_id ORDER BY viewed_at)
),
flagged AS (
SELECT *, CASE WHEN gap_min <= 30 THEN 0 ELSE 1 END AS new_session
FROM gaps
)
SELECT student_id, viewed_at, gap_min, new_session,
SUM(new_session) OVER (PARTITION BY student_id
ORDER BY viewed_at) AS session_no
FROM flagged
ORDER BY student_id, viewed_at;Beklenen çıktı
student_id | viewed_at | gap_min | new_session | session_no 1 | 2025-10-01 09:00 | NULL | 1 | 1 1 | 2025-10-01 09:10 | 10 | 0 | 1 1 | 2025-10-01 09:35 | 25 | 0 | 1 1 | 2025-10-01 11:00 | 85 | 1 | 2 1 | 2025-10-01 11:20 | 20 | 0 | 2 2 | 2025-10-01 10:00 | NULL | 1 | 1 2 | 2025-10-01 10:50 | 50 | 1 | 2
NULL <= 30 doğru değildir; bu yüzden her öğrencinin ilk görüntülemesi otomatik olarak yeni bir oturum açar.Eşit değerlerin adaları: durum dönemleri
Bazen ada “ardışık değerler” değil, “aynı durumdaki ardışık satırlar”dır: sunucu her gün denetlenir, up ya da down. Burada iki numaralamanın farkı çalışır: genel ROW_NUMBER ve durum içindeki ROW_NUMBER. Tek bir durumun kesintisiz döneminde ikisi birlikte artar ve fark sabittir:
CREATE TABLE checks (day TEXT, status TEXT);
INSERT INTO checks VALUES
('2025-10-01', 'up'), ('2025-10-02', 'up'), ('2025-10-03', 'down'),
('2025-10-04', 'down'), ('2025-10-05', 'up'), ('2025-10-06', 'down'),
('2025-10-07', 'up'), ('2025-10-08', 'up');
WITH g AS (
SELECT day, status,
ROW_NUMBER() OVER (ORDER BY day)
- ROW_NUMBER() OVER (PARTITION BY status ORDER BY day) AS grp
FROM checks
)
SELECT status, MIN(day) AS from_day, MAX(day) AS to_day, COUNT(*) AS days
FROM g
GROUP BY status, grp
ORDER BY from_day;Beklenen çıktı
status | from_day | to_day | days up | 2025-10-01 | 2025-10-02 | 2 down | 2025-10-03 | 2025-10-04 | 2 up | 2025-10-05 | 2025-10-05 | 1 down | 2025-10-06 | 2025-10-06 | 1 up | 2025-10-07 | 2025-10-08 | 2
grp yalnızca durumla birlikte benzersizdir; bu yüzden GROUP BY status, grp.Mini proje: giriş serisi raporu
Temizle
DISTINCT: günde bir giriş.Adalar
Öğrenci başına
ROW_NUMBER, çapa tarih,GROUP BY→ her serinin sonu ve uzunluğu.Göstergeler
Seri sayısı, en uzun seri ve rapor gününde (2025-10-09) biten güncel seri; yoksa 0.
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
(1, '2025-10-01'), (1, '2025-10-02'), (1, '2025-10-03'),
(1, '2025-10-05'), (1, '2025-10-06'), (1, '2025-10-09'),
(2, '2025-10-02'), (2, '2025-10-03'), (2, '2025-10-03'),
(2, '2025-10-04'), (2, '2025-10-05'), (2, '2025-10-08'),
(3, '2025-10-06'), (3, '2025-10-07'), (3, '2025-10-08'), (3, '2025-10-09');
WITH days AS (SELECT DISTINCT student_id, login_date FROM logins),
streaks AS (
SELECT student_id, MAX(login_date) AS end_day, COUNT(*) AS len
FROM (SELECT student_id, login_date,
date(login_date, '-' || ROW_NUMBER() OVER (
PARTITION BY student_id ORDER BY login_date) || ' days') AS grp
FROM days)
GROUP BY student_id, grp
)
SELECT s.first_name,
COUNT(*) AS streaks,
MAX(k.len) AS longest,
COALESCE(MAX(CASE WHEN k.end_day = '2025-10-09'
THEN k.len END), 0) AS current_streak
FROM streaks AS k
JOIN students AS s ON s.id = k.student_id
GROUP BY k.student_id
ORDER BY longest DESC, s.first_name;Beklenen çıktı
first_name | streaks | longest | current_streak Leyla | 1 | 4 | 4 Murad | 2 | 4 | 0 Aysel | 3 | 3 | 1
Raporu okuyalım: Leyla 4 gündür ara vermeden giriş yapıyor; uygulama onu tebrik edebilir. Murad'ın rekoru da 4 gün ama son girişi 8'indeydi ve güncel serisi 0: ona bir hatırlatma gönderme zamanı. Aysel'in 3 serisi var, güncel serisi 1 gün. Bütün mantık tek bir SQL sorgusunda ve istenen sayıda öğrenci için çalışıyor.
Alıştırma
3, 4 ve 8 numaralı siparişler silindi. LEAD ile id'lerdeki boşlukları bul: her boşluk için gap_start ve gap_end, gap_start sırasıyla.
DELETE FROM orders WHERE id IN (3, 4, 8);
-- find the gaps hereBeklenen çıktı
gap_start | gap_end 3 | 4 8 | 8
Alıştırma
Başlangıç kodundaki checks tablosundan sunucunun çalışmadığı (down) dönemleri listele: from_day, to_day, days, from_day sırasıyla.
CREATE TABLE checks (day TEXT, status TEXT);
INSERT INTO checks VALUES
('2025-10-01', 'up'), ('2025-10-02', 'up'), ('2025-10-03', 'down'),
('2025-10-04', 'down'), ('2025-10-05', 'up'), ('2025-10-06', 'down'),
('2025-10-07', 'up'), ('2025-10-08', 'up');
-- periods with status = 'down'Beklenen çıktı
from_day | to_day | days 2025-10-03 | 2025-10-04 | 2 2025-10-06 | 2025-10-06 | 1
Unutma
- Ada kesintisiz bir dizidir; boşluk adalar arasındaki eksiklerdir; “kesintisiz” bir adımla (1, bir gün, 30 dk) tanımlanır.
- Boşluklar:
LEAD(id)ilenext_id - id > 1; her eksik değer için özyinelemeli CTE ya da takvim +LEFT JOIN. - Adalar:
değer − ROW_NUMBER()adanın içinde sabittir; tarihlerdedate(gün, '-' || rn || ' days'). - Tekrarlar önce
DISTINCTile atılır (ya daDENSE_RANKkullanılır), numaralamaPARTITION BYile her nesnede yeniden başlar. - Her eşik için:
LAG→ başlangıç bayrağı → kümülatifSUM= ada numarası (oturumlar). - Aynı durumdaki dönemler:
ROW_NUMBER() OVER (ORDER BY t) − ROW_NUMBER() OVER (PARTITION BY status ORDER BY t).