Пример рекурсивных 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 помогают работать с иерархическими структурами. Навык извлечения иерархий из базы данных пригодится вам во многих реальных проектах. Например, при построении меню сайта, анализа организационных структур или работы с графами. Теперь вы готовы к решению задач любой сложности.

2

Задача

SQL SELF, 27 уровень, 4 лекция

Недоступна

Построение полного пути для каждой категории

Построение полного пути для каждой категории

Открыть

1

Опрос

Введение в CTE, 27 уровень, 4 лекция

Недоступен

Введение в CTE

Введение в CTE

Открыть