SQL Academy · Урок

Как работают рекурсивные CTE

Q: Урок «Как работают рекурсивные CTE» бесплатный?

Да — полный текст урока «Как работают рекурсивные CTE» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Q: Чему я научусь в уроке «Как работают рекурсивные CTE»?

Базовый случай плюс рекурсивный шаг Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Q: Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.

Q: Сколько времени занимает урок «Как работают рекурсивные CTE»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Q: Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Базовый случай плюс рекурсивный шаг

«Как работают рекурсивные CTE» — бесплатный урок SQL Academy на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

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

Рекурсивное CTE — это общее табличное выражение, которое ссылается само на себя. Оно позволяет писать запросы, повторяющие один шаг до выполнения условия, — подобно циклу, но в виде обычного SQL.

Рекурсивные CTE определяются с помощью ключевого слова WITH RECURSIVE и идеально подходят для обхода иерархических или подобных графам данных, например организационных структур, деревьев папок и спецификаций изделий.

Структура из двух частей

Каждое рекурсивное CTE состоит ровно из двух частей, разделённых оператором UNION ALL:

1. Базовый случай — нерекурсивный SELECT, возвращающий исходные строки.

2. Рекурсивный шаг — SELECT, который соединяет CTE с самим собой и формирует строки следующего уровня.

Механизм базы данных продолжает выполнять рекурсивный шаг и накапливать результаты, пока тот не перестанет возвращать новые строки.

WITH RECURSIVE cte_name AS (
  -- Base case
  SELECT ...
  UNION ALL
  -- Recursive step (references cte_name)
  SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;

Подсчёт от 1 до 5

Самый простой рекурсивный CTE подсчитывает числа. В базовом случае задаётся начальное значение 1. На каждом шаге рекурсии добавляется 1. Предложение WHERE внутри рекурсивного шага служит условием завершения — без него запрос выполнялся бы бесконечно.

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;

Пошаговое выполнение

Вот как механизм базы данных обрабатывает CTE-счётчик на каждой итерации:

Итерация 0 (базовый случай): возвращает {1}.

Итерация 1: применяет рекурсивный шаг к {1} и возвращает {2}.

Итерация 2: применяет рекурсивный шаг к {2} и возвращает {3}.

Итерации 3 и 4: возвращают сначала {4}, затем {5}.

Итерация 5: условие WHERE n < 5 ложно для n=5, поэтому возвращается ноль строк. Запрос завершается.

Все накопленные строки — 1, 2, 3, 4, 5 — составляют итоговый результат.

Настройка таблицы иерархии

Рекурсивные CTE особенно эффективны для таблиц, ссылающихся на самих себя. Создадим таблицу employees, в которой у каждого сотрудника может быть необязательное поле manager_id, указывающее на строку в той же таблице.

CREATE TABLE employees (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(50),
  manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

Обход иерархии

Теперь можно пройти всю цепочку подчинения, начиная с CEO (Alice, id=1). В базовом случае выбирается Alice, а рекурсивный шаг находит всех сотрудников, чей manager_id совпадает с уже имеющимся в CTE идентификатором.

Результат включает каждого сотрудника, достижимого от Alice, независимо от глубины дерева.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT depth, name FROM org_tree ORDER BY depth, name;

Отслеживание пути

Распространённое улучшение — построение строки пути, показывающей всю цепочку от корня до каждого узла. По мере углубления рекурсии мы объединяем имена, разделяя их с помощью ' -> '.

Это упрощает отображение навигации в виде цепочки ссылок и отладку глубоких иерархий.

WITH RECURSIVE org_tree AS (
  SELECT id, name, name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.path || ' -> ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;

Ограничение глубины рекурсии

Глубокие или циклические данные могут заставить рекурсивное CTE выполняться очень долго. Используйте две безопасные практики:

1. Отслеживайте глубину и добавляйте предложение WHERE — WHERE depth < 10 гарантирует, что обход не выйдет за пределы 10 уровней.

2. Используйте столбец для обнаружения циклов — некоторые базы данных (PostgreSQL 14+) поддерживают синтаксис CYCLE, автоматически обнаруживающий повторные посещения узлов.

WITH RECURSIVE org_tree AS (
  SELECT id, name, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10
)
SELECT depth, name FROM org_tree;

UNION и UNION ALL в рекурсивных CTE

В рекурсивном шаге почти всегда используется UNION ALL, а не UNION. Вот почему:

UNION удаляет дубликаты строк после каждой итерации, сравнивая весь набор результатов. Это чрезвычайно затратно и может изменить смысл для графов, в которых один и тот же узел действительно достижим по нескольким путям.

UNION ALL сохраняет все строки без удаления дубликатов, что быстрее и правильно при обходе дерева. Используйте UNION только при конкретной необходимости удалить дубликаты и если Вы понимаете связанные с этим затраты производительности.

Формирование последовательности дат

Рекурсивные CTE также удобны для формирования последовательностей дат. Этот пример создаёт запись для каждого дня заданной недели — такой подход часто используется для построения календарных отчётов или заполнения пропусков во временных рядах.

WITH RECURSIVE date_series AS (
  SELECT DATE '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL '1 day'
  FROM date_series
  WHERE day < DATE '2024-01-07'
)
SELECT day FROM date_series;

Поиск всех подчинённых одного руководителя

В базовом случае можно указать любой конкретный узел, а не только корневой. Здесь мы начинаем с Bob (id=2) и находим всех, кто подчиняется ему прямо или косвенно.

Этот подход полезен для проверок прав доступа, агрегаций по поддереву или ограничения данных на панели мониторинга одним отделом.

WITH RECURSIVE subordinates AS (
  SELECT id, name
  FROM employees
  WHERE id = 2
  UNION ALL
  SELECT e.id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates;

Быстрая проверка

Проверьте, насколько хорошо Вы поняли принцип работы рекурсивных CTE.

Итоги урока

В этом уроке Вы узнали, как работают рекурсивные CTE:

Структура: каждое рекурсивное CTE содержит базовый случай (исходные строки), соединённый с рекурсивным шагом (самоссылочным SELECT) с помощью UNION ALL.

Завершение: механизм базы данных повторяет рекурсивный шаг и накапливает результаты, пока этот шаг не вернёт ноль строк.

Распространённые применения: обход организационных структур и деревьев папок, формирование последовательностей чисел или дат, вычисление путей и поиск всех узлов в поддереве.

Советы по безопасности: всегда добавляйте условие завершения (ограничение глубины или защиту от циклов) и для повышения производительности предпочитайте UNION ALL оператору UNION.

Часто задаваемые вопросы

Урок «Как работают рекурсивные CTE» бесплатный?

Да — полный текст урока «Как работают рекурсивные CTE» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Как работают рекурсивные CTE»?

Базовый случай плюс рекурсивный шаг Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.

Сколько времени занимает урок «Как работают рекурсивные CTE»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

← Назад к SQL Academy