CTE və alt-sorğular: hansını nə vaxt seçmək lazımdır?

Artıq bilirik ki, CTE kodu daha oxunaqlı edir. Amma həmişə CTE istifadə etmək lazımdır? Bəzən sadə bir alt-sorğu daha yaxşı və sürətli işləyir. Gəlin baxaq, hansı halda hansı alət daha sərfəlidir və necə düzgün seçim etmək olar.

Alt-sorğular: tez və rahat

Yəqin yadındadır, alt-sorğu — SQL-in içində SQL-dir. Birbaşa əsas sorğunun içinə yerləşir və "yerində" icra olunur. Sadə, bir dəfəlik əməliyyatlar üçün əladır:

-- Orta qiymətdən baha olan məhsulları tap
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Burada alt-sorğu orta qiyməti bir dəfə hesablayır və məsələ bitir. Artıq heç bir konstruksiya yoxdur.

Performans: kim daha sürətlidir?

Alt-sorğular sadə əməliyyatlarda tez-tez sürət baxımından qalib gəlir. PostgreSQL onları "uçanda" optimallaşdıra bilir, xüsusən də alt-sorğu bir dəfə icra olunursa:

-- Tez: alt-sorğu bir dəfə işləyəcək
SELECT customer_id, order_total
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

CTE default olaraq materializasiya olunur — PostgreSQL əvvəlcə CTE-nin nəticəsini hesablayır, onu müvəqqəti cədvəl kimi saxlayır, sonra istifadə edir. Bu, sadə sorğuları ləngidə bilər:

-- Daha yavaş: CTE müvəqqəti cədvələ yazılır
WITH latest_date AS (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

Amma! PostgreSQL 12-dən başlayaraq materializasiyanı idarə etmək olur:

-- Məcburi olaraq materializasiya ETMƏ
WITH latest_date AS NOT MATERIALIZED (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

Bir neçə dəfə istifadə: burada CTE kraldır

Eyni aralıq nəticə bir neçə dəfə lazım olanda, CTE əvəzolunmaz olur:

-- Alt-sorğu ilə: eyni məntiqi iki dəfə təkrarlayırıq
SELECT
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') AS tamamlanmış_sifarişlər,
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') * 100.0 / COUNT(*) AS tamamlanma_faizi
FROM orders;

-- CTE ilə: bir dəfə hesablayırıq, iki dəfə istifadə edirik
WITH completed_orders AS (
    SELECT COUNT(*) AS count FROM orders WHERE status = 'completed'
)
SELECT
    co.count AS tamamlanmış_sifarişlər,
    co.count * 100.0 / (SELECT COUNT(*) FROM orders) AS tamamlanma_faizi
FROM completed_orders co;

Çətin analitika: CTE xalları yığır

Çoxmərhələli analitika üçün CTE xaosu qaydaya çevirir. Satış hesabatını müqayisə et:

Alt-sorğularla (beyində xaos):

SELECT 
    category,
    revenue,
    revenue * 100.0 / (
        SELECT SUM(p.price * oi.quantity)
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        JOIN orders o ON oi.order_id = o.order_id
        WHERE EXTRACT(year FROM o.order_date) = 2024
    ) AS gəlir_payı
FROM (
    SELECT 
        p.category,
        SUM(p.price * oi.quantity) AS revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
    GROUP BY p.category
) category_revenue;

CTE ilə (hər şey qaydasında):

WITH yearly_sales AS (
    SELECT 
        p.category,
        p.price * oi.quantity AS sale_amount
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
),
category_revenue AS (
    SELECT 
        category,
        SUM(sale_amount) AS revenue
    FROM yearly_sales
    GROUP BY category
),
total_revenue AS (
    SELECT SUM(sale_amount) AS total FROM yearly_sales
)
SELECT 
    cr.category,
    cr.revenue,
    cr.revenue * 100.0 / tr.total AS gəlir_payı
FROM category_revenue cr, total_revenue tr;

Rekursiya: CTE-nin monopoliyası

İerarxik strukturlar üçün alt-sorğular acizdir.

Yalnız rekursiv CTE-lər "menecerin bütün tabeçiliyində olanları tap" kimi tapşırıqları həll edə bilir:

WITH RECURSIVE employee_hierarchy AS (
    -- Başlayırıq CEO-dan
    SELECT employee_id, manager_id, name, 1 AS səviyyə
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Hər səviyyənin tabeçiliyində olanları əlavə edirik
    SELECT e.employee_id, e.manager_id, e.name, eh.səviyyə + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy ORDER BY səviyyə, name;

Debug və kodun dəstəklənməsi

CTE hissə-hissə rahat debug oluna bilir:

-- Birinci mərhələni yoxlayırıq
WITH active_customers AS (
    SELECT customer_id FROM customers WHERE status = 'aktiv'
)
SELECT COUNT(*) FROM active_customers; -- Məntiqin düzgün olduğuna əmin oluruq

-- İkinci mərhələni əlavə edirik
WITH active_customers AS (...),
recent_orders AS (
    SELECT customer_id, COUNT(*) as sifariş_sayı
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
)
SELECT COUNT(*) FROM recent_orders; -- Bunu da yoxlayırıq

Alt-sorğuları debug etmək daha çətindir — onları kontekstdən çıxarmaq lazımdır.

Praktiki tövsiyələr

Alt-sorğulardan istifadə et:

• Məntiq sadədirsə və bir sətrə sığırsa
• Sadə əməliyyatlarda maksimum performans lazımdırsa
• Aralıq nəticə yalnız bir dəfə istifadə olunursa
• Az miqdarda data ilə işləyirsənsə

CTE istifadə et:

• Sorğu mürəkkəbdirsə və məntiqi mərhələlərə bölünürsə
• Aralıq nəticələri bir neçə dəfə istifadə etmək lazımdırsa
• Kodun oxunaqlılığı və dəstəklənməsi vacibdirsə
• İerarxiyalarla işləyirsənsə (rekursiv CTE)
• Mürəkkəb məntiqi hissə-hissə debug edirsənsə

Qızıl qayda

Əvvəlcə alt-sorğu ilə başla. Əgər oxumaq çətinləşirsə və ya məntiq təkrarlanırsa — CTE-yə keç. Gələcəkdə həmkarın (ya da özün altı ay sonra) sənə təşəkkür edəcək!