RECURSIVE CTE

Kirish: Ushbu qo'llanmada siz tashkiliy jadvallar, toifalar daraxti va ko'p bosqichli iyerarxik ma'lumotlar bilan ishlash uchun PostgreSQL rekursiv CTE (RECURSIVE CTE) dan foydalanishni o'rganasiz.

PostgreSQL Rekursiv CTE ga kirish

Dasturlashda rekursiv funksiya — to'xtash shartiga yetgunga qadar o'z-o'zini qayta chaqiruvchi funksiyadir. Xuddi shuningdek, rekursiv CTE ham o'zining ichki so'rovida o'ziga-o'zi murojaat qiladigan vaqtinchalik jadval ifodasidir.

Rekursiv CTE ikkita asosiy qismdan iborat bo'ladi:

1. Boshlang'ich a'zo (Anchor member) — rekursiyaning boshlang'ich nuqtasi (bazaviy natijalar to'plami).
2. Rekursiv a'zo (Recursive member) — CTE ning o'ziga murojaat qiluvchi va to'xtash sharti bajarilgunga qadar takroriy ishlovchi so'rov.

Boshlang'ich va rekursiv a'zolarning natijalari UNION yoki UNION ALL operatori yordamida bitta to'plamga birlashtiriladi.

Rekursiv CTE sintaksisi quyidagicha:

WITH RECURSIVE cte_name (column1, column2, ...) AS (
    -- Boshlang'ich a'zo (Anchor member)
    SELECT ...
    UNION ALL
    -- Rekursiv a'zo (Recursive member)
    SELECT ...
    FROM cte_name
    WHERE ...
)
SELECT *
FROM cte_name;

Ushbu sintaksisda:

• WITH RECURSIVE rekursiv CTE e'lon qilinayotganini bildiradi.
• Boshlang'ich a'zo dastlabki qatorlarni hosil qiladi.
• Rekursiv a'zo ushbu qatorlarni oladi va shart qanoatlanmay qolguncha yangi qatorlarni hosil qilib boradi.
• Asosiy so'rov esa hosil bo'lgan barcha qatorlarni o'qiydi.

PostgreSQL Rekursiv CTE ga oddiy misol

Quyidagi misol rekursiv CTE yordamida 3 dan 1 gacha bo'lgan ortga hisoblashni (countdown) amalga oshiradi:

WITH RECURSIVE count_down (counter) AS (
    -- Boshlang'ich a'zo
    SELECT 3 AS counter
    UNION
    -- Rekursiv a'zo
    SELECT counter - 1
    FROM count_down
    WHERE counter > 1
)
SELECT * FROM count_down;

Natija:

 counter
---------
       3
       2
       1

So'rov qanday ishlaydi?

1. 1-iteratsiya: Boshlang'ich a'zo counter = 3 ni hosil qiladi.
2. 2-iteratsiya: Rekursiv a'zo 3 - 1 = 2 ni hosil qiladi (WHERE counter > 1 rost).
3. 3-iteratsiya: Rekursiv a'zo 2 - 1 = 1 ni hosil qiladi.
4. 4-iteratsiya: Endi counter > 1 sharti bajarilmaydi va rekursiya avtomatik to'xtaydi.
5. UNION barcha qadamlardagi natijalarni [3, 2, 1] birlashtirib qaytaradi.

Kategoriyalar daraxtini so'rashda rekursiv CTE

Kategoriyalari daraxtsimon tuzilgan mahsulotlar jadvalini ko'rib chiqaylik:

CREATE TABLE IF NOT EXISTS categories (
  category_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  category_name VARCHAR(255) NOT NULL UNIQUE,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES categories (category_id) ON DELETE CASCADE
);

Quyidagi rekursiv CTE yordamida barcha kategoriyalarni ularning iyerarxik chuqurligi (depth) bilan birga olamiz:

WITH RECURSIVE category_hierarchy AS (
    -- Boshlang'ich a'zo: eng yuqori kategoriyalar (parent_id IS NULL)
    SELECT category_id, category_name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    -- Rekursiv a'zo: quyi toifalar (depth + 1)
    SELECT c.category_id, c.category_name, c.parent_id, s.depth + 1
    FROM categories c
    INNER JOIN category_hierarchy s ON c.parent_id = s.category_id
)
SELECT * FROM category_hierarchy;

Natija:

 category_id |   category_name    | parent_id | depth
-------------+--------------------+-----------+-------
           1 | Electronics        |      NULL |     0
           2 | Mobile Devices     |         1 |     1
           7 | Home Entertainment |         1 |     1
          10 | Computers          |         1 |     1
           3 | Smartphones        |         2 |     2
           4 | Tablets            |         2 |     2
           5 | Accessories        |         2 |     2
           6 | Wearables          |         2 |     2
           8 | Televisions        |         7 |     2
           9 | Audio Systems      |         7 |     2
          11 | Laptops            |        10 |     2
          12 | Desktops           |        10 |     2

Xulosa

• Rekursiv CTE o'z-o'ziga murojaat qiluvchi ichki a'zoga ega bo'lib, iyerarxik va daraxtsimon ma'lumotlarni so'rash uchun juda qulaydir.
• Har doim cheksiz sikl (infinite loop) yuzaga kelmasligi uchun rekursiv a'zoda aniq to'xtash shartini (WHERE) ta'minlash zarur.