SQLite Window Fonksiyonları: OVER ve PARTITION BY

Window Fonksiyonu Satırları Daraltmadan Sütun Ekler

GROUP BY birçok satırı tek satıra indirger. SQLite window fonksiyonları ise işi farklı yapar: birbiriyle ilişkili bir satır kümesi üzerinden bir değer hesaplar, ama giriş satırlarının hepsini çıktıda korur. Yani satır bazındaki ayrıntıyı ve toplulaştırılmış sonucu yan yana görürsünüz.

Kalıbı her zaman aynı: önce bir fonksiyon, ardından OVER (...).

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

total_all sütunu, tüm satırların genel toplamını gösterir ve bu değer her satırda aynı şekilde tekrarlanır. Orijinal satırlara dokunulmaz. Bunu SELECT SUM(amount) FROM sales ile karşılaştırın — sonuç aynı sayı, ama bu sefer geriye sadece tek bir satır döner. Window fonksiyonları sayesinde her iki görünüme aynı anda sahip olursunuz.

PARTITION BY: Gruplar İçinde Toplama

Boş bir OVER () ifadesi, toplama işlemini tüm tablo üzerinde yapar. PARTITION BY eklediğinizde ise toplama işlemi gruplar içinde gerçekleşir — tıpkı GROUP BY gibi, ancak satırları birleştirmeden.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

Her satır kendi bölgesinin toplamını ve o toplam içindeki payını alıyor. Düz bir GROUP BY ile çalışsaydık, çalışan bazındaki detayı kaybederdik. İşte window fonksiyonlarının en büyük kozu tam da bu: tek sorguda hem detay hem de toplulaştırma.

Sıralama: ROW_NUMBER, RANK, DENSE_RANK

Sıralama fonksiyonları, OVER içindeki ORDER BY ifadesine göre satırları numaralandırır. Üç fonksiyon arasındaki fark, eşitlik durumlarını nasıl ele aldıklarında ortaya çıkıyor.

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

Çıktıyı okurken şunlara dikkat edin:

• ROW_NUMBER() her zaman benzersizdir — eşitlikler rastgele bir şekilde bozulur. Her satıra sabit ve farklı bir numara vermek istediğinizde bunu kullanın.
• RANK() eşit satırlara aynı sırayı verir, ardından sonraki numaraları atlar. 1. sırada eşit olan iki oyuncudan sonra 3. sıra gelir.
• DENSE_RANK() da eşitlik yapar ama atlama yapmaz. Bir sonraki sıra 2 olur.

"Grup başına ilk N kayıt" senaryosunda sıralamayı PARTITION BY ile birleştirip dış sorguda filtrelemeniz gerekir — çünkü WHERE window fonksiyonlarına doğrudan referans veremez:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

Her bölgedeki en yüksek kazançlı iki kişi.

LAG ve LEAD: Komşu satırlara göz atmak

LAG(col), pencere içinde bir önceki satırdaki col değerini döndürür. LEAD(col) ise bir sonraki satıra uzanır. İkisi de "zaman içinde ne değişti?" tarzı soruları yanıtlamak için birebirdir.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS yesterday,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

İlk satırın yesterday değeri NULL — çünkü öncesinde bir şey yok. İsterseniz varsayılan bir değer de verebilirsiniz: LAG(celsius, 1, celsius) OVER (ORDER BY day) ifadesi, önceki satır olmadığında bugünkü değeri kullanır.

LEAD ise bunun ayna görüntüsü: bir sonraki satıra bakar. İkisini PARTITION BY ile birleştirirseniz grup bazlı sıralamalar elde edersiniz — örneğin her bölge için bu ayın satışlarını geçen ayınkiyle karşılaştırmak gibi.

Window Frame ile Kümülatif Toplam (Running Total)

OVER içine ORDER BY eklediğiniz anda SUM, AVG, COUNT gibi toplama fonksiyonları artık kümülatif olarak hesaplama yapmaya başlar:

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

Dikkat etmeniz gereken iki nokta var:

• SUM(amount) OVER (ORDER BY day) bir running total (kümülatif toplam) hesaplar. ORDER BY yazıp da çerçeveyi (frame) açıkça belirtmediğinizde, varsayılan çerçeve "pencerenin başından mevcut satıra kadar" olur.
• İkinci sütunda ise çerçeve açıkça yazılmış: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Bu, 3 satırlık kayan bir pencere — yani hareketli ortalama.

Çerçeveler için zihinsel model şu: Her window fonksiyonu, mevcut satıra göre tanımlanmış bir satır çerçevesi üzerinden hesaplanır. Sık kullanılan çerçeveler:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — kümülatif toplam (örtük varsayılan budur).
• ROWS BETWEEN N PRECEDING AND CURRENT ROW — geriye dönük (trailing) pencere.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — partition'ın tamamı.

ROWS fiziksel satırları sayar. Bir de RANGE var; o ise değere göre gruplar. ORDER BY sütununda eşit değerleriniz olduğunda ve bunların tek bir adım gibi davranmasını istediğinizde işinize yarar.

FIRST_VALUE, LAST_VALUE ve NTILE

Bilmeye değer birkaç window fonksiyonu daha var:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

• FIRST_VALUE ve LAST_VALUE, çerçeve içindeki ilk veya son değeri döndürür. LAST_VALUE kullanırken çerçeveye dikkat etmek lazım: varsayılan çerçeve CURRENT ROW ile biter, dolayısıyla partition'ın gerçek son değerini almak için genelde ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING yazmanız gerekir.
• NTILE(n), satırları n adet aşağı yukarı eşit kovaya böler — çeyreklikler, yüzdelikler veya A/B tarzı bölmeler için ideal bir sqlite NTILE fonksiyonu.

WINDOW ile Pencereye İsim Vermek

Birden fazla sütun aynı OVER (...) ifadesini paylaşıyorsa, aynı şeyi tekrar tekrar yazmak yorucu olur. SQLite, pencereyi bir kez tanımlayıp birden çok yerde tekrar kullanmanıza izin verir:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

Aynı sorgu, daha az gürültü. WINDOW ifadesi WHERE/GROUP BY/HAVING sonrasında, ORDER BY öncesinde gelir.

Window Function vs GROUP BY: Aradaki Fark

İkisi de bir tür gruplama yapar ama farklı sorulara cevap verirler:

• GROUP BY daraltır. Her grup için tek satır döner. Sadece özet istiyorsanız bunu kullanırsınız.
• Window function ise korur. Tüm satırlar olduğu gibi kalır, yanlarına hesaplanmış ek kolonlar eklenir.

Eğer kendini GROUP BY ile özet hesaplayıp sonra bu özetleri tekrar orijinal tabloya JOIN'lerken bulursanız, bu işin tek sorguda window function ile çözülebileceğinin güçlü bir işaretidir.

Dikkat Edilmesi Gereken Birkaç Tuzak

• WHERE içinde window function kullanılamaz. Filtreleme, window hesaplanmadan önce yapılır. Sorguyu bir alt sorguya ya da CTE'ye sarıp dış katmanda filtrelemeniz gerekir.
• Varsayılan frame canını sıkabilir. SUM(x) OVER (ORDER BY y) aslında bir running total üretir, çünkü varsayılan frame RANGE UNBOUNDED PRECEDING'dir. Tüm partition'ın toplamını istiyorsanız ya ORDER BY olmadan OVER (PARTITION BY ...) yaz ya da frame'i açıkça belirt.
• LAST_VALUE herkesi ilk seferinde şaşırtır. Varsayılan frame mevcut satırda bittiği için, partition'ın son değerini değil, mevcut satırın değerini döndürür. Frame'i mutlaka değiştirmelisiniz.
• Window function için SQLite 3.25+ gerekiyor (2018'de yayınlandı). Makul derecede güncel her kurulumda var, ama bazı gömülü ortamlar geride kalmış olabilir.

Sırada: Generated Columns

Window function'lar sorgu anında yapılan hesaplamadır. Sonraki sayfada depolama anında yapılan hesaplamayı, yani generated column'ları işleyeceğiz: kolonun değerinin bir ifadeyle tanımlandığı ve altta yatan veri değiştikçe otomatik güncellendiği yapı.