Çalıştırılabilir SQLite Dokümanları: Recursive CTE

Q: SQLite'ta recursive CTE nedir?

Recursive CTE, kendisine tekrar tekrar başvurarak sonuç kümesi üreten bir WITH RECURSIVE sorgusudur. İki parçası vardır ve bunlar UNION ALL ile birleştirilir: başlangıç satırlarını üreten anchor sorgusu ve bir önceki adımdan yeni satırlar türeten recursive sorgu. SQLite, recursive kısım yeni satır üretmeyi bırakana kadar sorguyu çalıştırmaya devam eder.

Q: WITH RECURSIVE'i ne zaman kullanmalıyım?

Bir ağaç ya da graf üzerinde gezmen gerektiğinde (çalışan-yönetici, kategori-alt kategori, iç içe yorumlar) veya bir dizi üretmen gerektiğinde (bir aralıktaki tüm tarihler, 1'den 100'e kadar sayılar) tam aradığın şey budur. Düz JOIN'ler bir-iki seviyeye kadar iş görür; ama derinliği önceden bilmediğin durumlar için recursive CTE en temiz çözüm.

Q: Recursive CTE'de sonsuz döngüye nasıl düşmem?

Recursive sorgunun mutlaka bir durma koşulu olmalı: ya sonunda sıfır satır döndürecek bir WHERE ifadesi ya da üst sınırını koyduğun bir sayaç. Döngü içerebilen graf yapılarında gezdiğin yolu bir kolonda tutup oradaki düğümleri tekrar ziyaret etmemen gerekir. Güvenlik ağı olarak dış sorguya bir LIMIT koy; böylece kontrolden çıkan bir recursion belleği doldurmadan durur.

SQL'de Recursion Tuhaf Geliyor — Ta ki Görene Kadar

Çoğu sorgu, zaten var olan veriden satır döndürür. SQLite recursive CTE ise farklı çalışır: kendi ürettiği çıktıyı tekrar girdi olarak besler ve eklenecek yeni bir şey kalmayana dek adım adım satır üretir. Derinliği belli olmayan bir ağaçta gezinmek ya da elinizde sayı tablosu olmadan 1'den 100'e kadar sayı üretmek işte tam böyle yapılır.

Kalıp her zaman aynıdır:

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;

En üstte anchor (başlangıç) sorgusu, ardından UNION ALL, en altta da recursive (özyinelemeli) sorgu yer alır. SQLite önce anchor sorgusunu bir kez çalıştırır; sonra recursive kısmı tekrar tekrar işletir — her seferinde bir önceki adımda üretilen satırları girdi olarak kullanır — ve yeni satır gelmediği anda durur.

1'den 10'a Kadar Sayma

En basit sqlite recursive CTE örneği bir sayı serisi üretir. Üstelik hiçbir tabloya bile ihtiyacın yok:

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

Adım adım takip edelim:

Anchor (başlangıç) tek bir satır üretir: n = 1.
Recursive (özyinelemeli) adım bu satırı alır, n + 1 = 2 hesaplar ve 2 < 10 doğru olduğundan satırı tutar.
Sonraki iterasyon n = 2 değerini alıp n = 3 üretir. Ve bu böyle devam eder.
n, 10 değerine ulaştığında 10 < 10 artık yanlıştır; özyinelemeli adım hiç satır döndürmez ve SQLite durur.

Buradaki WHERE n < 10 koşulu, durma koşuludur. Bu olmazsa sorgu sonsuza kadar çalışır.

SQLite ile tarih serisi üretme

Aynı mantık gerçek raporlarda da çok işe yarar — bir tarih aralığındaki her günü, hiçbir şey olmayan günler dahil, tek tek doldurmak için kullanılır:

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;

Bunu genelde bir olay (events) tablosuyla LEFT JOIN ederek olaysız günleri doğru şekilde sayarsınız. Düz bir GROUP BY date boş günleri tamamen atlar; tarih serisi ise her gün için size bir satır verir, olay olsa da olmasa da.

SQLite ile parent-child ağaç yapısında gezinmek

Klasik kullanım senaryosu. Aşağıda her satırın kendi yöneticisini işaret ettiği bir çalışan tablosu var:

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;

Anchor kısmı kökü seçiyor (yöneticisi olmayan kişiyi). Özyinelemeli adım ise employees tablosunu CTE ile join ederek manager_id değeri CTE'de zaten bulunan bir id ile eşleşen herkesi buluyor. Her iterasyon bir kademe daha derine iniyor. depth ise çıktıyı girintilemek için eklediğimiz basit bir sayaç.

Bu sorgu istediğiniz derinlikteki ağaç yapısı için çalışır. İki seviye olmuş, on seviye olmuş fark etmez; sorgu aynı kalır.

Belirli Bir Kaydın Tüm Atalarını Bulma

Şimdi yönü ters çevirelim. Kökten aşağıya inmek yerine, belirli bir çalışandan yukarıya doğru yürüyüp o kişinin tüm yönetici zincirini bulalım:

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;

Çıkış noktamız (anchor) başlangıçtaki çalışandır. Her özyinelemeli adımda bir üst yöneticiye sıçrarız. SQLite kök kayda ulaşınca durur — yani manager_id IS NULL olduğunda join hiçbir şey bulamaz ve sorgu sonlanır.

Bu desen; ekmek kırıntısı (breadcrumb) menüleri, iç içe yorumlar, kategori yolları ve kısacası "yukarıya doğru tırman" mantığının geçtiği her yerde işe yarar.

Durdurma Koşulları ve Sonsuz Döngüler

En sık karşılaşılan hata, durdurma koşulunu unutmak ya da hiç tetiklenmeyecek bir koşul yazmaktır. Şu ikisini karşılaştıralım:

-- Sonsuza dek çalışır:
WITH RECURSIVE bad(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM bad
)
SELECT n FROM bad;

WHERE koşulu olmadığı için sonucu sıfır satıra düşürecek bir koşul da yok. SQLite de hiç sıkılmadan sonsuza kadar saymaya çalışır.

Kendinizi korumak için iki alışkanlık edinin:

Recursive kısımda büyümeyi sınırlayan bir WHERE koşulu mutlaka bulunsun.
Geliştirme aşamasında dış SELECT'e bir LIMIT ekleyin; bu sizin güvenlik ağınız olsun. Durdurma koşulunda bir hata yapmış olsanız bile sorgu yine de sonlanır.

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter
)
SELECT n FROM counter LIMIT 5;

CTE'nin kendisi sınırsız, ama LIMIT 5 dış sorguyu erkenden durduruyor. SQLite, LIMIT'in ihtiyacından fazlasını rekürsif olarak hesaplamayacak kadar akıllı. Keşif amaçlı işe yarar; ancak production kodunda gerçek bir durdurma koşulunun yerine geçmez.

Graph'larda Döngüler

Ağaçlarda döngü olmaz. Ama genel graph'larda olabilir — ve veride bir döngü varsa, naif yazılmış bir recursive CTE sonsuza kadar döner. Çözüm: o ana kadar yürüdüğünüz yolu takip etmek ve daha önce uğradığınız node'lara tekrar girmemek:

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;

path aslında o ana kadar uğranan node'ları virgülle ayrılmış şekilde tutan bir metin. Yeni bir node eklemeden önce WHERE koşulu, o node'un listede olup olmadığını kontrol ediyor. Bu koruma olmasaydı, 1 → 2 → 3 → 1 döngüsü sonsuza kadar dönerdi.

SQL'de hazır bir "ziyaret edilenler kümesi" yok — bunu kendin kurguluyorsunuz: ya bir string olarak biriktiriyorsunuz ya da o ana kadar oluşan CTE ile JOIN yaparak.

Recursive CTE mi, Self Join mi?

Sadece bir-iki seviye derine inecekseniz self join hem daha basit hem de daha hızlıdır:

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, "her bir kişinin doğrudan yöneticisi kim" sorusunu çözer. Ama iş "Ada'ya, kaç kademe üzerinden olursa olsun, dolaylı bağlı olan herkes" sorusuna gelince — derinlik belli değildir — bunu temiz bir şekilde yalnızca recursive CTE çözer. Aracı, ihtiyacın olan derinliğe göre seç:

Sabit ve küçük derinlik: self join, belki iki ya da üç tane.
Belirsiz veya değişken derinlik: WITH RECURSIVE.

Zihinsel Model

SQLite recursive CTE, aslında deklaratif şekilde yazılmış bir döngüdür:

Anchor (başlangıç sorgusu), döngünün ilk değeridir.
Recursive sorgu, döngünün gövdesidir — mevcut satırlardan yeni satır kümesini üretir.
Durma koşulu, döngünün çıkış testidir — sıfır satır döndürdüğünde döngü biter.
UNION ALL ise her şeyi nihai sonuç kümesinde biriktirir.

Bu eşleşme kafanızda oturduğunda, sözdizimi artık tuhaf gelmemeye başlar. Aslında SQL içinde bir for döngüsü yazıyorsunuz.

Sırada: İndeksler

Recursive CTE'ler bol miktarda satır üzerinde gezinir ve recursive adımdaki join her iterasyonda yeniden çalışır. Join'de kullanılan sütun indeksli değilse, performans uçurumdan düşer. İndeksler bir sonraki bölümün konusu ve manager_id tam da indeksten en çok fayda görecek türden bir sütun.

Sıkça Sorulan Sorular

SQLite'ta recursive CTE nedir?

Recursive CTE, kendisine tekrar tekrar başvurarak sonuç kümesi üreten bir WITH RECURSIVE sorgusudur. İki parçası vardır ve bunlar UNION ALL ile birleştirilir: başlangıç satırlarını üreten anchor sorgusu ve bir önceki adımdan yeni satırlar türeten recursive sorgu. SQLite, recursive kısım yeni satır üretmeyi bırakana kadar sorguyu çalıştırmaya devam eder.

WITH RECURSIVE'i ne zaman kullanmalıyım?

Bir ağaç ya da graf üzerinde gezmen gerektiğinde (çalışan-yönetici, kategori-alt kategori, iç içe yorumlar) veya bir dizi üretmen gerektiğinde (bir aralıktaki tüm tarihler, 1'den 100'e kadar sayılar) tam aradığın şey budur. Düz JOIN'ler bir-iki seviyeye kadar iş görür; ama derinliği önceden bilmediğin durumlar için recursive CTE en temiz çözüm.

Recursive CTE'de sonsuz döngüye nasıl düşmem?

Recursive sorgunun mutlaka bir durma koşulu olmalı: ya sonunda sıfır satır döndürecek bir WHERE ifadesi ya da üst sınırını koyduğun bir sayaç. Döngü içerebilen graf yapılarında gezdiğin yolu bir kolonda tutup oradaki düğümleri tekrar ziyaret etmemen gerekir. Güvenlik ağı olarak dış sorguya bir LIMIT koy; böylece kontrolden çıkan bir recursion belleği doldurmadan durur.

İlgili kavramlar

SQLite Recursive CTE: WITH RECURSIVE Kullanımı