CTE: конструкция WITH для читаемости

В прошлом уроке подзапросы вкладывались друг в друга, и читать их приходилось изнутри наружу. CTE решает эту проблему: те же подзапросы получают имена и выстраиваются сверху вниз. На собеседовании CTE спрашивают в двух видах: «перепиши этот запрос читаемо» и «выведи иерархию сотрудников», и второй без рекурсивного CTE не решить. Примеры на учебной базе блока.

Как спрашивают

Что такое CTE и зачем он, если есть подзапросы? Выведи всех сотрудников с уровнем в иерархии подчинения.

Короткий ответ

CTE, конструкция WITH имя AS (запрос), даёт подзапросу имя, по которому его можно использовать в основном запросе и в следующих CTE. Это делает многошаговую логику читаемой сверху вниз. Рекурсивный CTE (WITH RECURSIVE) умеет ссылаться сам на себя и обходит иерархии вроде «сотрудник и руководитель» на любую глубину. В PostgreSQL с 12-й версии обычный CTE по умолчанию встраивается в запрос как подзапрос, если на него ссылаются один раз, так что за читаемость не платят скоростью.

Разбор: черновики на полях

Сложную задачу по математике решают не одной формулой, а промежуточными шагами: «пусть A равно…», «тогда B равно…», «ответ». CTE это такие «пусть» внутри SQL.

Один CTE

Суммы заказов по клиентам:

WITH totals AS (
  SELECT customer_id, sum(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, t.total
FROM totals t
JOIN customers c ON c.id = t.customer_id
ORDER BY t.total DESC;

name  | total
------+------
Ольга | 11000
Павел |  7000
Роман |  1200

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

Клиенты с суммой выше средней. В виде подзапросов здесь была бы матрёшка из трёх уровней. С CTE каждый шаг назван:

WITH totals AS (
  SELECT customer_id, sum(amount) AS total
  FROM orders
  GROUP BY customer_id
),
avg_total AS (
  SELECT avg(total) AS value FROM totals
)
SELECT c.name, t.total
FROM totals t
JOIN customers c ON c.id = t.customer_id
CROSS JOIN avg_total a
WHERE t.total > a.value
ORDER BY t.total DESC;

name  | total
------+------
Ольга | 11000
Павел |  7000

Второй CTE avg_total использует первый totals. CTE перечисляются через запятую, а слово WITH пишется один раз.

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

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

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

WITH RECURSIVE chain AS (
  -- стартовая часть: руководители верхнего уровня
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- рекурсивная часть: подчинённые уже найденных
  SELECT e.id, e.name, e.manager_id, c.level + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT name, level
FROM chain
ORDER BY level, name;

name  | level
------+------
Анна  |     1
Глеб  |     1
Егор  |     1
Борис |     2
Вера  |     2
Дина  |     2
Жанна |     2

Пройдём по шагам:

1. Стартовая часть находит тех, у кого нет руководителя: Анна, Глеб, Егор, уровень 1.
2. Первая итерация рекурсии ищет сотрудников, чей manager_id указывает на найденных: Борис, Вера и Жанна подчиняются Анне, Дина Глебу. Уровень 2.
3. Вторая итерация ищет подчинённых уровня 2. Таких нет, рекурсия останавливается.

Иерархию можно обходить и вверх. Цепочка руководителей Веры:

WITH RECURSIVE up AS (
  SELECT id, name, manager_id FROM employees WHERE name = 'Вера'
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN up ON e.id = up.manager_id
)
SELECT name FROM up;

Ответ: Вера, Анна.