Ortak Tablo İfadeleri

Önceki dersin birleştirmeli yazımı ara sonucu FROM içine gömülü bir alt sorgu olarak kurmuştu. Tek katmanda bu okunur; iki katmanda okunmaz. Ara sonucun tanımı sorgunun ortasına gömülür, adı ancak kapanış parantezinden sonra görünür ve aynı ara sonuç iki yerde gerekiyorsa tanımı iki kez yazılır.

Ortak tablo ifadesi (common table expression), bu üç sorunu birden çözer: ara sonuca sorgunun başında ad verir, gövdeyi o adı kullanan sade bir sorguya indirger ve aynı adı gövde içinde birden çok kez kullanmaya izin verir.

Adlandırma

Bir ortak tablo ifadesi WITH anahtar sözcüğüyle başlar; ad, isteğe bağlı sütun listesi ve parantez içinde bir sorgu içerir. Bu tanımdan sonra gelen deyimin gövdesinde ad, bir tablo gibi kullanılır.

“Şube başına, üye başına düşen ortalama ödünç sayısı” sorusu iki katmanlıdır: önce üye başına sayım, sonra şube içinde ortalama. Aşağıdaki blok aynı soruyu iki yazımla sorar — biri gömülü alt sorguyla, diğeri adlandırılmış ifadeyle:

sqlite3 -box -header <<'SQL'
CREATE TABLE sube(id INTEGER PRIMARY KEY, ad TEXT, ust_id INT);
CREATE TABLE uye(id INTEGER PRIMARY KEY, ad TEXT, sube_id INT);
CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT, iade TEXT);
INSERT INTO sube VALUES (4,'Kadikoy',2),(5,'Uskudar',2),(6,'Besiktas',3);
INSERT INTO uye VALUES (1,'Ayse',4),(2,'Burak',4),(3,'Ceren',5),(4,'Deniz',5),
                       (5,'Emre',6),(6,'Fatma',6),(7,'Gokhan',4),(8,'Hale',5);
INSERT INTO odunc VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

.print -- gömülü alt sorgu --
SELECT s.ad AS sube, ROUND(AVG(t.adet), 2) AS uye_basina
FROM (SELECT u.sube_id, u.id, COUNT(o.id) AS adet
      FROM uye u LEFT JOIN odunc o ON o.uye_id = u.id
      GROUP BY u.sube_id, u.id) AS t
JOIN sube s ON s.id = t.sube_id
GROUP BY s.id, s.ad
ORDER BY uye_basina DESC;

.print -- ortak tablo ifadesi --
WITH uye_sayim AS (
  SELECT u.sube_id, u.id AS uye_id, COUNT(o.id) AS adet
  FROM uye u LEFT JOIN odunc o ON o.uye_id = u.id
  GROUP BY u.sube_id, u.id
)
SELECT s.ad AS sube, ROUND(AVG(t.adet), 2) AS uye_basina
FROM uye_sayim t JOIN sube s ON s.id = t.sube_id
GROUP BY s.id, s.ad
ORDER BY uye_basina DESC;
SQL

-- gömülü alt sorgu --
┌──────────┬────────────┐
│   sube   │ uye_basina │
├──────────┼────────────┤
│ Kadikoy  │ 3.67       │
│ Besiktas │ 3.0        │
│ Uskudar  │ 2.67       │
└──────────┴────────────┘
-- ortak tablo ifadesi --
┌──────────┬────────────┐
│   sube   │ uye_basina │
├──────────┼────────────┤
│ Kadikoy  │ 3.67       │
│ Besiktas │ 3.0        │
│ Uskudar  │ 2.67       │
└──────────┴────────────┘

Sonuçlar aynıdır; değişen okunma sırasıdır. İlk yazımda okuyucu dış sorgunun ne yaptığını anlamak için önce parantezin içine inip çıkmak zorundadır; FROM yan tümcesinin neye karşılık geldiği ancak kapanış parantezinden sonraki AS t görüldüğünde belli olur. İkincisinde tanım yukarıda bitmiştir ve gövde okunurken uye_sayim bir tablo gibi düşünülebilir. LEFT JOIN her iki yazımda da hiç ödünç almamış üyeleri sıfırla korur.

Ortak tablo ifadesi bir görünüm (view) değildir. Görünüm şemada kalıcı bir nesnedir ve başka sorgular da kullanabilir; ortak tablo ifadesi yalnız yazıldığı deyim boyunca vardır, deyim bitince adı da kaybolur. Bu geçicilik bir kısıt değil, tercih sebebidir: tek bir raporun ara adımı için şemaya kalıcı nesne eklemek, o nesneyi bakması gereken bir varlık hâline getirir.

Sütun Adlarını Tanımda Vermek

Adın ardından parantez içinde sütun listesi yazılabilir. Bu liste, iç sorgunun ürettiği sütunlara sırayla ad verir ve iç sorguda her ifadeye tek tek AS yazma gereğini kaldı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'),
  (4,7,1,'2024-03-11',NULL),(6,2,2,'2024-03-01','2024-03-12'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (13,2,3,'2024-03-25',NULL);

WITH sure(odunc_id, uye_id, gun) AS (
  SELECT id, uye_id, julianday(COALESCE(iade, '2024-03-31')) - julianday(alis)
  FROM odunc
)
SELECT uye_id, COUNT(*) AS kayit, CAST(AVG(gun) AS INT) AS ortalama_gun
FROM sure GROUP BY uye_id ORDER BY uye_id;
SQL

┌────────┬───────┬──────────────┐
│ uye_id │ kayit │ ortalama_gun │
├────────┼───────┼──────────────┤
│ 1      │ 3     │ 17           │
│ 2      │ 2     │ 10           │
│ 3      │ 2     │ 6            │
└────────┴───────┴──────────────┘

Sütun listesi verilmişse iç sorgunun ürettiği sütun sayısı listeyle birebir uyuşmak zorundadır; uyuşmazlık sözdizim değil anlam hatasıdır ve motor deyimi reddeder. Burada gun sütunu, iade edilmemiş kayıtlar için sayım gününü varsayan bir ifadedir; adın tanımda verilmesi, gövdenin bu ifadeyi bir daha görmemesini sağlar.

Zincirleme ve Yeniden Kullanım

WITH sonrası virgülle ayrılmış birden çok tanım yazılabilir. Sonraki tanımlar öncekilere başvurabilir; böylece hesap adım adım kurulur. Aynı ad gövdede birden çok kez de kullanılabilir — gömülü alt sorguda mümkün olmayan şey budur:

sqlite3 -box -header <<'SQL'
CREATE TABLE sube(id INTEGER PRIMARY KEY, ad TEXT, ust_id INT);
CREATE TABLE uye(id INTEGER PRIMARY KEY, ad TEXT, sube_id INT);
CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT, iade TEXT);
INSERT INTO sube VALUES (4,'Kadikoy',2),(5,'Uskudar',2),(6,'Besiktas',3);
INSERT INTO uye VALUES (1,'Ayse',4),(2,'Burak',4),(3,'Ceren',5),(4,'Deniz',5),
                       (5,'Emre',6),(6,'Fatma',6),(7,'Gokhan',4),(8,'Hale',5);
INSERT INTO odunc VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

WITH uye_sayim AS (
  SELECT u.sube_id, u.id AS uye_id, COUNT(o.id) AS adet
  FROM uye u LEFT JOIN odunc o ON o.uye_id = u.id
  GROUP BY u.sube_id, u.id
),
sube_toplam AS (
  SELECT sube_id, SUM(adet) AS toplam FROM uye_sayim GROUP BY sube_id
)
SELECT s.ad AS sube, t.toplam,
       ROUND(100.0 * t.toplam / (SELECT SUM(toplam) FROM sube_toplam), 1) AS yuzde
FROM sube_toplam t JOIN sube s ON s.id = t.sube_id
ORDER BY t.toplam DESC;
SQL

┌──────────┬────────┬───────┐
│   sube   │ toplam │ yuzde │
├──────────┼────────┼───────┤
│ Kadikoy  │ 11     │ 44.0  │
│ Uskudar  │ 8      │ 32.0  │
│ Besiktas │ 6      │ 24.0  │
└──────────┴────────┴───────┘

Hesap üç adımda kuruldu: üye başına sayım, şube başına toplam, toplamın genel toplama oranı. sube_toplam iki yerde geçiyor — bir kez FROM içinde satır kaynağı, bir kez SELECT listesinde genel toplamı veren skaler alt sorgu olarak. Gömülü yazımda bu, aynı tanımın iki kez kopyalanması demekti; kopyaların biri güncellenip diğeri unutulduğunda yüzdeler sessizce tutarsız hâle gelirdi. Ad tek olduğu için böyle bir sapma imkânsızdır.

Zincir aynı zamanda bir hata ayıklama aracıdır. Gövde geçici olarak SELECT * FROM uye_sayim ile değiştirilerek ilk adımın çıktısı doğrudan görülebilir; sonra sube_toplam sınanır. Gömülü yazımda bunun için parantezin içi elle söküp çalıştırılır, sonra geri konur.

Tanımların sırası bağlayıcıdır: bir ifade yalnız kendinden önce tanımlanmış ifadelere başvurabilir. Kendi kendine başvuru ise özel bir durumdur ve ayrı bir anahtar sözcük gerektirir — sonraki dersin konusu.

Maddeleştirme Kararı

Ad tek olsa da hesap tek olmak zorunda değildir. Motorun iki seçeneği vardır: ifadeyi bir kez hesaplayıp sonucu geçici bir yapıda tutmak (maddeleştirme, materialization) ya da tanımı her kullanım yerine yerinde açmak. İlki tekrar hesabı önler; ikincisi dış koşulların ifadenin içine itilmesine ve dizinlerin kullanılmasına izin verir. İkisi de her zaman iyi değildir, bu yüzden karar planlayıcıya bırakılmıştır.

Fark, önceki dersteki sayaçla ölçülebilir. Aşağıdaki üç sorgu aynı ifadeyi iki kez kullanır; tek fark, motora verilen maddeleştirme yönergesidir:

cat > cte-olcum.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE odunc(id INTEGER PRIMARY KEY, uye_id INT, alis TEXT);
  INSERT INTO odunc VALUES
    (1,1,'2024-03-01'),(2,1,'2024-03-01'),(3,1,'2024-03-04'),(4,1,'2024-03-11'),
    (5,1,'2024-03-18'),(6,2,'2024-03-01'),(7,2,'2024-03-06'),(8,2,'2024-03-11'),
    (9,2,'2024-03-21'),(10,3,'2024-03-04'),(11,3,'2024-03-06'),(12,3,'2024-03-13'),
    (13,3,'2024-03-25'),(14,4,'2024-03-04'),(15,4,'2024-03-13'),(16,4,'2024-03-20'),
    (17,5,'2024-03-06'),(18,5,'2024-03-11'),(19,5,'2024-03-25'),(20,6,'2024-03-04'),
    (21,6,'2024-03-13'),(22,6,'2024-03-20'),(23,7,'2024-03-11'),(24,7,'2024-03-18'),
    (25,8,'2024-03-13');
`);

let ziyaret = 0;
db.function('gecis', (deger) => { ziyaret += 1; return deger; });

const olc = (etiket, sorgu) => {
  ziyaret = 0;
  const satir = db.prepare(sorgu).get();
  console.log(etiket, JSON.stringify(satir), '| ziyaret:', ziyaret);
};

olc('varsayılan     ', `
  WITH sayim AS (SELECT uye_id, COUNT(*) AS adet FROM odunc
                 WHERE gecis(uye_id) IS NOT NULL GROUP BY uye_id)
  SELECT (SELECT MAX(adet) FROM sayim) AS en_cok,
         (SELECT MIN(adet) FROM sayim) AS en_az`);

olc('MATERIALIZED   ', `
  WITH sayim AS MATERIALIZED (SELECT uye_id, COUNT(*) AS adet FROM odunc
                 WHERE gecis(uye_id) IS NOT NULL GROUP BY uye_id)
  SELECT (SELECT MAX(adet) FROM sayim) AS en_cok,
         (SELECT MIN(adet) FROM sayim) AS en_az`);

olc('NOT MATERIALIZED', `
  WITH sayim AS NOT MATERIALIZED (SELECT uye_id, COUNT(*) AS adet FROM odunc
                 WHERE gecis(uye_id) IS NOT NULL GROUP BY uye_id)
  SELECT (SELECT MAX(adet) FROM sayim) AS en_cok,
         (SELECT MIN(adet) FROM sayim) AS en_az`);
JS
node cte-olcum.mjs

varsayılan      {"en_cok":5,"en_az":1} | ziyaret: 25
MATERIALIZED    {"en_cok":5,"en_az":1} | ziyaret: 25
NOT MATERIALIZED {"en_cok":5,"en_az":1} | ziyaret: 50

Üç sonuç aynı, iş miktarı farklı. Maddeleştirildiğinde 25 satırlık tablo bir kez taranır; yerinde açıldığında tanım iki kullanım yerine ayrı ayrı kopyalanır ve tablo iki kez, yani toplam 50 satır taranır. Bu ölçümde varsayılan davranış maddeleştirmeyle aynı çıktı; sorgunun biçimi değişseydi planlayıcı diğer kararı da verebilirdi.

Buradaki MATERIALIZED ve NOT MATERIALIZED yönergeleri motora özgüdür; standart SQL böyle bir denetim tanımlamaz ve her motorda bulunmaz. Standart olan, ortak tablo ifadesinin anlamıdır: hangi sonucu ürettiği tanımlıdır, kaç kez hesaplandığı planlayıcıya bırakılmıştır. Bu yüzden “ortak tablo ifadesi kullanınca sonuç bir kez hesaplanır” cümlesi taşınabilir bir varsayım değildir; bazı motorlarda ifade her zaman maddeleştirilir, bazı motorlarda hiç maddeleştirilmez, bazılarında karar sorguya göre değişir. Bir ifade birden çok yerde kullanılıyorsa ve tanımı pahalıysa, hedef motorun davranışının ölçülmesi gerekir.

Veri Değiştiren Deyimlerde

WITH yalnız SELECT deyiminin önüne değil, INSERT, UPDATE ve DELETE deyimlerinin önüne de yazılabilir. Hangi satırların değiştirileceğini belirleyen mantık böylece deyimden ayrılır ve tek başına okunabilir hâle gelir:

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
  (4,7,1,'2024-03-11',NULL),(9,8,2,'2024-03-21',NULL),(13,2,3,'2024-03-25',NULL),
  (16,4,4,'2024-03-20',NULL),(22,3,6,'2024-03-20',NULL),(1,1,1,'2024-03-01','2024-03-15');

WITH gecikmis AS (
  SELECT id FROM odunc WHERE iade IS NULL AND alis <= '2024-03-20'
)
UPDATE odunc SET iade = '2024-03-31' WHERE id IN (SELECT id FROM gecikmis);
SELECT changes() AS guncellenen;
SELECT id, alis, iade FROM odunc WHERE iade IS NULL ORDER BY id;
SQL

┌─────────────┐
│ guncellenen │
├─────────────┤
│ 3           │
└─────────────┘
┌────┬────────────┬──────┐
│ id │    alis    │ iade │
├────┼────────────┼──────┤
│ 9  │ 2024-03-21 │      │
│ 13 │ 2024-03-25 │      │
└────┴────────────┴──────┘

Üç satır güncellendi; iade tarihi 20 Martı geçen iki kayıt açık kaldı. Bu yazımın pratik yararı, güncellemeden önce aynı ifadenin SELECT ile çalıştırılıp etkilenecek satırların görülebilmesidir. Seçim ölçütü tek yerde tanımlı olduğu için denetleme ile uygulama arasında sapma kalmaz — koşulu iki kez yazmanın en pahalı sonucu, güncellemenin denetlenenden farklı bir kümeye çarpmasıdır.

Özet

• Ortak tablo ifadesi, ara sonuca deyimin başında ad verir; ad yalnız o deyim boyunca yaşar ve şemada kalıcı bir nesne oluşturmaz.
• Ada isteğe bağlı sütun listesi eklenebilir; liste verildiğinde iç sorgunun sütun sayısı listeyle birebir uyuşmalıdır.
• Birden çok ifade virgülle zincirlenir; her ifade yalnız kendinden önce tanımlananlara başvurabilir ve aynı ad gövdede birden çok kez kullanılabilir.
• Adın tek olması hesabın tek olduğunu göstermez: maddeleştirme kararı planlayıcıya aittir ve aynı ifade iki kez hesaplanabilir; ölçümde bu 25’e karşı 50 satır olarak çıktı.
• WITH, veri değiştiren deyimlerin önüne de yazılabilir; seçim ölçütü böylece uygulamadan önce SELECT ile denetlenebilir.

Sonraki Adım

Bir ortak tablo ifadesinin yalnız kendinden önce tanımlananlara başvurabildiği söylendi; tek istisna, ifadenin kendine başvurmasıdır. Bu istisna küçük bir sözdizim ayrıntısı değildir: sabit sayıda adımla ifade edilemeyen hesapları — şube ağacında köke kadar yükselmeyi, bir düğümden erişilebilen tüm düğümleri bulmayı — SQL’in erişimine açar. Sonraki ders özyinelemeli ortak tablo ifadesini tanımlayacak, hiyerarşi üzerinde derinlik hesaplayacak ve döngü koruması olmadan ne olduğunu sınırlı adımla gösterecek.