middle

Что такое CTE (Common Table Expression)?

CTE (Common Table Expression) — именованное временное результирующее множество, определяемое с помощью ключевого слова WITH. CTE существует только в рамках одного запроса и делает сложные запросы более читаемыми, позволяя разбить их на логические блоки.

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

Пример

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;

Пример: средняя зарплата по отделам

Пример

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT
    e.name, e.department, e.salary,
    d.avg_salary,
    e.salary - d.avg_salary AS diff_from_avg
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;

Несколько CTE в одном запросе

Пример с несколькими CTE

WITH
active_users AS (
    SELECT user_id, name
    FROM users
    WHERE status = 'active'
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY user_id
)
SELECT
    au.name,
    COALESCE(uo.order_count, 0) AS order_count,
    COALESCE(uo.total_amount, 0) AS total_amount
FROM active_users au
LEFT JOIN user_orders uo ON au.user_id = uo.user_id
ORDER BY total_amount DESC;

Рекурсивные CTE

Рекурсивные CTE позволяют выполнять иерархические запросы — обход деревьев, графов, организационных структур.

Пример

WITH RECURSIVE cte_name AS (
    -- Базовый (якорный) запрос
    SELECT ...
    UNION ALL
    -- Рекурсивный запрос (ссылается на cte_name)
    SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;

Пример: организационная иерархия

WITH RECURSIVE org_tree AS (
    -- Якорь: генеральный директор (нет руководителя)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Рекурсия: подчинённые каждого руководителя
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT
    REPEAT('  ', level - 1) || name AS org_chart,
    level
FROM org_tree
ORDER BY level, name;

Пример: генерация ряда дат

WITH RECURSIVE dates AS (
    SELECT DATE '2026-01-01' AS dt
    UNION ALL
    SELECT dt + INTERVAL '1 day'
    FROM dates
    WHERE dt < DATE '2026-12-31'
)
SELECT dt FROM dates;

CTE vs подзапрос vs временная таблица

Характеристика	CTE	Подзапрос	Временная таблица
Область видимости	Один запрос	Внутри внешнего запроса	Сессия / транзакция
Повторное использование	Можно ссылаться несколько раз в одном запросе	Нужно дублировать	Можно в разных запросах
Рекурсия	Поддерживается	Нет	Нет (нужен цикл)
Индексы	Нет	Нет	Можно создать
Материализация	Зависит от оптимизатора	Зависит от оптимизатора	Всегда материализована
Читаемость	Высокая	Низкая при вложенности	Средняя

Важное

В PostgreSQL CTE может быть материализованным или нет — оптимизатор решает сам. Можно управлять явно: WITH cte AS MATERIALIZED (...) или WITH cte AS NOT MATERIALIZED (...)
Рекурсивные CTE обязательно должны иметь условие завершения, иначе запрос зациклится
CTE может использоваться в SELECT, INSERT, UPDATE, DELETE
В PostgreSQL рекурсивный CTE требует ключевого слова RECURSIVE, даже если только один из нескольких CTE является рекурсивным

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

Забывают ключевое слово RECURSIVE для рекурсивных CTE в PostgreSQL
Отсутствие условия завершения рекурсии — приводит к бесконечному циклу
Предполагают, что CTE всегда материализуется (как в PostgreSQL до 12) — в новых версиях оптимизатор может «встроить» (inline) CTE
Используют CTE там, где достаточно простого подзапроса — избыточное усложнение

На собеседовании: объясните, что CTE — это именованный подзапрос для читаемости и повторного использования. Обязательно упомяните рекурсивные CTE и приведите пример (обход дерева). Частая ошибка — не знать разницу между CTE и временной таблицей.

#sql