Gruplama ve Süzme

Önceki ders toplama işlevlerini kurdu, ama hepsi bütün tabloyu tek bir sonuca indirgedi. Pratikte istenen genellikle bu değildir: “toplam kaç kitap” sorusunun yanıtı bir kez öğrenilir, “her yazarın kaç kitabı var” sorusu ise sürekli sorulur.

Bu ders satırları kümelere ayıran ve her küme için ayrı bir özet üreten yan tümceyi kurar. Ardından, grupların kendisini süzen yan tümceyi tanıtır ve bunun neden satırları süzen yan tümceden ayrı olduğunu aynı sorgu üzerinde ölçer.

Aşağıdaki blok şemayı ve örnek veriyi kurar; dersin bütün sorguları bu blokta oluşturulan kutuphane.db dosyası üzerinde çalışır.

rm -f kutuphane.db
sqlite3 kutuphane.db <<'SQL'
CREATE TABLE sube (sube_id INTEGER PRIMARY KEY, ad TEXT NOT NULL, sehir TEXT NOT NULL);
CREATE TABLE kitap (kitap_id INTEGER PRIMARY KEY, baslik TEXT NOT NULL, yazar TEXT NOT NULL,
                    basim_yili INTEGER, sube_id INTEGER REFERENCES sube(sube_id));
CREATE TABLE uye (uye_id INTEGER PRIMARY KEY, ad TEXT NOT NULL, soyad TEXT NOT NULL,
                  eposta TEXT, kayit_tarihi TEXT NOT NULL);
CREATE TABLE odunc (odunc_id INTEGER PRIMARY KEY, kitap_id INTEGER NOT NULL REFERENCES kitap(kitap_id),
                    uye_id INTEGER NOT NULL REFERENCES uye(uye_id), alis_tarihi TEXT NOT NULL,
                    iade_tarihi TEXT);
INSERT INTO sube VALUES (1,'Merkez','Ankara'),(2,'Bahçelievler','Ankara'),
  (3,'Kadıköy','İstanbul'),(4,'Konak','İzmir');
INSERT INTO kitap VALUES (1,'Körlük','José Saramago',1995,1),(2,'Tutunamayanlar','Oğuz Atay',1972,1),
  (3,'Kum Kitabı','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2),
  (5,'Sessiz Ev','Orhan Pamuk',1983,3),(6,'Anayurt Oteli','Yusuf Atılgan',NULL,3),
  (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL);
INSERT INTO uye VALUES (1,'Ayşe','Demir','[email protected]','2023-02-14'),
  (2,'Mehmet','Kaya','[email protected]','2023-05-30'),(3,'Zeynep','Arslan',NULL,'2024-01-09'),
  (4,'Emre','Yıldız','[email protected]','2024-03-22'),(5,'Selin','Aydın',NULL,'2024-11-05'),
  (6,'Burak','Şahin','[email protected]','2025-01-18');
INSERT INTO odunc VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'),
  (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'),
  (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'),
  (12,4,4,'2025-06-20','2025-07-04');
SQL

Satırları Gruplara Ayırmak

GROUP BY yan tümcesi, belirtilen sütunlarda aynı değeri taşıyan satırları bir grup sayar ve her grup için tek bir sonuç satırı üretir.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT yazar, COUNT(*) AS kitap_sayisi
FROM kitap
GROUP BY yazar
ORDER BY kitap_sayisi DESC, yazar;
SQL

yazar              kitap_sayisi
-----------------  ------------
Oğuz Atay          2           
Jorge Luis Borges  1           
José Saramago      1           
Orhan Pamuk        1           
Yakup Kadri        1           
Yusuf Atılgan      1           

Yedi satır altı gruba ayrıldı ve her grup tek satır olarak döndü. Sıralama, toplama işlevinin ürettiği sütuna göre yapılabiliyor — sıralama gruplamadan sonra değerlendirildiği için bu geçerlidir.

Gruplamalı bir sorguda sütun listesine yazılabilecek şeyler kısıtlıdır: ya gruplama sütunlarından biri, ya bir toplama işlevi. Başka bir sütun yazmak anlamsızdır, çünkü grup içinde o sütunun tek bir değeri yoktur. Bu kuralın uygulanması motora göre değişir — kimi motor hata verir, kimi rastgele bir satırın değerini döndürür.

Boş Değer Kendi Grubunu Kurar

Boş değerlerin gruplamadaki davranışı, koşullardaki davranışından ayrılır.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT sube_id, COUNT(*) AS kitap_sayisi FROM kitap GROUP BY sube_id ORDER BY sube_id;
SQL

sube_id  kitap_sayisi
-------  ------------
         1           
1        2           
2        2           
3        2           

Şube kimliği boş olan kitap yok sayılmadı; kendi başına bir grup oldu. Gruplama boş değerleri birbirine eşit sayar, oysa eşitlik işleci saymaz. Bu tutarsızlık gibi görünür ama gerekli bir seçimdir: aksi halde her boş değer ayrı bir grup olurdu ve gruplama işe yaramazdı.

Gruplama anahtarı ifade de olabilir; on yıllık dilimlere göre dağılım bunu gösteriyor.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT (basim_yili / 10) * 10 AS on_yil, COUNT(*) AS adet
FROM kitap WHERE basim_yili IS NOT NULL
GROUP BY on_yil
ORDER BY on_yil;
SQL

on_yil  adet
------  ----
1930    1   
1970    3   
1980    1   
1990    1   

Dış Birleştirmede Sayım Tuzağı

Gruplama en çok dış birleştirmeyle birlikte kullanılır: her şube için kitap sayısı, kitabı olmayan şubeler de dahil. Bu bileşimde sayım yazımı sonucu doğrudan belirler.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT s.ad AS sube, COUNT(k.kitap_id) AS kitap_sayisi, COUNT(*) AS satir_sayisi
FROM sube AS s
LEFT JOIN kitap AS k ON k.sube_id = s.sube_id
GROUP BY s.sube_id, s.ad
ORDER BY s.sube_id;
SQL

sube          kitap_sayisi  satir_sayisi
------------  ------------  ------------
Merkez        2             2           
Bahçelievler  2             2           
Kadıköy       2             2           
Konak         0             1           

Son satırda iki sütun ayrışıyor. Konak şubesinin hiç kitabı yok, ama dış birleştirme onu korumak için kitap sütunları boş olan bir satır üretti. COUNT(*) o satırı saydı ve 1 verdi — yanlış. COUNT(k.kitap_id) ise boş değeri atladığı için 0 verdi — doğru.

Kural: dış birleştirmeden sonra sayım yapılırken korunan tarafın değil, eşleşen tarafın bir sütunu sayılır ve o sütun boş değer kabul etmeyen bir sütun, tercihen birincil anahtar olmalıdır. Bu, önceki dersteki “toplama işlevleri boş değeri atlar” kuralının doğrudan uygulanmasıdır.

Gruplama anahtarı birden çok sütundan oluşabilir; grup, sütunların bileşimine göre kurulur.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT s.sehir, s.ad AS sube, COUNT(k.kitap_id) AS kitap_sayisi
FROM sube AS s
LEFT JOIN kitap AS k ON k.sube_id = s.sube_id
GROUP BY s.sehir, s.ad
ORDER BY s.sehir, s.ad;
SQL

sehir     sube          kitap_sayisi
--------  ------------  ------------
Ankara    Bahçelievler  2           
Ankara    Merkez        2           
İstanbul  Kadıköy       2           
İzmir     Konak         0           

Şehir ile şube adının her bileşimi ayrı bir grup oldu. Ankara’nın iki şubesi tek satırda toplanmadı, çünkü ikinci anahtar onları ayırıyor.

Satır Süzmesi ile Grup Süzmesi

WHERE yan tümcesi satırları, HAVING yan tümcesi grupları süzer. Aralarındaki fark değerlendirme sırasından gelir: WHERE gruplama öncesinde, HAVING gruplama sonrasında çalışır. Sıra şöyledir — FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

Sonucu ölçmek için aynı soruyu iki kez sormak yeter. Önce süzmesiz hali: en az iki kez ödünç almış üyeler.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT u.ad, u.soyad, COUNT(*) AS odunc_sayisi
FROM odunc AS o
JOIN uye AS u ON u.uye_id = o.uye_id
GROUP BY u.uye_id, u.ad, u.soyad
HAVING COUNT(*) >= 2
ORDER BY odunc_sayisi DESC, u.uye_id;
SQL

ad      soyad   odunc_sayisi
------  ------  ------------
Ayşe    Demir   3           
Emre    Yıldız  3           
Mehmet  Kaya    2           
Zeynep  Arslan  2           
Selin   Aydın   2           

HAVING COUNT(*) >= 2 koşulu gruplar üzerinde çalıştı; iki kayıttan az olan gruplar elendi. Şimdi aynı sorguya bir satır süzmesi eklensin: yalnız iade edilmiş kayıtlar sayılsın.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT u.ad, u.soyad, COUNT(*) AS odunc_sayisi
FROM odunc AS o
JOIN uye AS u ON u.uye_id = o.uye_id
WHERE o.iade_tarihi IS NOT NULL
GROUP BY u.uye_id, u.ad, u.soyad
HAVING COUNT(*) >= 2
ORDER BY odunc_sayisi DESC, u.uye_id;
SQL

ad      soyad   odunc_sayisi
------  ------  ------------
Ayşe    Demir   3           
Zeynep  Arslan  2           
Emre    Yıldız  2           

Beş satır üçe indi ve sayılar değişti. WHERE yan tümcesi iade edilmemiş üç kaydı gruplama başlamadan eledi; gruplar bu eksik veriyle kuruldu ve HAVING koşulu yeni sayılar üzerinde uygulandı. Emre üçten ikiye düştü, Mehmet ile Selin eşiğin altına inip listeden çıktı.

Ayrım şöyle özetlenir: WHERE “hangi satırlar sayılacak”, HAVING “hangi gruplar gösterilecek” sorusunu yanıtlar. İkisi yer değiştiremez. Toplama işlevi içeren bir koşul WHERE içine yazılamaz, çünkü o aşamada henüz grup yoktur.

Tersi de geçerli olmalıdır: gruplanmamış bir sütuna ait koşul HAVING içine yazılmamalıdır. Standart buna izin vermez, ancak uygulanışı motora göre değişir.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT u.ad, COUNT(*) AS adet
FROM odunc AS o
JOIN uye AS u ON u.uye_id = o.uye_id
GROUP BY u.uye_id, u.ad
HAVING o.iade_tarihi IS NOT NULL;
SQL

ad      adet
------  ----
Ayşe    3   
Zeynep  2   
Emre    3   
Selin   2   

Sorgu hata vermedi, ama sonuç hiçbir soruya karşılık gelmiyor: sayılar süzmesiz sorgudaki gibi, satır kümesi ise süzmeli sorguya benziyor. Grup içindeki tek bir satırın iade tarihi değerlendirilmiş ve grubun tamamının kaderi ona bağlanmış. Bu tür sonuçlar hata iletisi üretmeden yanlıştır; grup düzeyi olmayan her koşul WHERE yan tümcesine yazılmalıdır.

HAVING yan tümcesinin SELECT listesinde bulunmayan bir toplama işlevine başvurması ise geçerlidir — grup süzmesi, gösterilen sütunlarla sınırlı değildir.

Özet

• GROUP BY satırları anahtar değerine göre kümelere ayırır ve her küme için tek satır üretir.
• Gruplamalı bir sorgunun sütun listesinde yalnız gruplama sütunları ve toplama işlevleri bulunmalıdır.
• Gruplama boş değerleri birbirine eşit sayar; boş değer kendi grubunu kurar.
• Dış birleştirmeden sonra sayım, eşleşen tarafın boş değer kabul etmeyen bir sütunu üzerinden yapılmalıdır; COUNT(*) korunan satırı da sayar.
• WHERE gruplamadan önce satırları, HAVING gruplamadan sonra grupları süzer; sıra FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY biçimindedir.
• Grup düzeyi olmayan koşulu HAVING içine yazmak, kimi motorda hata vermeden yanlış sonuç üretir.

Sonraki Adım

Gruplama, tek bir sorgunun ürettiği satırları düzenledi. Kimi soru ise iki ayrı sorgunun sonuçlarını karşılaştırmayı ister: iki listede ortak olanlar, birinde olup diğerinde olmayanlar, ikisinin toplamı. Sonraki ders sonuç kümelerini küme olarak birleştiren işlemleri kurar ve yinelenen satırların korunup korunmamasının neden açık bir seçim olduğunu gösterir.