CTE - Common Table Expressions

CTE Nedir?

CTE, karmaşık sorguları parçalara ayırarak okunabilirliği artırır. Geçici bir "view" gibi düşünebilirsiniz.

Temel CTE

WITH active_users AS (
    SELECT id, username, email, created_at
    FROM users
    WHERE status = 'active'
    AND created_at > NOW() - INTERVAL '1 year'
)
SELECT * FROM active_users 
WHERE email LIKE '%@gmail.com'
ORDER BY created_at DESC;

Çoklu CTE

WITH 
-- Son 12 ayın satışları
monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', created_at) AS month,
        SUM(total_amount) AS total,
        COUNT(*) AS order_count
    FROM orders
    WHERE created_at > NOW() - INTERVAL '12 months'
    GROUP BY DATE_TRUNC('month', created_at)
),
-- Ortalama satış
avg_sales AS (
    SELECT 
        AVG(total) AS avg_monthly,
        AVG(order_count) AS avg_orders
    FROM monthly_sales
)
SELECT 
    TO_CHAR(ms.month, 'YYYY-MM') AS month,
    ms.total,
    ms.order_count,
    ROUND(ms.total - av.avg_monthly, 2) AS diff_from_avg,
    CASE 
        WHEN ms.total > av.avg_monthly THEN '↑'
        ELSE '↓'
    END AS trend
FROM monthly_sales ms
CROSS JOIN avg_sales av
ORDER BY ms.month;

CTE ile UPDATE/DELETE

-- Güncellenecek kayıtları önce belirle
WITH old_orders AS (
    SELECT id FROM orders
    WHERE created_at < NOW() - INTERVAL '2 years'
    AND status = 'completed'
)
UPDATE orders SET archived = TRUE
WHERE id IN (SELECT id FROM old_orders);

-- Silinen kayıt sayısını al
WITH deleted AS (
    DELETE FROM logs
    WHERE created_at < NOW() - INTERVAL '90 days'
    RETURNING id
)
SELECT COUNT(*) AS deleted_count FROM deleted;

Recursive CTE

Hiyerarşik veriler için: organizasyon şeması, kategori ağacı, vb.

Çalışan Hiyerarşisi

WITH RECURSIVE emp_hierarchy AS (
    -- Base case: En üst yönetici (manager_id NULL)
    SELECT 
        id, 
        name, 
        manager_id, 
        title,
        1 AS level,
        name::TEXT AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: Alt çalışanlar
    SELECT 
        e.id, 
        e.name, 
        e.manager_id, 
        e.title,
        eh.level + 1,
        eh.path || ' → ' || e.name
    FROM employees e
    INNER JOIN emp_hierarchy eh ON e.manager_id = eh.id
)
SELECT 
    REPEAT('  ', level - 1) || name AS org_chart,
    title,
    level,
    path
FROM emp_hierarchy
ORDER BY path;

Kategori Ağacı

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, name::TEXT AS full_path, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, ct.full_path || ' > ' || c.name, ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY full_path;