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

В этом уроке ты узнаешь

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

Читать запрос с тремя уровнями вложенных подзапросов — всё равно что решать головоломку изнутри наружу. В программировании в таких случаях промежуточный результат называют и кладут в переменную. В SQL для этого есть CTE (common table expression — обобщённое табличное выражение): запрос делится на именованные шаги, и каждый шаг может пользоваться предыдущими. Более того, CTE может ссылаться сам на себя — это мощный инструмент для иерархий и последовательностей.

WITH: даём имя промежуточному результату

CTE записывают в виде WITH имя AS (запрос) перед основным запросом, и он существует только во время выполнения этого запроса. Его большое преимущество перед подзапросом — к одному CTE можно обращаться несколько раз. Ниже customer_totals используется дважды: в соединении и в подзапросе, который считает среднюю сумму.

SQL· Измени код и запусти снова

WITH customer_totals AS (
  SELECT o.customer_id, SUM(o.quantity * p.price) AS total
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  GROUP BY o.customer_id
)
SELECT c.name, ROUND(ct.total, 2) AS total
FROM customer_totals AS ct
JOIN customers AS c ON c.id = ct.customer_id
WHERE ct.total > (SELECT AVG(total) FROM customer_totals)
ORDER BY ct.total DESC;

▸ Ожидаемый результат

name | total
Anar Mustafayev | 1723
Emre Yılmaz | 941.99
Zəhra Hüseynli | 935.99

Покупатели, потратившие больше среднего (563,15).

Цепочка CTE: запрос по шагам

В одном WITH можно через запятую записать несколько CTE, и каждый может обращаться к предыдущим. Получается «конвейер данных»: 1) вычисляем сумму и страну каждого заказа, 2) подводим итоги по странам, 3) находим долю каждой страны в общей выручке.

SQL· Измени код и запусти снова

WITH lines AS (
  SELECT c.country, o.quantity * p.price AS amount
  FROM orders AS o
  JOIN products  AS p ON p.id = o.product_id
  JOIN customers AS c ON c.id = o.customer_id
),
by_country AS (
  SELECT country, COUNT(*) AS orders, ROUND(SUM(amount), 2) AS revenue
  FROM lines
  GROUP BY country
)
SELECT country, orders, revenue,
       ROUND(100.0 * revenue / (SELECT SUM(revenue) FROM by_country), 1) AS share_pct
FROM by_country
ORDER BY revenue DESC;

▸ Ожидаемый результат

country | orders | revenue | share_pct
Azerbaijan | 7 | 2843.49 | 72.1
Türkiye | 3 | 973.59 | 24.7
United Kingdom | 1 | 69.98 | 1.8
Russia | 1 | 55 | 1.4

Рекурсивный CTE: запрос, ссылающийся на себя

Определение

Рекурсивный CTE

CTE, записанный с WITH RECURSIVE и состоящий из двух частей: начальная часть (anchor) даёт первые строки, а рекурсивная часть после UNION ALL обращается к самому CTE и строит новые строки из строк предыдущего шага. Процесс останавливается, когда рекурсивная часть больше не возвращает строк.

SQL· Измени код и запусти снова

WITH RECURSIVE numbers(n) AS (
  SELECT 1                                -- anchor
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5   -- step + stop
)
SELECT n, n * n AS square
FROM numbers;

▸ Ожидаемый результат

n | square
1 | 1
2 | 4
3 | 9
4 | 16
5 | 25

Зачем нужен такой ряд? Чтобы показывать в отчёте и пустые группы. Например, распределение баллов по интервалам в 10 баллов: GROUP BY вернёт только интервалы, где есть данные. Если создать интервалы рекурсивным CTE и сделать LEFT JOIN, интервал 40–49, куда никто не попал, тоже появится с нулём.

SQL· Измени код и запусти снова

WITH RECURSIVE buckets(low) AS (
  SELECT 40
  UNION ALL
  SELECT low + 10 FROM buckets WHERE low < 90
)
SELECT b.low || '-' || (b.low + 9) AS score_range,
       COUNT(e.id) AS enrollments
FROM buckets AS b
LEFT JOIN enrollments AS e ON e.score BETWEEN b.low AND b.low + 9
GROUP BY b.low
ORDER BY b.low;

▸ Ожидаемый результат

score_range | enrollments
40-49 | 0
50-59 | 1
60-69 | 2
70-79 | 5
80-89 | 4
90-99 | 8

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

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

SQL· Измени код и запусти снова

CREATE TABLE staff (id INTEGER PRIMARY KEY, name TEXT, role TEXT,
                    manager_id INTEGER REFERENCES staff(id));
INSERT INTO staff VALUES
  (1, 'Samir',   'Director',            NULL),
  (2, 'Ramin',   'Head of Science',     1),
  (3, 'Nərmin',  'Head of Humanities',  1),
  (4, 'Ülviyyə', 'Physics teacher',     2),
  (5, 'Elnur',   'Informatics teacher', 2),
  (6, 'Sara',    'English teacher',     3);

WITH RECURSIVE chain(id, name, level, path) AS (
  SELECT id, name, 0, name
  FROM staff
  WHERE manager_id IS NULL
  UNION ALL
  SELECT s.id, s.name, c.level + 1, c.path || ' > ' || s.name
  FROM staff AS s
  JOIN chain AS c ON s.manager_id = c.id
)
SELECT level, path
FROM chain
ORDER BY path;

▸ Ожидаемый результат

level | path
0 | Samir
1 | Samir > Nərmin
2 | Samir > Nərmin > Sara
1 | Samir > Ramin
2 | Samir > Ramin > Elnur
2 | Samir > Ramin > Ülviyyə

ORDER BY path выстраивает дерево как папки: за каждым руководителем идут его подчинённые.

СУБД	Обычный CTE	Рекурсивный CTE
SQLite	WITH	WITH RECURSIVE
PostgreSQL	WITH	WITH RECURSIVE
MySQL 8.0+	WITH	WITH RECURSIVE
SQL Server	WITH	`WITH` (слово RECURSIVE не пишут)

В SQL Server команда перед CTE должна заканчиваться точкой с запятой, поэтому часто пишут ;WITH.

Задание

Создай CTE course_stats: для каждого курса course_id, число зачислений (students) и средний балл (avg_score). Затем соедини его с courses и выведи название, число учеников и средний балл, округлённый до 1 знака, для курсов, где не меньше 3 учеников, — по убыванию среднего балла.

Задание · SQL· Измени код и запусти снова

WITH course_stats AS (
  -- course_id, students, avg_score
)
SELECT c.title, cs.students, ROUND(cs.avg_score, 1) AS avg_score
FROM course_stats AS cs
JOIN courses AS c ON c.id = cs.course_id
-- filter and sort

▸ Ожидаемый результат

title | students | avg_score
Python Basics | 3 | 92.7
Algebra | 4 | 87.3
Geometry | 3 | 81.3
Mechanics | 4 | 80

Задание

С помощью рекурсивного CTE создай ценовые интервалы 0, 100, 200, 300, 400 (low). Выведи, сколько товаров попадает в каждый интервал (products): товар попадает в интервал, если low <= price < low + 100. Пустые интервалы тоже должны появиться с 0. Отсортируй по low.

Задание · SQL· Измени код и запусти снова

WITH RECURSIVE brackets(low) AS (
  SELECT 0
  UNION ALL
  -- next bracket, stop at 400
)
SELECT b.low, COUNT(p.id) AS products
FROM brackets AS b
-- LEFT JOIN products
GROUP BY b.low
ORDER BY b.low;

▸ Ожидаемый результат

low | products
0 | 6
100 | 1
200 | 0
300 | 1
400 | 0

Главное

CTE (WITH имя AS (...)) — именованный промежуточный результат, существующий только в рамках одного запроса.
К одному CTE можно обращаться несколько раз; CTE, перечисленные через запятую, могут использовать предыдущие.
Рекурсивный CTE = начальная часть + UNION ALL + часть со ссылкой на себя + условие остановки.
Рекурсивные CTE применяют для числовых рядов, отчётов с пустыми группами и иерархий (оргструктура, категории).
В SQL Server рекурсивный CTE пишут без слова RECURSIVE, и по умолчанию он ограничен 100 шагами.

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

1 / 10

В чём главное преимущество CTE перед подзапросом во FROM?