Что такое CTE (Common Table Expression) в PostgreSQL?
CTE в PostgreSQL
Common Table Expression (CTE) — временный именованный подзапрос, объявляемый перед основным запросом с ключевым словом WITH. Результат CTE существует только в рамках выполнения текущего запроса и не сохраняется в базе данных.
Базовый синтаксис
WITH имя_cte AS (
SELECT ...
)
SELECT * FROM имя_cte;
Можно объявить несколько CTE через запятую — последующие могут ссылаться на предыдущие:
WITH
первый AS (SELECT ...),
второй AS (SELECT ... FROM первый)
SELECT * FROM второй;
Виды CTE
Обычные (нерекурсивные) CTE используются для декомпозиции сложных запросов. Устраняют дублирование подзапросов и делают код значительно читаемее.
Рекурсивные CTE объявляются с модификатором RECURSIVE и позволяют обходить иерархические структуры: деревья, графы, цепочки зависимостей. Состоят из двух частей, объединённых UNION ALL: базового случая (якорь) и рекурсивного шага.
WITH RECURSIVE дерево AS (
-- базовый случай: корень иерархии
SELECT id, название, parent_id, 1 AS уровень
FROM категории
WHERE parent_id IS NULL
UNION ALL
-- рекурсивный шаг: дочерние узлы
SELECT к.id, к.название, к.parent_id, д.уровень + 1
FROM категории к
INNER JOIN дерево д ON к.parent_id = д.id
)
SELECT * FROM дерево ORDER BY уровень;
Записываемые CTE (Writable CTE)
CTE могут содержать не только SELECT, но и INSERT, UPDATE, DELETE. Это позволяет выполнять несколько модифицирующих операций атомарно в рамках одного запроса.
WITH удалённые AS (
DELETE FROM заказы
WHERE статус = 'отменён'
RETURNING *
)
INSERT INTO архив_заказов SELECT * FROM удалённые;
CTE против подзапросов
| Критерий | CTE | Подзапрос |
|---|---|---|
| Читаемость | Высокая — имя объявляется один раз | Ниже при вложенности |
| Повторное использование | Да | Нужно дублировать |
| Рекурсия | Поддерживается | Не поддерживается |
Материализация и производительность
До PostgreSQL 12 CTE всегда материализовались: результат вычислялся один раз и сохранялся в памяти, что мешало оптимизатору применять push-down предикатов. Начиная с PostgreSQL 12 нерекурсивные немодифицирующие CTE могут инлайниться (встраиваться) в основной запрос, давая оптимизатору больше свободы.
Поведение можно задать явно:
-- Запретить инлайнинг — CTE вычислится один раз
WITH активные AS MATERIALIZED (
SELECT * FROM пользователи WHERE активен = true
)
SELECT * FROM активные WHERE город = 'Москва';
-- Разрешить инлайнинг явно
WITH активные AS NOT MATERIALIZED (
SELECT * FROM пользователи WHERE активен = true
)
SELECT * FROM активные WHERE город = 'Москва';
Материализация выгодна, когда CTE используется несколько раз или содержит дорогостоящее вычисление с побочным эффектом.
Что хочет услышать интервьюер
Чёткое определение CTE как временного именованного подзапроса в рамках текущего запроса
Знание синтаксиса WITH, умение объявлять несколько CTE и ссылаться между ними
Понимание разницы между обычными и рекурсивными CTE, знание структуры рекурсивного (якорь + UNION ALL + рекурсивный шаг)
Осведомлённость об изменении поведения материализации в PostgreSQL 12 и модификаторах MATERIALIZED / NOT MATERIALIZED
Умение объяснить преимущества CTE перед подзапросами и назвать сценарии, где предпочтительнее тот или иной подход
Пример: Нерекурсивный CTE
-- Нерекурсивный CTE: топ-10 покупателей по сумме заказов за 2024 год
WITH итоги_заказов AS (
SELECT
покупатель_id,
SUM(сумма) AS общая_сумма,
COUNT(*) AS количество_заказов
FROM заказы
WHERE дата >= '2024-01-01'
GROUP BY покупатель_id
)
SELECT
п.имя,
п.email,
и.общая_сумма,
и.количество_заказов
FROM покупатели п
INNER JOIN итоги_заказов и ON п.id = и.покупатель_id
ORDER BY и.общая_сумма DESC
LIMIT 10;
Пример: Рекурсивный CTE
-- Рекурсивный CTE: построение полного пути в дереве категорий
WITH RECURSIVE иерархия AS (
-- якорь: корневые категории без родителя
SELECT
id,
название,
parent_id,
0 AS глубина,
название::text AS путь
FROM категории
WHERE parent_id IS NULL
UNION ALL
-- рекурсивный шаг: присоединяем дочерние узлы
SELECT
к.id,
к.название,
к.parent_id,
и.глубина + 1,
и.путь || ' > ' || к.название
FROM категории к
INNER JOIN иерархия и ON к.parent_id = и.id
)
SELECT
REPEAT(' ', глубина) || название AS отображение,
путь
FROM иерархия
ORDER BY путь;
Пример: Записываемый CTE (Writable CTE)
-- Записываемый CTE: атомарный перенос старых отменённых заказов в архив
WITH перенесённые AS (
DELETE FROM заказы
WHERE статус = 'отменён'
AND дата < NOW() - INTERVAL '1 year'
RETURNING *
)
INSERT INTO архив_заказов
SELECT *, NOW() AS дата_архивации
FROM перенесённые
RETURNING id;
Типичные ошибки
Считают, что CTE сохраняется в базе данных — на самом деле он живёт только в рамках одного запроса
Не знают рекурсивных CTE или путаются в структуре: забывают якорь, пишут UNION вместо UNION ALL, не указывают RECURSIVE
Думают, что CTE всегда быстрее подзапросов — не учитывают, что материализация до PG 12 могла блокировать оптимизации
Не знают о записываемых CTE (Writable CTE) с INSERT / UPDATE / DELETE и их атомарности
Путают CTE с временными таблицами (CREATE TEMP TABLE), не понимая принципиальной разницы в области видимости и времени жизни
AI-тренажеры
4.7
Практика в студии
