Diziler: boşluklar ve adalar

Bir dizideki eksik numaraları ve tarihleri bulmak, ardışık satırları ROW_NUMBER farkıyla gruplamak, LAG ve kümülatif toplamla oturumlar kurmak, aynı durumdaki dönemleri ayırmak ve bir giriş serisi raporu yazmak.

  • İleri
  • 22 dk
  • 12 soru
  • Ders 22 / 28

Bu derste öğreneceklerin

  • LEAD ve özyinelemeli bir CTE ile eksik id'leri ve tarihleri bulmak
  • Ardışık değerleri “değer − ROW_NUMBER” hilesiyle adalara gruplamak
  • Olayları LAG ve kümülatif SUM ile oturumlara bölmek
  • Seriler, dönemler ve tarih aralıkları üzerine gerçek bir rapor kurmak

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:

SQL
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 boşluk bir satırdır: başlangıcı ve sonu.

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.

SQL
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:

SQL
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
Filtre WHERE içinde değil ON içinde; yoksa LEFT JOIN sıradan bir JOIN olurdu.

Okumaya devam etmek için ücretsiz kaydol

Dersin geri kalan bölümleri, test ve notlar Educora hesabıyla açılır. Kayıt bir dakika sürer; ilerlemen tüm cihazlarında saklanır.

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”:

grp = value − ROW_NUMBER() OVER (ORDER BY value)

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.

valueROW_NUMBERvalue − ROW_NUMBERAda
110A
220A
330A
642B
752B
963C
Adalar 1–3, 6–7 ve 9; boşluklar 4–5 ve 8.

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

1) Numaralar: 1, 2, 3, 4, 5, 6.
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:

SQL
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
Üç farklı çapa, üç seri.

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:

SQL
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
SQL
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:

  1. Ara

    Önceki olayın zamanını LAG ile al ve farkı dakika olarak hesapla (unixepoch saniye verir).

  2. Bayrak

    Ara 30 dakikadan fazlaysa ya da önceki olay yoksa (NULL), new_session = 1, değilse 0.

  3. Numara

    Bayrakların kümülatif toplamı (SUM(...) OVER (ORDER BY ...)) oturum numarasıdır.

  4. Özet

    Oturum numarasına göre GROUP BY: başlangıç, son, görüntüleme sayısı.

SQL
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:

SQL
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

  1. Temizle

    DISTINCT: günde bir giriş.

  2. Adalar

    Öğrenci başına ROW_NUMBER, çapa tarih, GROUP BY → her serinin sonu ve uzunluğu.

  3. Göstergeler

    Seri sayısı, en uzun seri ve rapor gününde (2025-10-09) biten güncel seri; yoksa 0.

SQL
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.

Alıştırma · SQL
DELETE FROM orders WHERE id IN (3, 4, 8);

-- find the gaps here
Beklenen çı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.

Alıştırma · SQL
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) ile next_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; tarihlerde date(gün, '-' || rn || ' days').
  • Tekrarlar önce DISTINCT ile atılır (ya da DENSE_RANK kullanılır), numaralama PARTITION BY ile her nesnede yeniden başlar.
  • Her eşik için: LAG → başlangıç bayrağı → kümülatif SUM = ada numarası (oturumlar).
  • Aynı durumdaki dönemler: ROW_NUMBER() OVER (ORDER BY t) − ROW_NUMBER() OVER (PARTITION BY status ORDER BY t).
Bu derste bir hata ya da yanlışlık mı gördün?

Kendini test et

12 soru. Her doğru cevap XP kazandırır.

Soru 1 / 12

Soru 1 / 12. “Ada” nedir?