İyerarxiyalarla işləmək üçün rekursiv CTE nümunəsi

Təsəvvür elə: sənin onlayn mağazanda minlərlə məhsul var, hamısı səliqəli şəkildə rəflərə düzülüb — kateqoriyalar, alt-kateqoriyalar, alt-alt-kateqoriyalar. Saytda bu, gözəl açılan menyu kimi görünür, amma database-də başağrısına çevrilir. "Elektronika → Smartfonlar → Aksesuarlar" budağını bir sorğu ilə necə çıxardaq? Hər kateqoriyanın neçə səviyyə iç-içəliyi var, necə sayaq? Sadə JOIN-lar burda kömək eləmir — rekursiya lazımdır!

Rekursiv CTE ilə məhsul kateqoriyaları strukturunun qurulması

Relyasiya bazalarında klassik məsələlərdən biri — iyerarxik strukturlarla işləməkdir. Təsəvvür elə, səndə məhsul kateqoriyaları ağacı var: əsas kateqoriyalar, alt-kateqoriyalar, alt-alt-kateqoriyalar və s. Məsələn:

Elektronika
  └── Smartfonlar
      └── Aksesuarlar
  └── Noutbuklar
      └── Qeymerlər üçün
  └── Foto və video

Bu struktur onlayn mağazaların interfeysində asanlıqla göstərilir, bəs database-də necə saxlamaq və çıxarmaq olar? Burda köməyə rekursiv CTE gəlir!

Kateqoriyaların ilkin cədvəli

Əvvəlcə categories cədvəlini yaradaq, burada məhsul kateqoriyaları haqqında məlumat saxlanacaq:

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- Kateqoriyanın unikal ID-si
    category_name TEXT NOT NULL,          -- Kateqoriyanın adı
    parent_category_id INT                -- Valideyn kateqoriya (əsaslar üçün NULL)
);

Cədvələ əlavə edəcəyimiz nümunə məlumatlar belədir:

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('Elektronika', NULL),
    ('Smartfonlar', 1),
    ('Aksesuarlar', 2),
    ('Noutbuklar', 1),
    ('Qeymerlər üçün', 4),
    ('Foto və video', 1);

Burda nə baş verir:

• Elektronika — əsas kateqoriyadır (valideyni yoxdur, parent_category_id = NULL).
• Smartfonlar Elektronika kateqoriyasının içindədir.
• Aksesuarlar Smartfonlar kateqoriyasına aiddir.
• Qalan kateqoriyalar da eyni qaydada.

categories cədvəlindəki hazırki məlumat strukturu belədir:

Rekursiv CTE ilə kateqoriya ağacının qurulması

İndi isə bütün kateqoriya iyerarxiyasını iç-içəlik səviyyəsi ilə birlikdə çıxarmaq istəyirik. Bunun üçün rekursiv CTE istifadə edirik.

WITH RECURSIVE category_tree AS (
    -- Əsas sorğu: bütün kök kateqoriyaları seçirik (parent_category_id = NULL)
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth -- Birinci iç-içəlik səviyyəsi
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- Rekursiv sorğu: hər kateqoriya üçün alt-kateqoriyaları tapırıq
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1 AS depth -- İç-içəlik səviyyəsini artırırıq
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- Son sorğu: nəticələri CTE-dən çıxarırıq
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Nəticə:

Burda nə baş verir?

1. Əvvəlcə əsas sorğu (SELECT … FROM categories WHERE parent_category_id IS NULL) əsas kateqoriyaları seçir. Burda bu, yalnız Elektronika-dır və depth = 1.
2. Sonra rekursiv sorğu INNER JOIN ilə alt-kateqoriyaları əlavə edir, iç-içəlik səviyyəsini artırır (depth + 1).
3. Bu proses bütün səviyyələr üçün bütün alt-kateqoriyalar tapılana qədər davam edir.

Faydalı təkmilləşdirmələr

Əsas nümunə işləyir, amma real layihələrdə adətən daha çox şey lazımdır. Məsələn, saytda breadcrumb göstərmək istəyirsən, ya da menecerə hansı kateqoriyada daha çox alt-bölmə olduğunu göstərmək istəyirsən. Gəlin sorğumuzu bir neçə praktik şəkildə təkmilləşdirək.

1. Kateqoriyanın tam yolunun əlavə olunması

Bəzən kateqoriyanın tam yolunu göstərmək faydalı olur, məsələn: Elektronika > Smartfonlar > Aksesuarlar. Bunu string aggregation ilə etmək olar:

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        category_name AS full_path,
        1 AS depth
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.full_path || ' > ' || c.category_name AS full_path, -- string-ləri birləşdiririk
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    category_id,
    category_name,
    parent_category_id,
    full_path,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Nəticə:

İndi hər kateqoriyanın iç-içəlik yolunu göstərən tam yolu var.

2. Alt-kateqoriyaların sayının hesablanması

Bəs hər kateqoriyanın neçə alt-kateqoriyası olduğunu bilmək istəsək?

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        parent_category_id
    FROM categories

    UNION ALL

    SELECT
        c.category_id,
        c.parent_category_id
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    parent_category_id,
    COUNT(*) AS subcategory_count
FROM category_tree
WHERE parent_category_id IS NOT NULL
GROUP BY parent_category_id
ORDER BY parent_category_id;

Nəticə:

Cədvəl göstərir ki, Elektronika-da 3 alt-kateqoriya var (Smartfonlar, Noutbuklar, Foto və video), Smartfonlar və Noutbuklar-da isə bir dənə.

Rekursiv CTE ilə işləyərkən xüsusiyyətlər və tipik səhvlər

Sonsuz rekursiya: Əgər məlumatlarda dövr varsa (məsələn, kateqoriya özü-özünə istinad edirsə), sorğu sonsuz dövrə düşə bilər. Bunu önləmək üçün WHERE depth < N və ya limitlərdən istifadə et.

İşin optimallaşdırılması: Rekursiv CTE-lər böyük həcmdə məlumatda yavaş ola bilər. parent_category_id üzərində index-lərdən istifadə et, sürətlənəcək.

UNION əvəzinə UNION ALL səhvi: Rekursiv CTE üçün həmişə UNION ALL istifadə et, yoxsa PostgreSQL dublikatları silməyə çalışacaq və sorğu yavaşlayacaq.

Bu nümunə göstərir ki, rekursiv CTE-lər iyerarxik strukturlarla işləməyə necə kömək edir. Database-dən iyerarxiya çıxarma bacarığı sənə bir çox real layihədə lazım olacaq. Məsələn, sayt menyusu quranda, təşkilati strukturları analiz edəndə və ya qraf-larla işləyəndə. İndi istənilən çətinlikdə tapşırıqları həll etməyə hazırsan.

1

Sorğu/viktorina

, səviyyə, dərs

Əlçatan deyil

CTE-yə giriş

CTE-yə giriş

Açıq