Приклад рекурсивних CTE для роботи з ієрархіями

Уяви: у тебе є інтернет-магазин з тисячами товарів, акуратно розкладених по поличках — категорії, підкатегорії, під-підкатегорії. На сайті це виглядає як гарне випадаюче меню, але в базі даних перетворюється на головний біль. Як витягнути всю гілку "Електроніка → Смартфони → Аксесуари" одним запитом? Як порахувати, скільки рівнів вкладеності у кожної категорії? Звичайні JOIN-и тут безсилі — потрібна рекурсія!

Побудова структури категорій товарів за допомогою рекурсивних CTE

Одна з класичних задач у реляційних базах даних — робота з ієрархічними структурами. Уяви, що у тебе є дерево категорій товарів: головні категорії, підкатегорії, під-підкатегорії і так далі. Наприклад:

Електроніка
  └── Смартфони
      └── Аксесуари
  └── Ноутбуки
      └── Геймерські
  └── Фото і відео

Ця структура легко уявляється в інтерфейсах інтернет-магазинів, але як її зберігати в базі даних і витягати? Тут на допомогу приходять рекурсивні CTE!

Початкова таблиця категорій

Спочатку створимо таблицю categories, яка буде зберігати дані про категорії товарів:

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- Унікальний ідентифікатор категорії
    category_name TEXT NOT NULL,          -- Назва категорії
    parent_category_id INT                -- Батьківська категорія (NULL для головних категорій)
);

Ось приклад даних, які ми додамо в таблицю:

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('Електроніка', NULL),
    ('Смартфони', 1),
    ('Аксесуари', 2),
    ('Ноутбуки', 1),
    ('Геймерські', 4),
    ('Фото і відео', 1);

Що тут відбувається:

• Електроніка — це головна категорія (немає батьківської, parent_category_id = NULL).
• Смартфони знаходяться всередині категорії Електроніка.
• Аксесуари відносяться до категорії Смартфони.
• Аналогічно інші категорії.

Поточна структура даних у таблиці categories виглядає так:

Побудова дерева категорій за допомогою рекурсивного CTE

Тепер ми хочемо отримати всю ієрархію категорій із зазначенням рівня вкладеності. Для цього використовуємо рекурсивний CTE.

WITH RECURSIVE category_tree AS (
    -- Базовий запит: вибираємо всі кореневі категорії (parent_category_id = NULL)
    SELECT
        category_id,
        category_name,
        parent_category_id,
        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.depth + 1 AS depth -- Збільшуємо рівень вкладеності
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- Фінальний запит: витягуємо результати з CTE
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Результат:

Що тут відбувається?

1. Спочатку базовий запит (SELECT … FROM categories WHERE parent_category_id IS NULL) вибирає головні категорії. У цьому випадку це тільки Електроніка з depth = 1.
2. Далі рекурсивний запит за допомогою INNER JOIN додає підкатегорії, збільшуючи рівень вкладеності (depth + 1).
3. Цей процес повторюється, поки не знайдуться всі підкатегорії для всіх рівнів.

Корисні доопрацювання

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

1. Додавання повного шляху категорії

Іноді корисно показувати повний шлях до категорії, наприклад: Електроніка > Смартфони > Аксесуари. Це можна реалізувати, використовуючи стрічкову агрегацію:

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, -- склеюємо стрічки
        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;

Результат:

Тепер у кожної категорії є повний шлях, що показує вкладеність.

2. Підрахунок кількості підкатегорій

А що, якщо ми хочемо дізнатися, скільки підкатегорій є у кожної категорії?

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;

Результат:

Таблиця показує, що у Електроніки 3 підкатегорії (Смартфони, Ноутбуки, Фото і відео), а у Смартфонів і Ноутбуків по одній.

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

Нескінченна рекурсія: Якщо дані містять цикли (наприклад, категорія посилається сама на себе), запит може піти в нескінченний цикл. Щоб цього уникнути, можна обмежити глибину рекурсії за допомогою WHERE depth < N або лімітів.

Оптимізація роботи: Рекурсивні CTE можуть бути повільними на великих об'ємах даних. Використовуй індекси на parent_category_id для прискорення.

Помилка UNION замість UNION ALL: Завжди використовуй UNION ALL для рекурсивних CTE, інакше PostgreSQL буде намагатися видалити дублі, що сповільнить виконання запиту.

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

1

Опитування

Вступ до CTE, рівень 27, лекція 4

Недоступний

Вступ до CTE

Вступ до CTE

Відкрити