Основы CTE: имя для подзапроса

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

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

Сравни один и тот же замысел — «менеджеры, чья команда разместила больше 100 заказов за прошлый месяц» — написанный двумя способами над employees(id, manager_id, name) и таблицей orders:

-- Вложенный: читаешь изнутри наружу, и внутренний агрегат погребён
SELECT e.name, t.order_count
FROM employees e
JOIN (
  SELECT o.handled_by, count(*) AS order_count
  FROM orders o
  WHERE o.created_at >= date_trunc('month', now()) - interval '1 month'
    AND o.created_at <  date_trunc('month', now())
  GROUP BY o.handled_by
) t ON t.handled_by = e.id
WHERE t.order_count > 100;

-- Тот же запрос, нисходящий конвейер
WITH last_month AS (
  SELECT o.handled_by, count(*) AS order_count
  FROM orders o
  WHERE o.created_at >= date_trunc('month', now()) - interval '1 month'
    AND o.created_at <  date_trunc('month', now())
  GROUP BY o.handled_by
)
SELECT e.name, lm.order_count
FROM last_month lm
JOIN employees e ON e.id = lm.handled_by
WHERE lm.order_count > 100;

Логически оба запроса одинаковы. Второй даёт имя грязной части (last_month), и финальный SELECT читается как фраза. CTE отлаживают, прогоняя его отдельно — вставь только внутренний SELECT в сессию и посмотри его строки — что куда труднее с глубоко вложенной производной таблицей, погребённой в FROM.

Цепочки: каждый CTE видит предыдущие

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

WITH recent_orders AS (
  SELECT handled_by, amount
  FROM orders
  WHERE created_at >= now() - interval '30 days'
),
per_employee AS (                       -- читает из recent_orders
  SELECT handled_by, sum(amount) AS revenue, count(*) AS n
  FROM recent_orders
  GROUP BY handled_by
),
ranked AS (                             -- читает из per_employee
  SELECT handled_by, revenue, n,
         revenue / NULLIF(n, 0) AS avg_ticket
  FROM per_employee
  WHERE n >= 10
)
SELECT e.name, r.revenue, r.avg_ticket
FROM ranked r
JOIN employees e ON e.id = r.handled_by
ORDER BY r.revenue DESC;

Направление важно. CTE не может ссылаться на CTE, объявленный после него — это была бы прямая ссылка вперёд, и Postgres её отвергает — с единственным исключением: CTE, ссылающийся сам на себя, то есть рекурсия, тема урока 04. Пока располагай свои CTE так, чтобы каждый зависел только от более ранних.

Что CTE даёт, а что нет

Выигрыш — читаемость и переиспользование внутри одного оператора: дай имя результату один раз, ссылайся по имени несколько раз, и ты ни разу не дублируешь текст подзапроса. CTE не сохраняется — имя исчезает в момент завершения оператора. Если нужен переиспользуемый именованный запрос между многими операторами — это VIEW, который урок 02 противопоставляет CTE. А исторически CTE был оптимизационным барьером, менявшим как Postgres выполнял твой запрос; это поведение изменилось в Postgres 12, и урок 03 целиком об этом. Для этого урока считай CTE чистым инструментом читаемости: тот же результат, более ясная форма.