Pencere İşlevleri

Önceki dersteki hesapların ortak yanı, satır kümesini değiştirmeleriydi: gezinme yeni satırlar üretti, gruplama satırları teke indirdi. Bazı sorularda ise satırlar korunmalı, her satırın yanına komşularına bakan bir değer eklenmelidir.

“Her ödünç işleminin yanında, o üyenin toplam işlem sayısı” böyle bir sorudur. Gruplama bunu doğrudan veremez: GROUP BY uygulanınca ayrıntı satırları yok olur ve geriye grup başına bir satır kalır. Ayrıntıyı korumak isteyen, gruplanmış sonucu ayrıntıyla yeniden birleştirmek zorunda kalır. Pencere işlevleri (window functions), bu birleştirmeyi gereksiz kılar: toplama işlevini satırları yutmadan, her satırın kendi görüş alanı üzerinde çalıştırır.

Aynı İşlev, İki Kullanım

Bir toplama işlevinin ardına OVER yan tümcesi eklendiğinde işlev pencere işlevine dönüşür. OVER boş bırakılırsa pencere tüm sonuç kümesidir; PARTITION BY verilirse sonuç kümesi bölümlere ayrılır ve her satır kendi bölümünü görür.

sqlite3 -box -header <<'SQL'
CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT, iade TEXT);
INSERT INTO odunc VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (6,2,2,'2024-03-01','2024-03-12'),(3,5,1,'2024-03-04','2024-03-18'),
  (7,4,2,'2024-03-06','2024-03-25'),(4,7,1,'2024-03-11',NULL),
  (8,6,2,'2024-03-11','2024-03-19'),(23,2,7,'2024-03-11','2024-03-16'),
  (5,9,1,'2024-03-18','2024-03-29'),(24,5,7,'2024-03-18','2024-03-24'),
  (9,8,2,'2024-03-21',NULL);

SELECT uye_id, COUNT(*) AS adet FROM odunc GROUP BY uye_id ORDER BY uye_id;
SELECT id, uye_id, alis, COUNT(*) OVER (PARTITION BY uye_id) AS uye_adedi
FROM odunc ORDER BY uye_id, alis LIMIT 6;
SQL

┌────────┬──────┐
│ uye_id │ adet │
├────────┼──────┤
│ 1      │ 5    │
│ 2      │ 4    │
│ 7      │ 2    │
└────────┴──────┘
┌────┬────────┬────────────┬───────────┐
│ id │ uye_id │    alis    │ uye_adedi │
├────┼────────┼────────────┼───────────┤
│ 1  │ 1      │ 2024-03-01 │ 5         │
│ 2  │ 1      │ 2024-03-01 │ 5         │
│ 3  │ 1      │ 2024-03-04 │ 5         │
│ 4  │ 1      │ 2024-03-11 │ 5         │
│ 5  │ 1      │ 2024-03-18 │ 5         │
│ 6  │ 2      │ 2024-03-01 │ 4         │
└────┴────────┴────────────┴───────────┘

İlk sorgu on bir satırı üçe indirdi. İkincisi on bir satırı korudu ve her satıra kendi üyesinin toplamını yazdı — birinci sorgunun ürettiği 5, 4, 2 sayıları burada ilgili satırların yanında görünüyor. PARTITION BY ile GROUP BY aynı bölümlemeyi yapar; fark, bölümlemenin sonucunda satırların yutulup yutulmamasıdır.

Değerlendirme sırası bu ayrımı açıklar. Pencere işlevleri, WHERE, GROUP BY ve HAVING uygulandıktan sonra çalışır. İki sonuç doğar: pencere işlevinin girdisi süzülmüş kümedir ve pencere işlevinin sonucu aynı sorgunun WHERE yan tümcesinde kullanılamaz. Pencere sonucuna göre süzmek gerekiyorsa sorgu bir ortak tablo ifadesine sarılır ve süzme dışarıda yapılır.

Çerçeve Tanımı

OVER içine ORDER BY eklendiğinde pencere sıralanır ve çerçeve (frame) kavramı devreye girer: satırın gördüğü alan artık tüm bölüm değil, bölümün sıralı bir dilimidir. Çerçeve iki biçimde tanımlanır:

• ROWS: sınırlar satır sayısıyla ölçülür. Geçerli satırdan geriye iki satır demek, fiziksel olarak iki satır demektir.
• RANGE: sınırlar değer üzerinden ölçülür. Sıralama anahtarında aynı değere sahip satırlar — eşler — çerçeveye birlikte girer ya da birlikte girmez.

ORDER BY verilip çerçeve yazılmazsa varsayılan çerçeve RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW olur. Fark, sıralama anahtarında yinelenen değerler olduğunda ortaya çıkar. Aşağıdaki sorgu üç çerçeveyi aynı veride yan yana koyar:

sqlite3 -box -header <<'SQL'
CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT, iade TEXT);
INSERT INTO odunc VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (6,2,2,'2024-03-01','2024-03-12'),(3,5,1,'2024-03-04','2024-03-18'),
  (7,4,2,'2024-03-06','2024-03-25'),(4,7,1,'2024-03-11',NULL),
  (8,6,2,'2024-03-11','2024-03-19'),(23,2,7,'2024-03-11','2024-03-16'),
  (5,9,1,'2024-03-18','2024-03-29'),(24,5,7,'2024-03-18','2024-03-24'),
  (9,8,2,'2024-03-21',NULL);

SELECT alis,
       COUNT(*) OVER (ORDER BY alis) AS varsayilan,
       COUNT(*) OVER (ORDER BY alis
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS satir_bazli,
       COUNT(*) OVER (ORDER BY alis
                      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS kayan_uc
FROM odunc ORDER BY alis, id;
SQL

┌────────────┬────────────┬─────────────┬──────────┐
│    alis    │ varsayilan │ satir_bazli │ kayan_uc │
├────────────┼────────────┼─────────────┼──────────┤
│ 2024-03-01 │ 3          │ 1           │ 1        │
│ 2024-03-01 │ 3          │ 2           │ 2        │
│ 2024-03-01 │ 3          │ 3           │ 3        │
│ 2024-03-04 │ 4          │ 4           │ 3        │
│ 2024-03-06 │ 5          │ 5           │ 3        │
│ 2024-03-11 │ 8          │ 6           │ 3        │
│ 2024-03-11 │ 8          │ 7           │ 3        │
│ 2024-03-11 │ 8          │ 8           │ 3        │
│ 2024-03-18 │ 10         │ 9           │ 3        │
│ 2024-03-18 │ 10         │ 10          │ 3        │
│ 2024-03-21 │ 11         │ 11          │ 3        │
└────────────┴────────────┴─────────────┴──────────┘

Üç sütun aynı veriden üç farklı yanıt üretti.

İlk sütunda 1 Mart’ın üç satırı da 3 değerini aldı: değer tabanlı çerçeve, aynı tarihe sahip satırları tek bir eş kümesi sayar ve hepsine kümenin sonundaki toplamı verir. Bu, “1 Mart sonu itibarıyla toplam” sorusunun yanıtıdır ve satırların tablodaki sırasından etkilenmez.

İkinci sütun satır tabanlıdır: her satır bir öncekinin üstüne bir ekler ve 1, 2, 3 diye ilerler. Aynı tarihli satırların farklı değerler alması, sonucun sıralamaya bağlı olduğunu gösterir. Sıralama anahtarı satırları tam olarak ayırmıyorsa bu sütunun değeri belirlenimci değildir: aynı sorgu, motorun satırları farklı sırada okuduğu bir çalışmada üçlü grubun içinde farklı dağılım verebilir. Ayrım gerekiyorsa ORDER BY anahtarına tekilliği sağlayan bir sütun eklenir.

Üçüncü sütun kayan penceredir: geçerli satır ve ondan önceki iki satır. İlk iki satırda çerçeve henüz dolmadığı için 1 ve 2 değerleri çıktı; sonrasında sabit 3 kaldı. Kayan pencere, kümülatif toplamın tersine yereldir: uzak geçmiş çerçeveden düşer.

Bu üç davranış farkı, çerçevenin bir ayrıntı olmadığını gösterir. Aynı işlev, aynı sıralama ve aynı veriyle yazılmış üç ifade üç ayrı sütun üretti; hangisinin doğru olduğu sorulan soruya bağlıdır.

Kümülatif Toplam ve Kayan Ortalama

Çerçeveler her sütunda tekrar yazılırsa sorgu okunmaz hâle gelir. WINDOW yan tümcesi pencere tanımlarına ad verir; OVER sonrasında yalnız ad yazılır:

sqlite3 -box -header <<'SQL'
CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT, iade TEXT);
INSERT INTO odunc VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (6,2,2,'2024-03-01','2024-03-12'),(3,5,1,'2024-03-04','2024-03-18'),
  (7,4,2,'2024-03-06','2024-03-25'),(4,7,1,'2024-03-11',NULL),
  (8,6,2,'2024-03-11','2024-03-19'),(23,2,7,'2024-03-11','2024-03-16'),
  (5,9,1,'2024-03-18','2024-03-29'),(24,5,7,'2024-03-18','2024-03-24'),
  (9,8,2,'2024-03-21',NULL);

SELECT alis,
       CAST(SUM(julianday(COALESCE(iade,'2024-03-31')) - julianday(alis))
            OVER kumulatif AS INT) AS gun_toplami,
       ROUND(AVG(julianday(COALESCE(iade,'2024-03-31')) - julianday(alis))
             OVER kayan, 1) AS kayan_ortalama
FROM odunc
WINDOW kumulatif AS (ORDER BY alis ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
       kayan AS (ORDER BY alis ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY alis, id;
SQL

┌────────────┬─────────────┬────────────────┐
│    alis    │ gun_toplami │ kayan_ortalama │
├────────────┼─────────────┼────────────────┤
│ 2024-03-01 │ 14          │ 14.0           │
│ 2024-03-01 │ 33          │ 16.5           │
│ 2024-03-01 │ 44          │ 14.7           │
│ 2024-03-04 │ 58          │ 14.7           │
│ 2024-03-06 │ 77          │ 14.7           │
│ 2024-03-11 │ 97          │ 17.7           │
│ 2024-03-11 │ 105         │ 15.7           │
│ 2024-03-11 │ 110         │ 11.0           │
│ 2024-03-18 │ 121         │ 8.0            │
│ 2024-03-18 │ 127         │ 7.3            │
│ 2024-03-21 │ 137         │ 9.0            │
└────────────┴─────────────┴────────────────┘

Ölçülen değer, her işlemin kaç gün sürdüğüdür; iade edilmemiş kayıtlar için sayım günü varsayılmıştır. Birinci sütun bu sürelerin kümülatif toplamıdır ve hep artar. İkinci sütun son üç işlemin ortalama süresidir ve iner çıkar: mart ortasından sonra kısa süreli işlemler geldikçe ortalama 17,7’den 7,3’e düşüyor. Kümülatif toplam eğilimi gizler, kayan ortalama gösterir; ikisi aynı veriden okunan iki ayrı bilgidir.

WINDOW yan tümcesi standarttadır ve sorgunun sonuna, ORDER BY öncesine yazılır. Desteklenmediği bir motorda tanımlar OVER içine açık açık kopyalanır; anlam değişmez.

Komşu Satıra Erişim

Bazı işlevler yalnız pencere işlevi olarak vardır; toplama karşılıkları yoktur. LAG ve LEAD, aynı bölümde belirli sayıda önceki ya da sonraki satırın değerini getirir:

sqlite3 -box -header <<'SQL'
CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT, iade TEXT);
INSERT INTO odunc VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (6,2,2,'2024-03-01','2024-03-12'),(3,5,1,'2024-03-04','2024-03-18'),
  (7,4,2,'2024-03-06','2024-03-25'),(4,7,1,'2024-03-11',NULL),
  (8,6,2,'2024-03-11','2024-03-19'),(23,2,7,'2024-03-11','2024-03-16'),
  (5,9,1,'2024-03-18','2024-03-29'),(24,5,7,'2024-03-18','2024-03-24'),
  (9,8,2,'2024-03-21',NULL);

SELECT uye_id, alis,
       LAG(alis) OVER (PARTITION BY uye_id ORDER BY alis) AS onceki,
       CAST(julianday(alis) - julianday(LAG(alis) OVER (PARTITION BY uye_id ORDER BY alis))
            AS INT) AS aradaki_gun
FROM odunc ORDER BY uye_id, alis;
SQL

┌────────┬────────────┬────────────┬─────────────┐
│ uye_id │    alis    │   onceki   │ aradaki_gun │
├────────┼────────────┼────────────┼─────────────┤
│ 1      │ 2024-03-01 │            │             │
│ 1      │ 2024-03-01 │ 2024-03-01 │ 0           │
│ 1      │ 2024-03-04 │ 2024-03-01 │ 3           │
│ 1      │ 2024-03-11 │ 2024-03-04 │ 7           │
│ 1      │ 2024-03-18 │ 2024-03-11 │ 7           │
│ 2      │ 2024-03-01 │            │             │
│ 2      │ 2024-03-06 │ 2024-03-01 │ 5           │
│ 2      │ 2024-03-11 │ 2024-03-06 │ 5           │
│ 2      │ 2024-03-21 │ 2024-03-11 │ 10          │
│ 7      │ 2024-03-11 │            │             │
│ 7      │ 2024-03-18 │ 2024-03-11 │ 7           │
└────────┴────────────┴────────────┴─────────────┘

Her bölümün ilk satırında önceki satır yoktur; LAG orada boş değer verir ve boş değerle yapılan çıkarma da boş değer üretir. Bölüm sınırının aşılmaması pencerenin tanımı gereğidir: uye_id değiştiğinde geçmiş sıfırlanır, bir üyenin son işlemi başka üyenin ilk işlemine komşu sayılmaz.

Aynı sonucu ilişkili alt sorguyla üretmek mümkündür — “bu satırdan küçük tarihlerin en büyüğü” — ancak o yazım satır başına bir arama yapar. Pencere işlevi bölümü bir kez sıralar ve tek geçişte ilerler.

Özet

• OVER yan tümcesi, toplama işlevini satırları koruyan bir pencere işlevine çevirir; PARTITION BY, GROUP BY ile aynı bölümlemeyi satır yutmadan yapar.
• Pencere işlevleri WHERE, GROUP BY ve HAVING sonrasında çalışır; sonuçlarına göre süzmek için sorgunun bir ortak tablo ifadesine sarılması gerekir.
• Çerçeve ROWS ile satır sayısına, RANGE ile değere göre ölçülür; ORDER BY verilip çerçeve yazılmazsa varsayılan RANGE … CURRENT ROW olur ve eş satırlar aynı sonucu alır.
• Aynı veride varsayılan çerçeve, satır tabanlı kümülatif toplam ve üç satırlık kayan pencere üç ayrı sütun üretti; hangisinin doğru olduğu sorulan soruya bağlıdır.
• LAG ve LEAD bölüm içinde komşu satıra erişir ve bölüm sınırını aşmaz.

Sonraki Adım

Bu derste pencere, satırların üzerinde bir hesap yürüttü ama satırlara sıra vermedi. Oysa “en çok ödünç alan üç üye” gibi sorular sıralama numarası ister ve hemen ardından bir karar gerektirir: eşit sayıya sahip iki üye aynı sırayı mı almalı, farklı mı? Sonraki ders satır numarası, sıra ve yoğun sıra işlevlerini aynı veri üzerinde yan yana koyarak beraberlik davranışlarındaki farkı gösterecek.