Рекурсивные 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.

2

Задача

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

Недоступна

Построение иерархии сотрудников

Построение иерархии сотрудников

Открыть