re:re:actable
Урок 033 минуты чтения

Что такое CTE

На этой странице
Кратко

CTE (Common Table Expression) — именованный подзапрос, объявленный через WITH name AS (...) перед основным запросом. CTE делает сложный запрос читаемым по шагам: «отфильтровать → посчитать → соединить», позволяет сослаться на один промежуточный результат несколько раз и является основой рекурсивных запросов. В PostgreSQL 12+ обычный CTE, на который ссылаются один раз, встраивается в основной запрос (оптимизируется как подзапрос); AS MATERIALIZED принудительно вычисляет его один раз, AS NOT MATERIALIZED — наоборот. В PostgreSQL CTE может содержать и INSERT/UPDATE/DELETE … RETURNING (data-modifying CTE).

Вопрос

Что такое CTE и чем он отличается от подзапроса?

Запрос по шагам

WITH paid_orders AS (
  SELECT user_id, total
  FROM orders
  WHERE status = 'paid'
    AND created_at >= now() - interval '90 days'
),
user_revenue AS (
  SELECT user_id, sum(total) AS revenue, count(*) AS orders
  FROM paid_orders
  GROUP BY user_id
)
SELECT u.email, r.revenue, r.orders
FROM user_revenue r
JOIN users u ON u.id = r.user_id
WHERE r.revenue > 10000
ORDER BY r.revenue DESC;

Каждый CTE видит предыдущие — запрос читается сверху вниз.

Повторное использование

WITH stats AS (
  SELECT avg(total) AS avg_total, stddev(total) AS sd FROM orders
)
SELECT o.*
FROM orders o, stats s
WHERE o.total > s.avg_total + 3 * s.sd;     -- аномально крупные заказы

MATERIALIZED и NOT MATERIALIZED (PostgreSQL 12+)

WITH recent AS MATERIALIZED (
  SELECT * FROM events WHERE created_at > now() - interval '1 hour'
)
SELECT ... FROM recent r1 JOIN recent r2 ON ...;
ПоведениеКогда
По умолчанию: встраивается, если не рекурсивный, без побочных эффектов и используется один разОбычные запросы
MATERIALIZEDТяжёлое вычисление используется несколько раз; нужно «оптимизационное ограждение»
NOT MATERIALIZEDНужно, чтобы фильтры основного запроса проникли внутрь CTE, используемого несколько раз

До PostgreSQL 12 CTE всегда материализовался — старые советы «CTE медленнее подзапроса» относятся к этому.

Data-modifying CTE

-- перенести старые заказы в архив одной командой
WITH moved AS (
  DELETE FROM orders
  WHERE created_at < now() - interval '3 years'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;

Все части выполняются в одном снимке данных, атомарно.

CTE или подзапрос

CTEПодзапрос
Читаемость сложной логикиВысокаяПадает с вложенностью
Повторное использованиеДаНужно дублировать
РекурсияWITH RECURSIVEНет
Производительность в PG 12+Обычно та же—

Частые ошибки

  • Считать, что CTE всегда кешируется (в PG 12+ это не так).
  • MATERIALIZED там, где фильтр снаружи мог бы использовать индекс.
  • CTE-«лапша» из десяти шагов, где хватило бы одного JOIN.
  • Ожидать, что UPDATE в одном CTE виден SELECT в другом CTE того же запроса — они видят исходный снимок.

Что важно помнить

  • CTE — именованный шаг запроса для читаемости и переиспользования.
  • В PostgreSQL 12+ одноразовый CTE встраивается.
  • CTE умеет изменять данные и быть рекурсивным.

Мини-шпаргалка

  • WITH a AS (...), b AS (... FROM a) SELECT ....
  • AS MATERIALIZED / AS NOT MATERIALIZED.
  • WITH x AS (DELETE ... RETURNING *) INSERT ....
  • WITH RECURSIVE — иерархии.

Документация

PostgreSQL: WITH Queries.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам