SQL'de Rekürsif Yaklaşım: Mantığı Anlamak
Standart SQL sorguları genellikle mevcut verileri filtreleyip getirirken, SQLite'ın recursive CTE (özyinelemeli ortak tablo ifadesi) özelliği farklı bir mantıkla çalışır. Bu yapı, kendi ürettiği sonuçları tekrar girdi olarak kullanır ve yeni veri üretilemeyene kadar bu işlemi döngüsel olarak sürdürür. Bu yöntem, özellikle derinliği belirsiz ağaç yapılarını taramak veya herhangi bir tabloya ihtiyaç duymadan sayı dizileri oluşturmak için idealdir.
Sistem genel olarak şu şablon üzerinden ilerler:
SQL:
WITH RECURSIVE name(columns) AS (
-- başlangıç: ilk satırlar
SELECT ...
UNION ALL
-- özyinelemeli: önceki adımdan türetilen satırlar
SELECT ... FROM name WHERE ...
)
SELECT * FROM name;
Sürecin başında bir başlangıç (anchor) sorgusu, ortada
UNION ALL ve sonunda ise özyinelemeli (recursive) sorgu bulunur. SQLite önce başlangıç sorgusunu bir kez çalıştırır. Ardından, bir önceki adımda elde edilen satırları girdi olarak kullanarak özyinelemeli kısmı tekrar tekrar işletir. Yeni satır üretimi durduğunda işlem tamamlanır.Sayı Dizisi Oluşturma (1'den 10'a Kadar)
Recursive CTE'nin en basit kullanım örneği, tabloya ihtiyaç duymadan bir sayı serisi üretmektir:
SQL:
WITH RECURSIVE counter(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;
İşleyiş sırası şöyledir:
1. Başlangıç sorgusu ilk satırı üretir:
n = 1.2. Özyinelemeli adım bu değeri alır,
n + 1 = 2 işlemini yapar ve 2 < 10 şartı sağlandığı için bu satırı listeye ekler.3. Bir sonraki döngüde
n = 2 değeri işlenerek n = 3 üretilir ve süreç bu şekilde devam eder.4.
n değeri 10 olduğunda, 10 < 10 ifadesi yanlış olduğu için özyinelemeli adım artık satır döndürmez ve işlem sona erer.Sorgunun sonsuz döngüye girmemesi için
WHERE n < 10 gibi bir durdurma koşulunun eklenmesi kritiktir.Tarih Serileri Üretmek
Bu mantık, raporlama süreçlerinde boşlukları doldurmak için oldukça kullanışlıdır. Belirli bir tarih aralığındaki tüm günleri (veri olsun ya da olmasın) listelemek için şu yapı kullanılır:
SQL:
WITH RECURSIVE days(d) AS (
SELECT date('2024-01-01')
UNION ALL
SELECT date(d, '+1 day') FROM days WHERE d < '2024-01-07'
)
SELECT d FROM days;
Kodu çalıştırdığınızda elde ettiğiniz bu seriyi, bir etkinlikler tablosuyla
LEFT JOIN yaparak, hiçbir aktivitenin olmadığı günleri de raporunuza dahil edebilirsiniz. Sadece GROUP BY date kullanmak veri olmayan günleri atlar; ancak tarih serisi oluşturmak her güne karşılık bir satır garanti eder.Hiyerarşik (Parent-Child) Yapılarda Gezinme
Bu özelliğin en yaygın kullanım alanı ağaç yapılardır. Örneğin, her çalışanın kendi yöneticisini belirttiği bir tabloyu düşünelim:
SQL:
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
manager_id INTEGER REFERENCES employees(id)
);
INSERT INTO employees VALUES
(1, 'Ada', NULL),
(2, 'Boris', 1),
(3, 'Carla', 1),
(4, 'Diego', 2),
(5, 'Elena', 2),
(6, 'Farouk', 3);
WITH RECURSIVE org(id, name, manager_id, depth) AS (
SELECT id, name, manager_id, 0
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, org.depth + 1
FROM employees e
JOIN org ON e.manager_id = org.id
)
SELECT printf('%.*c%s', depth * 2, ' ', name) AS tree
FROM org;
Burada başlangıç sorgusu en üstteki yöneticiyi (yöneticisi olmayan kişiyi) bulur. Özyinelemeli kısım ise
employees tablosunu CTE ile birleştirerek, manager_id alanı CTE'deki mevcut id değerleriyle eşleşen alt çalışanları bulur. Her adımda hiyerarşinin bir alt katmanına inilir. depth değişkeni ise görsel olarak girintileme yapmak için kullanılan bir sayaçtır.Sorgu, ağaç yapısının derinliği ne olursa olsun (ister 2 ister 10 seviye) aynı şekilde çalışır.
Belirli Bir Kayıttan Yukarıya Doğru Tırmanma
Süreci tersine çevirerek, belirli bir çalışandan başlayıp tüm üst yöneticilerini (atalarını) bulabiliriz:
SQL:
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
manager_id INTEGER REFERENCES employees(id)
);
INSERT INTO employees VALUES
(1, 'Ada', NULL),
(2, 'Boris', 1),
(3, 'Carla', 1),
(4, 'Diego', 2),
(5, 'Elena', 2),
(6, 'Farouk', 3);
WITH RECURSIVE chain(id, name, manager_id) AS (
SELECT id, name, manager_id
FROM employees
WHERE name = 'Diego'
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN chain ON chain.manager_id = e.id
)
SELECT name FROM chain;
Başlangıç noktamız hedef çalışandır. Her özyineleme adımında bir üst yöneticiye geçiş yapılır. SQLite,
manager_id IS NULL durumuna gelip kök kayda ulaştığında join işlemi sonuç döndüremeyeceği için sorgu sona erer.Bu yapı; kategori yolları, iç içe yorum zincirleri veya breadcrumb (ekmek kırıntısı) menüleri oluştururken kullanılır.
Sonsuz Döngüler ve Durdurma Koşulları
Yapılan en yaygın hata, durdurma koşulunu yanlış yazmak veya tamamen unutmaktır. Şu iki örneğe göz atalım:
SQL:
-- Sonsuza dek çalışır:
WITH RECURSIVE bad(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM bad
)
SELECT n FROM bad;
Buradaki örnekte
WHERE koşulu eksik olduğu için sonuç kümesi hiçbir zaman boşalmaz ve SQLite sonsuz bir döngüye girer.Bu riskten korunmak için şu iki yöntemi alışkanlık haline getirin:
1. Özyinelemeli bölümde büyüme sınırını belirleyen bir
WHERE koşulu mutlaka kullanın.2. Geliştirme aşamasında, dış sorguya bir
LIMIT ekleyerek güvenlik önlemi alın.
SQL:
WITH RECURSIVE counter(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM counter
)
SELECT n FROM counter LIMIT 5;
Kodu çalıştırdığınızda, CTE sınırsız olsa bile dıştaki
LIMIT 5 ifadesi işlemi erkenden bitirir. SQLite, LIMIT için gereken miktardan fazlasını hesaplamayacak kadar optimize çalışır. Ancak bu yöntem sadece testler içindir; canlı sistemde gerçek bir durdurma koşulunun yerini tutmaz.Graph Yapılarındaki Döngü Problemi
Ağaç yapılarının aksine, genel graph (çizge) yapılarında döngüler olabilir. Eğer veride bir döngü varsa, standart bir recursive CTE sonsuza kadar döner. Bunu engellemek için gidilen yolun takibi yapılmalıdır:
SQL:
CREATE TABLE edges (a INTEGER, b INTEGER);
INSERT INTO edges VALUES (1, 2), (2, 3), (3, 1), (3, 4);
WITH RECURSIVE walk(node, path) AS (
SELECT 1, ',1,'
UNION ALL
SELECT e.b, path || e.b || ','
FROM edges e
JOIN walk ON e.a = walk.node
WHERE instr(path, ',' || e.b || ',') = 0
)
SELECT node, path FROM walk;
Buradaki
path sütunu, ziyaret edilen düğümleri virgülle ayırarak saklayan bir metindir. WHERE koşulu, yeni eklenecek düğümün daha önce ziyaret edilip edilmediğini kontrol eder. Bu kontrol olmasaydı, örneğin 1 → 2 → 3 → 1 şeklinde bir döngüde sorgu hiç bitmezdi.SQL'de hazır bir "ziyaret edilenler listesi" bulunmadığı için, bunu ya string olarak biriktirerek ya da CTE ile JOIN yaparak kendiniz kurgulamalısınız.
Recursive CTE mi, yoksa Self Join mi?
Eğer sadece bir veya iki seviye derinliğe inecekseniz, self join kullanmak daha performanslı ve basittir:
SQL:
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
manager_id INTEGER REFERENCES employees(id)
);
INSERT INTO employees VALUES
(1, 'Ada', NULL),
(2, 'Boris', 1),
(3, 'Carla', 1),
(4, 'Diego', 2),
(5, 'Elena', 2),
(6, 'Farouk', 3);
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
Bu yöntem "doğrudan yönetici kim" sorusunu yanıtlar. Fakat derinliğin belirsiz olduğu "belirli bir kişiye dolaylı olarak bağlı olan herkes" gibi sorular için tek temiz çözüm recursive CTE'dir. Seçiminizi derinliğe göre yapın:
• Sabit ve sığ derinlikler: self join.
• Değişken veya bilinmeyen derinlikler:
WITH RECURSIVE.Mantıksal Model
SQLite recursive CTE'yi aslında deklaratif bir döngü olarak düşünebilirsiniz:
• Başlangıç sorgusu (Anchor) = Döngünün başlangıç değeri.
• Özyinelemeli sorgu = Döngü gövdesi (mevcut veriden yeni veri üretir).
• Durma koşulu = Döngüden çıkış testi (satır üretimi bittiğinde durur).
•
UNION ALL = Tüm sonuçların toplandığı havuz.Bu bakış açısıyla, aslında SQL içerisinde bir
for döngüsü kurduğunuzu görebilirsiniz.Sıradaki Konu: İndeksleme
Recursive CTE'ler çok sayıda satır üzerinde işlem yapar ve her adımda join çalıştırır. Eğer join yapılan sütunlarda indeks yoksa performans ciddi oranda düşer.
manager_id gibi sütunların indekslenmesi bu noktada kritik önem taşır ve bir sonraki konumuz olan indeksleri detaylandıracaktır.