Зачем нужен CTE

К третьему модулю ты уже видел запросы из 50+ строк, в которых подзапросы вложены друг в друга и невозможно понять, что откуда берётся. Это типичная боль реляционного синтаксиса: один длинный SELECT ... FROM (SELECT ... FROM (SELECT ...)) читается изнутри наружу, против направления взгляда.

Синтаксис:

WITH имя AS (
  SELECT ...
)
SELECT ... FROM имя;

Внутри имя доступно так же, как обычная таблица. После ; оно исчезает — никаких следов в базе.

Простой CTE — извлекаем подзапрос наверх

Вот частая пара «до и после». Запрос: «топ-3 категории по выручке».

Запрос немного длиннее в строках, но первая часть (WITH category_revenue AS ...) теперь читается как «вычисли выручку по категориям», а вторая — как «возьми из этого топ-3». Это отделение «что» от «как», главная ценность CTE.

На простых запросах CTE кажется излишним. На запросах из 5+ шагов это становится незаменимо.

Лестница CTE — несколько шагов

WITH принимает много определений, разделённых запятыми. Каждое следующее видит все предыдущие. Это позволяет строить запрос как пайплайн.

Читается сверху вниз, как обычный код. Каждый CTE — это логический шаг, который можно прочитать и понять отдельно от остальных. В обзорах кода это снижает время на чтение в разы.

Области видимости — куда CTE «дотягивается»

Это место, где спотыкаются новички. Запоминай правила:

1. CTE виден только в рамках одного WITH ... SELECT-стейтмента. Как только запрос закончился, имя исчезло. Это не временная таблица.
2. Внутри WITH каждый CTE видит все предыдущие, но не последующие. Порядок имеет значение.
3. CTE НЕ виден внутри других CTE того же уровня, если он не определён ДО них. Если ты сослался на b из a, а b идёт после a — будет ошибка.
4. Финальный SELECT видит все CTE. Это и есть основная причина писать WITH — сделать промежуточные результаты доступными для финального запроса.

Заметь, как финальный SELECT одновременно использует big_orders через JOIN и paid_orders через коррелированный подзапрос в SELECT-списке. Оба CTE доступны до самой последней строки запроса.

CTE можно использовать несколько раз

Это — частый источник путаницы про производительность. Если ты сослался на один и тот же CTE дважды, он не вычисляется дважды автоматически. В PostgreSQL 12+ CTE по умолчанию inлайнятся, и оптимизатор сам решает, повторять ли вычисление. В более старых версиях (или с MATERIALIZED) — материализуется один раз.

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

Чек-лист на хорошее использование CTE

Прежде чем писать запрос с CTE, спроси себя:

1. Есть ли у этого шага осмысленное имя? Если нет — может, и подзапросом обошёлся бы.
2. Будет ли этот промежуточный результат использован несколько раз? Если да — CTE сильно помогает.
3. Скучно ли читать без CTE? Если запрос трудно читать снизу вверх — CTE его выпрямит.
4. Не пытаешься ли ты подменить CTE временной таблицей или materialized view? Если результат нужен между транзакциями — это не работа CTE.

Чек-лист

• WITH имя AS (SELECT ...) определяет именованное промежуточное отношение, видимое внутри одного SQL-стейтмента.
• Через запятую можно описать лестницу CTE: каждый следующий видит предыдущие.
• CTE — это инструмент читаемости в первую очередь. Используй, когда запрос становится сложно читать в один SELECT.
• Финальный SELECT видит все CTE из WITH. Один CTE можно использовать несколько раз.
• CTE — это не временная таблица. После ; он исчезает.