Sql & Veritabanı İle Veri Analizi İçin Recursive Sorgu Nasıl Yapılır?

Gereksinimler ve Ön Hazırlık

Bu eğitimde anlatılan teknikleri uygulayabilmek için modern bir RDBMS ortamına ihtiyacınız vardır. Recursive sorgular, ANSI SQL standartlarına uygun olarak WITH RECURSIVE sözdizimi ile tanımlanır. Aşağıdaki araçların yüklü olduğundan emin olun:

• Veritabanı: PostgreSQL 12+, MySQL 8.0.2+, veya SQL Server 2019+.
• SQL Editörü: DBeaver, pgAdmin veya terminal üzerinden erişim.
• Temel Bilgi: JOIN işlemleri ve SELECT ifadeleri hakkında temel yetkinlik.

Çalışma ortamınızın hazır olduğundan emin olmak için veritabanınızın sürümünü SELECT version(); komutuyla kontrol edebilirsiniz.

Temel Yapı: Recursive CTE Nasıl Çalışır?

Recursive sorgular iki ana bölümden oluşur: Anchor Member (Çapa Üye) ve Recursive Member (Özyinelemeli Üye). Çapa üye, sorgunun başlangıç noktasını belirlerken, özyinelemeli üye sonuç kümesi boşalana kadar kendini çağırmaya devam eder.

-- Temel Recursive CTE yapısı
WITH RECURSIVE Hiyerarsi AS (
    -- 1. Çapa Üye: Başlangıç verisi
    SELECT id, isim, ust_id FROM kategoriler WHERE ust_id IS NULL
    UNION ALL
    -- 2. Özyinelemeli Üye: İlişkili verileri bul
    SELECT k.id, k.isim, k.ust_id 
    FROM kategoriler k
    INNER JOIN Hiyerarsi h ON k.ust_id = h.id
)
SELECT * FROM Hiyerarsi;

Bu kodda, önce en üst seviye kategoriler seçilir. Ardından INNER JOIN ile bu kategorilerin altındaki çocuklar bulunur ve sonuç kümesi tamamlanana kadar işlem tekrarlanır.

Adım Adım Uygulama: Organizasyon Şeması Analizi

Bir şirketteki yönetici-çalışan ilişkisini analiz ettiğinizi varsayalım. Her çalışan bir yönetici_id değerine sahiptir. Tüm hiyerarşiyi tek bir sorguda listelemek için şu adımları izleyin.

-- Çalışan hiyerarşisini seviye bazlı listeleme
WITH RECURSIVE CalisanHiyerarsi AS (
    SELECT id, isim, yonetici_id, 1 AS seviye
    FROM calisanlar WHERE yonetici_id IS NULL
    UNION ALL
    SELECT c.id, c.isim, c.yonetici_id, ch.seviye + 1
    FROM calisanlar c
    INNER JOIN CalisanHiyerarsi ch ON c.yonetici_id = ch.id
)
SELECT * FROM CalisanHiyerarsi ORDER BY seviye;

Burada seviye sütunu ekleyerek verinin derinliğini takip ediyoruz. Bu, raporlama süreçlerinde hangi çalışanın hangi hiyerarşik seviyede olduğunu anlamanızı sağlar.

Veri Analizinde Performans ve Optimizasyon

Recursive sorgular, yanlış kurgulandığında sonsuz döngüye girebilir veya bellek tüketimini artırabilir. Büyük veri setlerinde performans kaybını önlemek için şu stratejileri uygulayın:

Sonsuz döngüleri engellemek için WHERE koşullarını veya LIMIT ifadelerini dikkatli kullanmalısınız. Özellikle döngüsel referanslar (A, B'ye bağlı; B, A'ya bağlı) veritabanını kilitleyebilir.

Breadcrumb (İz Sürme) Yöntemi ile Veri Analizi

Veri analizinde, bir öğenin kök dizine kadar olan yolunu görmek (örneğin: Elektronik > Bilgisayar > Laptop) çok yaygındır. Bunu ARRAY fonksiyonları ile kolayca yapabilirsiniz.

-- Kategori yolunu oluşturma
WITH RECURSIVE KategoriYolu AS (
    SELECT id, isim, CAST(isim AS TEXT) AS yol
    FROM kategoriler WHERE ust_id IS NULL
    UNION ALL
    SELECT k.id, k.isim, ky.yol || ' > ' || k.isim
    FROM kategoriler k
    INNER JOIN KategoriYolu ky ON k.ust_id = ky.id
)
SELECT * FROM KategoriYolu;

Bu örnekte || operatörü ile metinleri birleştirerek her satır için tam bir hiyerarşik yol oluşturduk. Bu, kullanıcı arayüzlerinde navigasyon menüleri oluşturmak için idealdir.

Kritik Uyarı: SQL sorgularında kullanıcıdan gelen verileri doğrudan sorguya dahil etmeyin. SQL Injection riskine karşı mutlaka prepared statements (hazırlanmış ifadeler) kullanın. Recursive sorgularda derinlik sınırı (max recursion depth) veritabanı ayarlarında tanımlanmıştır; çok derin ağaçlarda bu sınırı aşmamaya dikkat edin.

Sıkça Sorulan Sorular

Recursive sorgular neden sonsuz döngüye girer?

Eğer verinizde döngüsel bir referans varsa (örneğin A, B'nin üstü; B de A'nın üstü), sorgu çıkış yolu bulamaz. Bunu engellemek için WHERE koşulunda zaten ziyaret edilmiş düğümleri takip eden bir dizi yapısı kullanabilirsiniz.

MySQL ve PostgreSQL arasında fark var mı?

Her iki veritabanı da WITH RECURSIVE sözdizimini destekler. Ancak, PostgreSQL dizi (array) fonksiyonları konusunda daha esnektir, MySQL ise performans optimizasyonu için daha katı kurallar gerektirebilir.

Recursive sorgular ne zaman kullanılmamalıdır?

Eğer hiyerarşik veriniz çok nadir değişiyorsa ve sürekli okunuyorsa, veriyi düz (flat) bir tabloda tutmak veya "Nested Set" modelini kullanmak daha performanslı olabilir.

Performansı nasıl ölçerim?

Sorgunuzun başına EXPLAIN ANALYZE ekleyerek veritabanının sorguyu nasıl işlediğini, hangi indeksleri kullandığını ve ne kadar süre harcadığını görebilirsiniz.

Recursive sorgularla toplam hesaplanabilir mi?

Evet, her adımda SUM() fonksiyonu ile kümülatif toplamlar veya hiyerarşik ağaçtaki toplam değerleri kolayca hesaplayabilirsiniz.

İleri Seviye Senaryo: Döngüsel Veri (Circular Reference) Tespiti ve Yönetimi

Recursive sorgularla çalışırken en büyük risklerden biri, veritabanı şemasında yanlışlıkla oluşturulan döngüsel referanslardır. Örneğin, bir çalışanın yöneticisinin yine kendisi veya astlarından biri olarak tanımlanması, sorgunun sonsuz döngüye girmesine ve sistem kaynaklarının tükenmesine neden olur. Bu durumu yönetmek için CYCLE anahtar kelimesini veya manuel izleme yöntemlerini kullanmalıyız.

Aşağıdaki örnekte, bir döngü tespit edildiğinde sorgunun nasıl güvenli bir şekilde durdurulacağını ve hata mesajı üreteceğini görebilirsiniz:

WITH RECURSIVE Hiyerarsi_Analizi AS (
    -- Başlangıç noktası
    SELECT id, yonetici_id, isim, ARRAY[id] AS yol, false AS dongu_var
    FROM Calisanlar WHERE id = 1
    
    UNION ALL
    
    SELECT c.id, c.yonetici_id, c.isim, h.yol || c.id, c.id = ANY(h.yol)
    FROM Calisanlar c
    JOIN Hiyerarsi_Analizi h ON c.yonetici_id = h.id
    WHERE NOT h.dongu_var
)
SELECT * FROM Hiyerarsi_Analizi WHERE dongu_var = false;

Bu yaklaşım, yol adında bir dizi (array) oluşturarak her adımda ziyaret edilen düğümleri kaydeder. Eğer bir düğüm zaten yolda mevcutsa, dongu_var değeri true olur ve rekürsiyon güvenli bir şekilde sonlandırılır.

Recursive Sorgular İçin Birim Testi ve Doğrulama Stratejileri

Karmaşık hiyerarşik sorguların doğruluğunu garanti altına almak için sadece sonuç kümesine bakmak yeterli değildir. Özellikle derinlik (depth) ve düğüm sayısı (node count) gibi metrikleri test etmek, veri bütünlüğünü korumak için kritiktir. Aşağıdaki SQL bloğu, bir ağaç yapısının derinliğini doğrulamak için kullanılan bir test şablonudur:

-- Ağaç derinliğini ve düğüm sayısını doğrulayan test sorgusu
WITH RECURSIVE Test_Agaci AS (
    SELECT id, 1 AS derinlik
    FROM Kategoriler WHERE ust_kategori_id IS NULL
    
    UNION ALL
    
    SELECT k.id, ta.derinlik + 1
    FROM Kategoriler k
    JOIN Test_Agaci ta ON k.ust_kategori_id = ta.id
)
SELECT 
    MAX(derinlik) AS toplam_derinlik, 
    COUNT(*) AS toplam_dugum_sayisi
FROM Test_Agaci;

Bu test sorgusunu, veritabanı taşıma (migration) süreçlerinizde veya CI/CD süreçlerinizde bir "sanity check" olarak kullanabilirsiniz. Eğer beklenen derinlikten daha fazla veya az sonuç dönüyorsa, verilerinizde kopuk bir hiyerarşi veya beklenmedik bir dallanma olduğunu anlayabilirsiniz.

İleri İpuçları: Sorgu Hızlandırma Teknikleri

• İndeksleme: Recursive sorgularda JOIN yapılan sütunlara (örneğin yonetici_id veya ust_kategori_id) mutlaka B-Tree indeksi ekleyin.
• Sınırlandırma: LIMIT ifadesini recursive sorgunun en dış katmanında kullanarak, test aşamalarında tüm ağacın taranmasını engelleyin.
• Malzeme Görünümler (Materialized Views): Eğer hiyerarşi çok sık değişmiyorsa, recursive sorguyu her seferinde çalıştırmak yerine sonucu bir MATERIALIZED VIEW içinde saklayın ve belirli aralıklarla güncelleyin.

Sonuç

SQL & Veritabanı ile veri analizi için recursive sorgu yapısı, karmaşık ilişkileri anlamlandırmak için en güçlü araçlardan biridir. Bu rehberde, temel CTE yapısından başlayarak, hiyerarşik yollar oluşturmaya ve performans optimizasyonuna kadar kritik noktaları ele aldık. Bir sonraki adım olarak, kendi veritabanınızda EXPLAIN ANALYZE kullanarak mevcut sorgularınızın çalışma planlarını incelemeyi ve indeksleme stratejileriyle hızlarını artırmayı deneyebilirsiniz.

Sorumluluk Reddi: Bu makaledeki kod örnekleri eğitim amaçlıdır. Üretim ortamında (production) çalıştırmadan önce mutlaka yedek alınız ve güvenlik testlerini gerçekleştiriniz. SQL Injection ve yetkilendirme kontrolleri tamamen geliştiricinin sorumluluğundadır.