Özyinelemeli CTE (recursive CTE, WITH RECURSIVE)

Kendini çağıran sorgu

Özyinelemeli CTE iki parçadan oluşur: çapa (başlangıç satırları) ve özyinelemeli kısım (bir önceki adımın sonucunu kullanır). UNION ALL ile birleştirilir ve yeni satır üretilmeyene kadar devam eder.

Sayı dizisi üretmek

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

Tarih iskeleti

Hiç sipariş olmayan günleri de raporda göstermek için:

WITH RECURSIVE gunler(gun) AS (
  SELECT '2024-12-01'
  UNION ALL
  SELECT date(gun, '+1 day') FROM gunler WHERE gun < '2024-12-31'
)
SELECT g.gun, COUNT(o.order_id) AS siparis
FROM gunler g
LEFT JOIN orders o ON o.order_date = g.gun
GROUP BY g.gun;

Organizasyon şeması

WITH RECURSIVE org AS (
  SELECT emp_id, first_name, manager_id, 0 AS seviye
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.first_name, e.manager_id, org.seviye + 1
  FROM employees e
  JOIN org ON e.manager_id = org.emp_id
)
SELECT * FROM org ORDER BY seviye, emp_id;

Derinleş: özyinelemenin iki kullanımı

Özyinelemeli CTE ya üretmek (sayı, tarih dizisi) ya da gezmek (hiyerarşi, ağ) için kullanılır. Gezme örneği: bir müşterinin davet ettiği herkes, her kuşağıyla:

WITH RECURSIVE zincir(customer_id, kusak) AS (
  SELECT customer_id, 1 FROM lk_customers WHERE referred_by = 214
  UNION ALL
  SELECT c.customer_id, z.kusak + 1
  FROM lk_customers c JOIN zincir z ON c.referred_by = z.customer_id
)
SELECT kusak, COUNT(*) AS kisi FROM zincir GROUP BY kusak;

Döngü olabilecek verilerde (A, B'yi; B, A'yı davet etmiş gibi) bir derinlik sınırı ekle: WHERE z.kusak < 20.