Что такое WITH … AS (CTE) в SQL? Common Table Expressions для начинающих

WITH … AS — это синтаксис для Common Table Expression (сокращённо CTE). По-русски — «обобщённое табличное выражение», но в речи все говорят просто «CTE» или «with-блок».

Простыми словами: ты задаёшь запросу временное имя для подзапроса и потом используешь это имя как обычную таблицу. Сложный запрос на 100 строк превращается в три простых блока, каждый со своей ясной задачей.

Зачем нужен CTE

Главная причина — читаемость. Сравни два варианта:

-- Без CTE: всё в одном комке
SELECT customer_id, total, total * 1.0 / (SELECT SUM(amount) FROM orders) AS share
FROM (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) sub
WHERE total > 1000
ORDER BY total DESC;

-- С CTE: видно шаги
WITH per_customer AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
),
grand_total AS (
  SELECT SUM(amount) AS sum_all FROM orders
)
SELECT
  pc.customer_id,
  pc.total,
  pc.total * 1.0 / gt.sum_all AS share
FROM per_customer pc, grand_total gt
WHERE pc.total > 1000
ORDER BY pc.total DESC;

Второй вариант длиннее, но прозрачнее: «считаем сумму по клиенту → считаем общую сумму → делим одно на другое». Через год, когда вернёшься к этому коду, поймёшь сразу.

Базовый синтаксис

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

Структура:

1. WITH имя AS (...) — определение временной таблицы.
2. После закрывающей скобки — основной запрос, который её использует.

После WITH обязательно идёт основной SELECT (или INSERT/UPDATE/DELETE). Сама CTE без основного запроса бесполезна.

Простой пример

Таблица orders:

id	customer_id	amount
1	1	100
2	1	250
3	2	80
4	3	500

Хотим: «клиенты с суммой больше 200, отсортированные».

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM customer_totals
WHERE total > 200
ORDER BY total DESC;

Внутри CTE — посчитали сумму по каждому клиенту. Снаружи — отфильтровали и отсортировали.

Промежуточная «таблица» customer_totals:

customer_id	total
1	350
2	80
3	500

Финал после фильтра и сортировки:

customer_id	total
3	500
1	350

Несколько CTE через запятую

WITH
  active_users AS (
    SELECT id FROM users WHERE last_login_at > NOW() - INTERVAL '30 days'
  ),
  big_orders AS (
    SELECT user_id, COUNT(*) AS orders_cnt
    FROM orders
    WHERE amount > 1000
    GROUP BY user_id
  )
SELECT u.id, u.email, COALESCE(b.orders_cnt, 0) AS big_orders
FROM active_users u
LEFT JOIN big_orders b ON b.user_id = u.id
ORDER BY big_orders DESC;

CTE разделяются запятыми, каждая получает своё имя. В основном запросе используешь их как обычные таблицы.

CTE можно использовать в JOIN

CTE не отличается от таблицы в плане синтаксиса. Можно JOIN, WHERE, GROUP BY, любые штуки:

WITH top_products AS (
  SELECT product_id, SUM(quantity) AS sold
  FROM order_items
  GROUP BY product_id
  ORDER BY sold DESC
  LIMIT 10
)
SELECT p.name, p.category, t.sold
FROM top_products t
JOIN products p ON p.id = t.product_id;

CTE с DML

В PostgreSQL CTE может содержать не только SELECT, но и INSERT/UPDATE/DELETE с RETURNING. Это позволяет атомарно «удалить из A → вставить в B»:

WITH archived AS (
  DELETE FROM orders
  WHERE created_at < NOW() - INTERVAL '2 years'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM archived;

Один statement, одна транзакция, никаких промежуточных состояний. Без CTE пришлось бы делать через INSERT FROM SELECT потом DELETE, и между ними другая транзакция могла что-то добавить.

CTE vs подзапрос

В простых случаях CTE и подзапрос делают одно и то же:

-- Подзапрос
SELECT * FROM (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) sub
WHERE total > 1000;

-- CTE
WITH per_customer AS (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id)
SELECT * FROM per_customer WHERE total > 1000;

Когда CTE лучше:

• Когда тот же подзапрос нужен несколько раз.
• Когда логически удобнее разбить на «шаги» с именами.
• Когда подзапрос длинный и плохо читается.

Когда подзапрос лучше:

• Очень короткий, одноразовый.
• В WHERE (типа ... WHERE id IN (SELECT ...)).

Производительность CTE в Postgres — важная деталь

До Postgres 12 CTE был «optimization fence» — Postgres не пытался «сплющить» запрос, а выполнял CTE отдельно. Это могло сильно тормозить.

С Postgres 12+ оптимизатор стал умнее: для большинства non-recursive CTE он inline'ит их в основной запрос. Если хочешь старое поведение, явно WITH x AS MATERIALIZED (...). Если хочешь принудительный inline — WITH x AS NOT MATERIALIZED (...).

В абсолютном большинстве случаев — пиши обычный CTE, доверяй Postgres.

Частые ошибки новичков

1. CTE без основного запроса. WITH x AS (SELECT ...) без SELECT * FROM x ниже — синтаксическая ошибка. CTE — это «префикс» к основному запросу, не самостоятельный.

2. Запятые между CTE. WITH a AS (...) WITH b AS (...) SELECT ... — НЕ работает. Два слова WITH нельзя. Правильно: WITH a AS (...), b AS (...) SELECT ....

3. Пытаются модифицировать CTE. WITH x AS (...) UPDATE x SET ... — нельзя. CTE — read-only «view» внутри запроса. Меняй настоящие таблицы.

4. Циклические ссылки между CTE. WITH a AS (SELECT * FROM b), b AS (SELECT * FROM a) — ошибка. CTE объявляются последовательно, каждая может ссылаться на предыдущие, но не на следующие.

5. CTE для очень короткого запроса. Иногда новички оборачивают всё в WITH x AS (...) SELECT * FROM x;. Если основной запрос — это просто SELECT * FROM x, CTE не нужен, пиши тело как основной запрос.

6. Думают, что CTE кешируется между запросами. Нет — CTE живёт только внутри одного WITH … SELECT … statement. После выполнения исчезает. Если нужен «общий» промежуточный результат на несколько запросов — это уже VIEW или временная таблица.

Мини-резюме

• WITH имя AS (...) — даёт подзапросу имя и делает запрос читаемее.
• Несколько CTE — через запятую: WITH a AS (...), b AS (...) SELECT ....
• CTE — read-only view внутри одного statement. После выполнения исчезает.
• В Postgres CTE может содержать INSERT/UPDATE/DELETE … RETURNING для атомарных мульти-операций.
• В современном Postgres CTE inline'ится оптимизатором — производительности обычно не теряет.
• Используй CTE, когда логика разбивается на шаги или один и тот же подзапрос нужен несколько раз.