--- title: 'Pencere İşlevleri' source: 'https://academia.sh/tr/kurslar/ileri-sql/pencere-islevleri' course: 'İleri SQL' language: tr updated: '2026-08-17T18:08:54+00:00' license: 'CC BY-SA 4.0' --- # Pencere İşlevleri OVER yan tümcesiyle satırları koruyarak toplama, bölümleme, çerçeve tanımının RANGE ve ROWS ayrımı, kümülatif toplam ile kayan pencere ve komşu satıra erişim. Ö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. ```bash 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 ``` ```text ┌────────┬──────┐ │ 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: ```bash 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 ``` ```text ┌────────────┬────────────┬─────────────┬──────────┐ │ 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: ```bash 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 ``` ```text ┌────────────┬─────────────┬────────────────┐ │ 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: ```bash 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 ``` ```text ┌────────┬────────────┬────────────┬─────────────┐ │ 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.