Рекурсивні CTE: що це таке і навіщо вони потрібні

Сьогодні ми зробимо ще один крок вперед і займемося магією рекурсії. Якщо ти вже колись програмував на мові з підтримкою рекурсії (наприклад, той же Python), то ти приблизно розумієш, про що йде мова. Але не переймайся, якщо це звучить як щось загадкове — ми розберемо все дуже детально.

Рекурсивні CTE — це потужний інструмент для роботи з ієрархічними, деревоподібними структурами даних, такими як організаційні структури компаній, сімейне дерево або каталоги файлів.

Простими словами, це такі вирази, які можуть "викликати самі себе", щоб поступово обійти і обробити всі рівні даних.

Ключові особливості рекурсивних CTE:

1. Вони використовують ключове слово WITH RECURSIVE.
2. Рекурсивні CTE складаються з двох частин:
   • Базовий запит: визначає початкову точку (або "корінь") рекурсії.
   • Рекурсивний запит: обробляє решту даних, використовуючи результат попереднього кроку.

Алгоритм роботи рекурсивного CTE схожий на те, як ти підіймаєшся по сходах:

• Спочатку ти стаєш на першу сходинку (це базовий запит).
• Потім ти піднімаєшся на другу сходинку, використовуючи результат першої сходинки (рекурсивний запит).
• Цей процес повторюється, поки сходинки не закінчаться (досягнення умови завершення).

Синтаксис рекурсивного CTE

Давай одразу подивимось на шаблонний приклад:

WITH RECURSIVE cte_name AS (
    -- Базовий запит
    SELECT column1, column2
    FROM table_name
    WHERE condition_for_base_case

    UNION ALL

    -- Рекурсивний запит
    SELECT column1, column2
    FROM table_name
    JOIN cte_name ON some_condition
    WHERE stop_condition
)
SELECT * FROM cte_name;

Роль UNION та UNION ALL у рекурсивних CTE

Кожен рекурсивний CTE зобов'язаний використовувати оператори UNION або UNION ALL між базовою та рекурсивною частиною.

Який оператор обрати: UNION чи UNION ALL?

Якщо ти не впевнений, що використовувати — майже завжди обирай UNION ALL. Чому? Бо він працює швидше: він просто об'єднує результати, не перевіряючи, чи є там дублікати. Це означає — менше обчислень, менше ресурсів і швидший результат.

Особливо це важливо в рекурсивних CTE. Коли ти будуєш ієрархії — наприклад, дерево коментарів або структуру підлеглих у компанії — UNION ALL потрібен майже завжди. Якщо використовувати просто UNION, база може випадково вирішити, що якісь кроки вже були і «відрізати» частину результату. А це зламає всю логіку обходу.

Використовувати UNION можна, тільки якщо ти точно знаєш, що дублікати шкідливі і їх треба прибрати. Але пам'ятай: це завжди компроміс між чистотою і швидкістю.

Приклад різних підходів

-- UNION: дублікати виключаються
SELECT 'A'
UNION
SELECT 'A';     -- Результат: один рядок 'A'

-- UNION ALL: дублікати зберігаються
SELECT 'A'
UNION ALL
SELECT 'A';     -- Результат: два рядки 'A'

У рекурсивних запитах безпечніше завжди використовувати UNION ALL, щоб не втратити важливі кроки при обході структури.

Розглянемо типову задачу: у нас є таблиця співробітників з колонками employee_id, manager_id і name. Треба побудувати ієрархію, починаючи з директора — людини без начальника (у якого manager_id = NULL).

Припустимо у нас є таблиця співробітників: employees

Нам треба зрозуміти, хто кому підпорядковується, і дізнатися рівень кожного співробітника в структурі. Це зручно, коли ти хочеш, наприклад, відобразити дерево співробітників в інтерфейсі або підготувати звіт про командну структуру.

WITH RECURSIVE employee_hierarchy AS (
    -- Починаємо з тих, у кого немає начальника
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Додаємо підлеглих і збільшуємо рівень
    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;

Результат буде таким:

Цей запит наочно показує, як можна "пройтися" по ієрархії співробітників — від директора до наймолодших у структурі. Рівень level зручно використовувати для форматування або візуалізації дерева.

Приклад: категорії товарів

Тепер уяви, що ми працюємо з таблицею категорій товарів, де кожна категорія може мати підкатегорії, а ті, в свою чергу, свої підкатегорії. Як нам побудувати дерево категорій?

Таблиця categories

Рекурсивний запит:

WITH RECURSIVE category_tree AS (
    -- Базовий випадок: знайти кореневі категорії
    SELECT
        category_id,
        name,
        parent_id,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Рекурсивна частина: знайти підкатегорії поточних категорій
    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;

Результат:

Тепер ми бачимо дерево категорій з рівнями вкладеності.

Чому рекурсивні CTE — це круто?

Рекурсивні CTE — один із найвиразніших і найпотужніших інструментів SQL. Замість складної вкладеної логіки ти просто описуєш, з чого почати (базовий випадок), і як рухатися далі (рекурсивну частину) — все інше робить PostgreSQL.

Найчастіше такі запити використовують для обходу ієрархій: співробітників, категорій товарів, директорій на диску, графів у соцмережах. Вони легко розширюються: якщо в таблицю додадуться нові дані, запит підхопить їх сам. Це зручно і масштабовано.

Але є і підводні камені. Обов'язково слідкуй за умовами завершення — без них запит може піти в нескінченний цикл. Не забудь про індекси: у великих таблицях рекурсивні запити без них можуть гальмувати. А UNION ALL — майже завжди найкращий вибір, особливо в ієрархічних задачах, інакше ти ризикуєш втратити кроки рекурсії через видалення дублікатів.

Гарно налаштований рекурсивний CTE дозволяє виразити складну бізнес-логіку буквально в кількох рядках — без процедур, циклів і додаткового коду. Це той випадок, коли SQL працює не тільки правильно, а й красиво.

Типові помилки при роботі з рекурсивними CTE

• Нескінченна рекурсія: якщо ти не задаси коректну умову завершення (WHERE), запит може зациклитися.
• Зайві дані: неправильне використання UNION ALL додає дублікати.
• Продуктивність: рекурсивні запити можуть бути важкими для великого обсягу даних. Індекси на ключових колонках (наприклад, manager_id) допоможуть прискорити виконання.

Коли без рекурсивних запитів не обійтись

Іноді здається, що рекурсивні запити — це щось з теорії, але насправді вони часто зустрічаються у повсякденній розробці. Наприклад:

• щоб побудувати звіти по структурі компанії або класифікації товарів;
• щоб обійти дерево папок і зібрати список усіх вкладених директорій;
• щоб аналізувати графи — соціальні зв'язки, маршрути, залежності між задачами;
• щоб просто представити складні зв'язки між об'єктами у читабельному вигляді.

Якщо треба пройтись по структурі, в якій одне залежить від іншого, — майже напевно знадобиться WITH RECURSIVE.