Rekursiv CTE-lər: nədir və nə üçün lazımdır

Bu gün bir addım da irəli gedəcəyik və rekursiyanın magiyasına baxacağıq. Əgər əvvəllər rekursiyanı dəstəkləyən bir dildə (məsələn, Python) proqramlaşdırmısansa, təxminən nə baş verdiyini bilirsən. Amma narahat olma, əgər bu sənə bir az mistik gəlirsə — hər şeyi çox detallı izah edəcəyik.

Rekursiv CTE-lər — iyerarxik, ağac tipli məlumat strukturları ilə işləmək üçün güclü bir vasitədir, məsələn, şirkətlərin təşkilati strukturları, ailə ağacı və ya fayl kataloqları kimi.

Sadə dillə desək, bunlar elə ifadələrdir ki, "özlərini çağırıb" məlumatların bütün səviyyələrini tədricən gəzməyə və emal etməyə imkan verir.

Rekursiv CTE-lərin əsas xüsusiyyətləri:

1. Onlar WITH RECURSIVE açar sözündən istifadə edir.
2. Rekursiv CTE-lər iki hissədən ibarətdir:
   • Baza sorğusu: rekursiyanın başlanğıc nöqtəsini (və ya "kökünü") müəyyən edir.
   • Rekursiv sorğu: əvvəlki addımın nəticəsini istifadə edərək qalan məlumatları emal edir.

Rekursiv CTE-nin işləmə alqoritmi pilləkənlə qalxmağa bənzəyir:

• Əvvəlcə birinci pilləyə çıxırsan (bu baza sorğusudur).
• Sonra birinci pillənin nəticəsini istifadə edərək ikinci pilləyə qalxırsan (rekursiv sorğu).
• Bu proses pillələr bitənə qədər (bitmə şərtinə çatana qədər) təkrarlanır.

Rekursiv CTE-nin sintaksisi

Gəlin dərhal şablon bir nümunəyə baxaq:

WITH RECURSIVE cte_name AS (
    -- Baza sorğusu
    SELECT column1, column2
    FROM table_name
    WHERE condition_for_base_case

    UNION ALL

    -- Rekursiv sorğu
    SELECT column1, column2
    FROM table_name
    JOIN cte_name ON some_condition
    WHERE stop_condition
)
SELECT * FROM cte_name;

Rekursiv CTE-lərdə UNION və UNION ALL-ın rolu

Hər bir rekursiv CTE mütləq baza və rekursiv hissə arasında UNION və ya UNION ALL operatorlarından istifadə etməlidir.

Hansını seçmək: UNION yoxsa UNION ALL?

Əgər əmin deyilsənsə, hansını istifadə edəsən — demək olar ki, həmişə UNION ALL seç. Niyə? Çünki o, daha sürətli işləyir: sadəcə nəticələri birləşdirir, təkrar olub-olmadığını yoxlamır. Yəni — daha az hesablama, daha az resurs və daha tez nəticə.

Xüsusilə rekursiv CTE-lərdə bu vacibdir. Məsələn, şərh ağacı və ya şirkətdə tabeçilik strukturu quranda — UNION ALL demək olar ki, həmişə lazımdır. Sadəcə UNION istifadə etsən, database təsadüfən hansısa addımların artıq olduğunu düşünüb nəticənin bir hissəsini "kəsə" bilər. Bu isə bütün gəzmə məntiqini pozar.

UNION yalnız o halda istifadə oluna bilər ki, dəqiq bilirsən, təkrarlar zərərlidir və onları silmək lazımdır. Amma unutma: bu həmişə təmizlik və sürət arasında kompromisdir.

Fərqli yanaşmaların nümunəsi

-- UNION: təkrarlar çıxarılır
SELECT 'A'
UNION
SELECT 'A';     -- Nəticə: bir sətr 'A'

-- UNION ALL: təkrarlar saxlanılır
SELECT 'A'
UNION ALL
SELECT 'A';     -- Nəticə: iki sətr 'A'

Rekursiv sorğularda strukturu gəzərkən vacib addımları itirməmək üçün həmişə UNION ALL istifadə etmək daha təhlükəsizdir.

Tipik bir tapşırığa baxaq: bizdə employee_id, manager_id və name sütunları olan işçilər cədvəli var. İyerarxiyanı direktor — yəni rəhbəri olmayan şəxs (onda manager_id = NULL) ilə başlamaq lazımdır.

Tutaq ki, bizdə işçilər cədvəli var: employees

Bizə lazımdır başa düşmək, kim kimə tabedir və hər işçinin strukturdakı səviyyəsini öyrənmək. Bu, məsələn, işçilərin ağacını interfeysə çıxarmaq və ya komanda strukturu barədə hesabat hazırlamaq üçün rahatdır.

WITH RECURSIVE employee_hierarchy AS (
    -- Rəhbəri olmayanlardan başlayırıq
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Tabe olanları əlavə edirik və səviyyəni artırırıq
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Nəticə belə olacaq:

Bu sorğu açıq şəkildə göstərir ki, işçilərin iyerarxiyasını necə "gəzmək" olar — direktordan ən aşağı səviyyəyə qədər. Level səviyyəsi ağacın formatlanması və ya vizuallaşdırılması üçün rahatdır.

Nümunə: məhsul kateqoriyaları

İndi təsəvvür et ki, biz məhsul kateqoriyaları cədvəli ilə işləyirik, burada hər bir kateqoriya alt-kateqoriyalara sahib ola bilər, onlar isə öz növbəsində başqa alt-kateqoriyalara sahib ola bilər. Kateqoriyalar ağacını necə qurmaq olar?

categories cədvəli

Rekursiv sorğu:

WITH RECURSIVE category_tree AS (
    -- Baza hal: kök kateqoriyaları tapırıq
    SELECT
        category_id,
        name,
        parent_id,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Rekursiv hissə: cari kateqoriyaların alt-kateqoriyalarını tapırıq
    SELECT
        c.category_id,
        c.name,
        c.parent_id,
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

Nəticə:

İndi biz kateqoriyaların ağacını və dərinlik səviyyələrini görürük.

Niyə rekursiv CTE-lər — superdir?

Rekursiv CTE-lər — SQL-in ən ifadəli və güclü alətlərindən biridir. Çətin iç-içə məntiq əvəzinə sadəcə haradan başlamağı (baza halı) və necə davam etməyi (rekursiv hissə) təsvir edirsən — qalan hər şeyi PostgreSQL özü həll edir.

Ən çox belə sorğular iyerarxiyaları gəzmək üçün istifadə olunur: işçilər, məhsul kateqoriyaları, diskdəki qovluqlar, sosial şəbəkələrdə qraflar. Onlar asanlıqla genişlənir: cədvələ yeni məlumatlar əlavə olunsa, sorğu onları özü götürəcək. Bu rahat və miqyaslana biləndir.

Amma bəzi tələlər də var. Bitmə şərtlərinə mütləq fikir ver — onsuz sorğu sonsuz dövrə düşə bilər. İndeksləri unutma: böyük cədvəllərdə rekursiv sorğular indeks olmadan ləngiyə bilər. Və UNION ALL — demək olar ki, həmişə ən yaxşı seçimdir, xüsusilə iyerarxik tapşırıqlarda, yoxsa təkrarların silinməsi səbəbindən rekursiyanın addımlarını itirə bilərsən.

Yaxşı qurulmuş rekursiv CTE mürəkkəb biznes məntiqini bir neçə sətrdə ifadə etməyə imkan verir — prosedurlar, dövrlər və əlavə kod olmadan. Bu o haldır ki, SQL təkcə düzgün yox, həm də gözəl işləyir.

Rekursiv CTE-lərlə işləyərkən tipik səhvlər

• Sonsuz rekursiya: düzgün bitmə şərti (WHERE) qoymasan, sorğu dövrə düşə bilər.
• Artıq məlumatlar: UNION ALL-dan səhv istifadə təkrarlar əlavə edir.
• Performans: rekursiv sorğular böyük həcmdə məlumat üçün ağır ola bilər. Açar sütunlarda (məsələn, manager_id) indekslər performansı artıracaq.

Rekursiv sorğusuz keçinmək mümkün olmayan hallar

Bəzən elə gəlir ki, rekursiv sorğular — sırf nəzəriyyə üçündür, amma əslində onlar gündəlik proqramlaşdırmada tez-tez rast gəlinir. Məsələn:

• şirkət strukturu və ya məhsul təsnifatı üzrə hesabatlar qurmaq üçün;
• qovluq ağacını gəzmək və bütün daxili qovluqların siyahısını toplamaq üçün;
• qrafları analiz etmək — sosial əlaqələr, marşrutlar, tapşırıqlar arasında asılılıqlar üçün;
• sadəcə mürəkkəb obyektlər arasındakı əlaqələri oxunaqlı şəkildə göstərmək üçün.

Əgər bir strukturu gəzmək lazımdırsa və orada bir şey digərindən asılıdırsa — demək olar ki, mütləq WITH RECURSIVE köməyə gələcək.