CTE и рекурсивные запросы

Как превратить запрос-монстр в лесенку понятных шагов и обойти дерево любой глубины.

CTE (Common Table Expression) — именованный временный результат, объявленный через WITH имя AS (...) и доступный в основном запросе, как будто это обычная таблица.

Когда запрос обрастает вложенными подзапросами в три-четыре уровня, его становится невозможно читать: глаз тонет в скобках, а одну и ту же выборку приходится повторять. CTE решает обе беды. Вы даёте промежуточному результату имя и обращаетесь к нему по имени — запрос читается сверху вниз, как список шагов рецепта.

Зачем это на практике

Три типичных сценария. Первый — читаемость: разложить отчёт на этапы «сначала посчитаем суммы по клиентам, потом отберём крупных, потом присоединим адреса». Второй — переиспользование: если одна и та же подвыборка нужна дважды, CTE считает её один раз и подставляет в обоих местах. Третий, самый мощный — рекурсия: обход иерархий (сотрудник → начальник, комментарий → родитель, категория → подкатегория), где заранее неизвестна глубина дерева.

Обычный CTE: лесенка шагов

Синтаксис прост: WITH имя AS ( запрос ), после чего идёт основной SELECT, который видит это имя. Несколько CTE перечисляются через запятую. Запустите пример — он считает зарплатный фонд по отделам и оставляет только те, где фонд больше 100000.

CREATE TABLE emp (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO emp (name, dept, salary) VALUES
  ('Анна','IT',120000), ('Борис','IT',95000),
  ('Вера','HR',70000),  ('Глеб','HR',65000),
  ('Дина','Sales',80000);

WITH dept_fund AS (
  SELECT dept, SUM(salary) AS fund
  FROM emp
  GROUP BY dept
)
SELECT dept, fund
FROM dept_fund
WHERE fund > 100000
ORDER BY fund DESC;

Вывод:

IT|215000

Тот же результат можно записать подзапросом в FROM, но CTE читается линейно: «вот таблица фондов по отделам — теперь фильтруем». Имя dept_fund само документирует смысл шага.

Несколько CTE подряд

CTE можно складывать цепочкой: следующий ссылается на предыдущий. Это и есть та самая лесенка — каждый шаг опирается на готовый результат прошлого.

CREATE TABLE sales (id INTEGER PRIMARY KEY, manager TEXT, amount INTEGER);
INSERT INTO sales (manager, amount) VALUES
  ('Анна',500), ('Анна',300), ('Борис',900), ('Вера',200);

WITH per_manager AS (
  SELECT manager, SUM(amount) AS total
  FROM sales
  GROUP BY manager
),
avg_line AS (
  SELECT AVG(total) AS avg_total FROM per_manager
)
SELECT p.manager, p.total
FROM per_manager p, avg_line a
WHERE p.total >= a.avg_total
ORDER BY p.total DESC;

Вывод:

Борис|900
Анна|800

Рекурсивный CTE: обход дерева

Иерархию в реляционной таблице обычно хранят через ссылку на родителя: у строки есть колонка parent_id, указывающая на id родителя. Чтобы достать всё поддерево, нужен рекурсивный CTE — WITH RECURSIVE. Он состоит из двух частей, объединённых через UNION ALL:

• якорь (anchor) — стартовая строка (или строки), от которой начинаем спуск;
• рекурсивная часть — JOIN самого CTE с таблицей, который на каждом шаге добавляет следующий уровень.

База останавливает рекурсию сама, когда очередной шаг не находит новых строк. Разберём на дереве сотрудников: у каждого есть boss_id. Найдём всю цепочку подчинения под руководителем с id = 1.

CREATE TABLE staff (id INTEGER PRIMARY KEY, name TEXT, boss_id INTEGER);
INSERT INTO staff (id, name, boss_id) VALUES
  (1,'Директор', NULL),
  (2,'Рук. IT', 1),
  (3,'Рук. продаж', 1),
  (4,'Программист', 2),
  (5,'Тестировщик', 2),
  (6,'Менеджер', 3);

WITH RECURSIVE subtree AS (
  SELECT id, name, boss_id, 0 AS level
  FROM staff
  WHERE id = 1
  UNION ALL
  SELECT s.id, s.name, s.boss_id, st.level + 1
  FROM staff s
  JOIN subtree st ON s.boss_id = st.id
)
SELECT level, name
FROM subtree
ORDER BY level, id;

Вывод:

0|Директор
1|Рук. IT
1|Рук. продаж
2|Программист
2|Тестировщик
2|Менеджер

Колонка level накапливает глубину: якорь даёт 0, каждый виток рекурсии прибавляет единицу. Точно так же обходят дерево комментариев (ответ ссылается на parent_id комментария), категории каталога или граф зависимостей задач.

Как это работает под капотом

База держит «рабочую таблицу» текущего уровня. Сначала туда попадает результат якоря. Затем движок повторяет рекурсивную часть: берёт строки из рабочей таблицы, джойнит с основной таблицей, новые строки складывает в результат и делает их новой рабочей таблицей. Цикл крутится, пока очередной шаг возвращает хотя бы одну строку; как только новых строк нет — обход завершён. Важно: UNION ALL (а не UNION) не дедуплицирует — на чистом дереве дублей и не будет, а проверка уникальности стоит дорого.

Частые ошибки

• Нет условия остановки и зацикливание. Если в данных есть цикл (A — начальник B, B — начальник A), рекурсия не закончится. Спасает счётчик уровня с ограничением WHERE level < 100 в рекурсивной части.
• Забыли RECURSIVE. Без ключевого слова CTE не сможет ссылаться сам на себя — будет ошибка «нет такой таблицы».
• Думают, что CTE всегда быстрее подзапроса. Это не так: в одних СУБД CTE — оптимизационный барьер (материализуется), в других — инлайнится. CTE про читаемость, а не про скорость; для производительности смотрите план запроса.
• Ждут, что имя CTE видно «снаружи». CTE живёт только в пределах одного запроса. Нужен переиспользуемый объект — это VIEW.

Итоги

• WITH имя AS (...) даёт промежуточному результату имя и делает запрос читаемым сверху вниз.
• Несколько CTE через запятую выстраиваются в цепочку шагов.
• WITH RECURSIVE = якорь + рекурсивная часть через UNION ALL; обходит деревья и иерархии любой глубины.
• Накапливайте level и ограничивайте его, чтобы не зациклиться на «грязных» данных.
• CTE — про ясность кода; за скоростью идите в план запроса, а не в синтаксис.