Recursive CTE Nasıl Yazılır: Ağaç Yapılı Veriyi Tek Sorguda Gezmek

Recursive CTE hangi problemi çözer?

Bazı veriler kendi içine referans verir: bir çalışanın yöneticisi de aynı çalışan tablosundadır, bir kategorinin üst kategorisi aynı kategori tablosundadır, bir parçanın alt parçaları aynı parça tablosunda bulunur. Bu yapılara hiyerarşik veri denir. Kaç seviye derinlikte olduğunu önceden bilmediğiniz böyle bir yapıyı, sabit sayıda JOIN ile sorgulamak mümkün değildir: her yeni seviye için sorguya bir birleştirme eklemeniz gerekir.

Recursive CTE (özyinelemeli ortak tablo ifadesi) bu sorunu kendi kendini çağıran bir sorguyla çözer. Sorgu önce başlangıç satırlarını alır, sonra her turda bir önceki turda bulduğu satırlara bağlı yeni satırları ekler ve yeni satır kalmayınca durur. Böylece derinliği ne olursa olsun, ağacın tamamı tek sorguyla gezilir.

Tipik kullanım alanları: organizasyon şeması (bir yöneticinin altındaki tüm çalışanlar), ürün kategorisi ağacı, malzeme listesi patlatma (bir ürünün bütün alt bileşenleri), dosya klasörü yapısı ve ardışık sayı ya da tarih serisi üretmek.

Yapısı: başlangıç, birleşim, özyinelemeli adım

Her recursive CTE belirli parçalardan oluşur. PostgreSQL belgesi yapıyı şöyle tarif eder: özyinelemesiz bir başlangıç terimi, UNION ya da UNION ALL ve CTE'nin kendi çıktısına başvurabilen özyinelemeli terim. Belgeye göre yazım özyinelemeli olsa da çalışma biçimi yinelemelidir: başlangıç satırları çalışma tablosuna konur; çalışma tablosu boş olmadıkça özyinelemeli terim, çalışma tablosunu kendine başvuru yerine koyarak çalıştırılır ve üretilen satırlar sonuca eklenir.

Örnek olarak bir çalışan tablosunda bir yöneticinin altındaki tüm çalışanları listeleyelim:

WITH RECURSIVE ekip AS ( SELECT employee_id, ad, manager_id FROM employees WHERE employee_id = :upper_manager

UNION ALL

SELECT c.employee_id, c.ad, c.manager_id FROM employees c JOIN ekip e ON c.manager_id = e.employee_id ) SELECT * FROM ekip;

İlk SELECT başlangıç noktasıdır: kökten başlamak istediğimiz kişi. İkinci SELECT, o ana kadar bulunan her satır için, yöneticisi o satırdaki kişi olanları getirir. Her turda bir seviye aşağı inilir; kimsenin altında çalışan kalmadığında ikinci SELECT boş döner ve sorgu biter.

SQL Server'da aynı yapı RECURSIVE anahtar sözcüğü olmadan, yalnızca WITH ile yazılır. PostgreSQL, MySQL ve SQLite WITH RECURSIVE biçimini kullanır. Başlangıç ve özyinelemeli SELECT'in sütun sayısı ve tipleri aynı olmalıdır.

Seviye sayısını ve yolu izlemek

Hiyerarşik sorgularda yalnızca satırların kendisi değil, satırın ağaçtaki yeri de istenir. Bu amaçla yardımcı sütunlar eklenir: derinlik sayacı ve yol.

Yol sütunu, kökten o satıra kadar giden adları birleştirir:

WITH RECURSIVE agac AS ( SELECT kategori_id, ad, ust_id, ad AS yol FROM kategoriler WHERE ust_id IS NULL

UNION ALL

SELECT k.kategori_id, k.ad, k.ust_id, a.yol || ' > ' || k.ad FROM kategoriler k JOIN agac a ON k.ust_id = a.kategori_id ) SELECT yol FROM agac ORDER BY yol;

Bu sorgu, kök kategorilerden başlar (ust_id değeri boş olanlar) ve her alt kategori için "Elektronik > Bilgisayar > Dizüstü" gibi bir yol üretir. Metin birleştirme operatörü veritabanına göre değişir: PostgreSQL ve Oracle'da dikey çubuk çifti, SQL Server'da artı işareti ya da CONCAT, MySQL'de CONCAT kullanılır. Yola göre sıralamak, ağacı okunabilir bir girinti sırasına koyar.

Derinlik sayacı için başlangıç SELECT'inde sayacı başlatıp özyinelemeli SELECT'te bir artırırsınız. Derinlik, hem raporda girinti vermek hem de sonsuz döngüyü sınırlamak için işe yarar.

Döngüye karşı koruma

Veri hatalıysa bir kayıt dolaylı olarak kendisinin üstü olabilir: A'nın yöneticisi B, B'nin yöneticisi A. Böyle bir döngüde özyinelemeli sorgu sonsuza kadar çalışır. PostgreSQL belgesi bu riski açıkça anlatır ve döngüden korunmak için yöntemler sıralar.

• UNION ALL yerine UNION kullanmak: yinelenen satırları atar, ancak yalnızca satırın tamamı tekrarlıyorsa döngüyü keser. Derinlik sütunu her turda değiştiği için bu sütunla birlikte işe yaramaz.
• Elle takip: ziyaret edilen düğümlerin yolunu bir dizi ya da metin olarak taşıyıp, zaten yolda olan bir düğümü tekrar genişletmemek.
• Yerleşik CYCLE cümlesi: PostgreSQL belgesine göre aynı mantığı sizin yerinize üretir.

SQL Server'da ayrıca MAXRECURSION seçeneği vardır; özyineleme sayısına üst sınır koyar ve aşıldığında sorgu hatayla durur. Sınırın varsayılan değeri ve ayarı sürüme göre kontrol edilmelidir.

Pratik kural: sorguyu canlı veriye çalıştırmadan önce, veride döngü olup olmadığını bir kez kontrol edin ve her zaman bir derinlik sınırı ya da döngü koruması ekleyin. Bu önlem, sorgunun üretimde sonsuz döngüye girip kaynakları tüketmesini engeller.

Aşağı doğru ve yukarı doğru gezinti

Yukarıdaki örnekler yukarıdan aşağı gider: bir kökten başlayıp altındakileri bulur. Ters yön de mümkündür ve sıkça gerekir: belirli bir çalışandan başlayıp yönetici zincirini genel müdüre kadar çıkarmak. Bu durumda başlangıç SELECT'i çalışanın kendisini alır, özyinelemeli SELECT ise mevcut satırın yöneticisini getirir:

WITH RECURSIVE zincir AS ( SELECT employee_id, ad, manager_id FROM employees WHERE employee_id = :employee

UNION ALL

SELECT c.employee_id, c.ad, c.manager_id FROM employees c JOIN zincir z ON c.employee_id = z.manager_id ) SELECT * FROM zincir;

Fark yalnızca JOIN koşulunun yönündedir. Aşağı inerken "satırın yöneticisi, bulunan kişidir"; yukarı çıkarken "satırın kendisi, bulunan kişinin yöneticisidir". Bu yön ayrımını zihninde netleştirmeyen kullanıcı, boş sonuç ya da beklenmeyen satırlarla karşılaşır.

Malzeme listesi patlatmada da aynı mantık uygulanır: ürün başlangıç satırı, bileşenler özyinelemeli adımda gelir ve her bileşenin miktarı üst ürünle çarpılarak toplam ihtiyaç hesaplanır. Miktar çarpımı özyinelemeli SELECT'te taşınan bir sütunla yapılır.

Performans ve alternatifler

Recursive CTE, ağaç büyüdükçe pahalılaşır; her tur bir önceki turun çıktısına birleştirme yapar. Performansı belirleyen etmenler birleştirmenin yapıldığı sütunda indeks bulunması (burada yönetici ya da üst kimlik sütunu), başlangıç kümesinin darlığı ve derinliktir. Yürütme planında özyinelemeli adımın tablo taraması mı indeks araması mı yaptığına bakmak, ilk teşhis adımıdır.

Hiyerarşi sürekli sorgulanıyorsa ve nadiren değişiyorsa alternatifler düşünülebilir: yol bilgisini tabloda saklayan (materialized path) tasarım, iç içe küme modeli ya da bir kapanım tablosu. Bunlar yazma karmaşıklığını artırır ama okumayı hızlandırır. Oracle gibi bazı sistemlerde hiyerarşik sorgular için CONNECT BY gibi ürüne özgü sözdizimleri de bulunur; taşınabilirlik gerekiyorsa standart recursive CTE tercih edilir.

Küçük ve orta ölçekli hiyerarşilerde recursive CTE yeterlidir. Çok büyük grafiklerde ya da sık sorgulanan hiyerarşilerde tasarım kararı gündeme gelir. Alt sorguların ve CTE'lerin temelleri için SQL alt sorgu ve CTE sayfası, performans teşhisi için SQL performans ayarı sayfası tamamlayıcıdır.

Mindset programlarında bu beceri nerede çalışılır?

Hiyerarşik sorgular, organizasyon şeması, ürün ağacı ve kategori yapıları üzerinde raporlama yapan veri analistleri ve veri mühendisleri için gereklidir. Mindset'in SQL ile Veri Sorgulama (İleri) programında Ortak Tablo İfadeleri (CTE) ve Recursive Sorgular ayrı bir konu başlığıdır; aynı programda alt sorgular, sorgu performansı ve yürütme planı analizi ile pencere fonksiyonları da yer alır.

Program, SQL'in temelini bilen ve ileri sorgulara geçmek isteyen analist ve geliştiriciler için uygundur. Kurumsal ekiplerde örneğin insan kaynakları ya da üretim planlama raporlarını elle birleştiren çalışanlar, kendi organizasyon ya da ürün ağacı verileri üzerinden çalışma yapabilir. Kapsam ayrıntısı program sayfasında yer alır.