Рекурсивные CTE для иерархий
Обходите иерархические данные — организационные структуры, ветвящиеся комментарии и графы — с помощью WITH RECURSIVE и условий остановки
«Рекурсивные CTE для иерархий» — бесплатный урок SQL Academy на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.
Зачем нужна рекурсия?
Обычный SQL не умеет обходить дерево неизвестной глубины: родителей родителей, потомков потомков. Рекурсивные CTE — стандартное решение SQL для этой задачи.
Структура
Рекурсивный CTE состоит из двух частей, соединённых с помощью UNION ALL:
WITH RECURSIVE name AS (
-- 1. Anchor query: seed rows
SELECT ...
UNION ALL
-- 2. Recursive step: references the CTE itself
SELECT ...
FROM name JOIN ...
)
SELECT * FROM name;Обход организационной структуры
Найдите всех сотрудников, подчиняющихся указанному руководителю напрямую или через несколько уровней:
WITH RECURSIVE reports AS (
-- anchor: the manager themself
SELECT id, full_name, manager_id, 0 AS depth
FROM employees WHERE id = 42
UNION ALL
-- recurse: people whose manager is in reports
SELECT e.id, e.full_name, e.manager_id, r.depth + 1
FROM employees e
JOIN reports r ON r.id = e.manager_id
)
SELECT * FROM reports ORDER BY depth, full_name;Комментарии с ответами
Обойдите дерево обсуждения, начиная с корневого комментария:
WITH RECURSIVE thread AS (
SELECT id, parent_id, body, 0 AS depth, ARRAY[id] AS path
FROM comments WHERE id = $1
UNION ALL
SELECT c.id, c.parent_id, c.body, t.depth + 1, t.path || c.id
FROM comments c
JOIN thread t ON c.parent_id = t.id
)
SELECT * FROM thread ORDER BY path;Завершение
Рекурсия останавливается, когда рекурсивный шаг не возвращает новых строк.
Как избежать бесконечных циклов
Если в графе есть циклы, отслеживайте уже посещённые узлы:
WITH RECURSIVE walk AS (
SELECT id, ARRAY[id] AS path FROM nodes WHERE id = $1
UNION ALL
SELECT e.target_id, w.path || e.target_id
FROM edges e
JOIN walk w ON e.source_id = w.id
WHERE e.target_id <> ALL(w.path)
)
SELECT * FROM walk;Числовые последовательности
Рекурсивные CTE также могут создавать последовательности:
WITH RECURSIVE n(i) AS (
VALUES (1)
UNION ALL
SELECT i + 1 FROM n WHERE i < 100
)
SELECT i, i*i AS square FROM n;Спецификация изделия
Разложите изделие на все компоненты, включая сборочные узлы:
WITH RECURSIVE bom AS (
SELECT part_id, sub_part_id, qty FROM parts WHERE part_id = $1
UNION ALL
SELECT p.part_id, p.sub_part_id, p.qty * bom.qty
FROM parts p
JOIN bom ON bom.sub_part_id = p.part_id
)
SELECT sub_part_id, SUM(qty) AS total_qty FROM bom GROUP BY sub_part_id;Ограничение глубины
В целях безопасности ограничьте глубину рекурсии:
WITH RECURSIVE tree AS (
SELECT id, parent_id, 0 AS depth FROM nodes WHERE id = $1
UNION ALL
SELECT n.id, n.parent_id, t.depth + 1
FROM nodes n JOIN tree t ON n.parent_id = t.id
WHERE t.depth < 10
)
SELECT * FROM tree;UNION и UNION ALL
Обычно выбирают UNION ALL. UNION удаляет дубликаты — это полезно, когда до узла можно добраться несколькими путями.
Производительность
Рекурсивные CTE вычисляются итеративно. На каждом шаге «рабочая таблица» содержит строки, созданные на предыдущем шаге. Создавайте индексы для столбцов, используемых в JOIN.
Итоги
Рекурсивные CTE обходят иерархии и графы.
- Начальная часть + UNION ALL + рекурсивный шаг
- Остановка, когда рекурсивный шаг не возвращает строк
- Используйте массив пути для разрыва циклов
Быстрая проверка
Какое ключевое слово превращает CTE в рекурсивный?
Часто задаваемые вопросы
Урок «Рекурсивные CTE для иерархий» бесплатный?
Да — полный текст урока «Рекурсивные CTE для иерархий» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.
Чему я научусь в уроке «Рекурсивные CTE для иерархий»?
Обходите иерархические данные — организационные структуры, ветвящиеся комментарии и графы — с помощью WITH RECURSIVE и условий остановки Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Academy?
Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.
Сколько времени занимает урок «Рекурсивные CTE для иерархий»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Academy?
Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Скалярные, строковые и табличные подзапросы
- Коррелированные и некоррелированные подзапросы
- Общие табличные выражения (WITH)
- Рекурсивные CTE для иерархий