Общие табличные выражения (WITH / CTE)

CTE — именованный подзапрос

CTE (Common Table Expression, «обобщённое табличное выражение») — это временный именованный результат, объявленный через WITH перед основным запросом. По сути это тот же подзапрос, но вынесенный наверх и названный — так сложные запросы читаются гораздо легче.

WITH seat_counts AS (
  SELECT airplane_code, COUNT(*) AS seats
  FROM seats
  GROUP BY airplane_code
)
SELECT airplane_code, seats
FROM seat_counts
WHERE seats > 200
ORDER BY seats DESC;

Логически мы сначала описываем seat_counts (число мест по самолётам), а основной запрос обращается к нему как к таблице. Физический порядок выбирает оптимизатор: PostgreSQL может встроить обычный CTE в основной запрос или материализовать его, поэтому WITH сам по себе не гарантирует отдельного первого вычисления.

Чем CTE удобнее обычного подзапроса

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

WITH a AS ( ... ),
     b AS ( ... берёт данные из a ... )
SELECT ... FROM b;

Внутри основного запроса к CTE применимо всё, что мы уже знаем: WHERE, JOIN, ORDER BY, и даже вложенный подзапрос (например, сравнение со средним по самому CTE).

Разбор: как строить запрос из CTE по шагам

CTE удобно писать «снизу вверх». Сначала напишите и запустите первый шаг отдельно:

SELECT airplane_code, COUNT(*) AS routes
FROM routes
GROUP BY airplane_code;

Когда результат правильный, заверните его в WITH rc AS (…) и напишите следующий шаг, который берёт данные из rc. Так каждый кусок проверен до того, как на нём построено что-то ещё.

Типичные ошибки

• Второе слово WITH. Несколько CTE перечисляются через запятую после одного WITH: WITH a AS (…), b AS (…).
• Точка с запятой после CTE. WITH a AS (…); SELECT … — ошибка: точка с запятой закончила запрос раньше времени.
• Обращение к CTE из другого запроса. CTE существует только внутри своего запроса. В следующем запросе его уже нет.

💡 CTE не делает запрос «умнее» подзапроса — он делает его понятнее. Для сложных отчётов это огромная помощь. (Существует ещё WITH RECURSIVE для иерархий — это уже за рамками базового курса.)