SQL CTE: что такое WITH и зачем нужны общие табличные выражения

CTE (Common Table Expression) — это именованный подзапрос, который определяется в начале запроса через WITH. Он делает сложные запросы читаемыми, а иногда — единственно возможными (рекурсия).

Синтаксис

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

Можно определить несколько CTE через запятую:

WITH
  cte1 AS (SELECT ...),
  cte2 AS (SELECT ... FROM cte1)
SELECT * FROM cte2;

Зачем CTE, если есть подзапросы?

Рассмотрим задачу: найти клиентов, чья сумма покупок выше средней.

С подзапросом:

SELECT customer_id, total
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE total > (
    SELECT AVG(total)
    FROM (
        SELECT SUM(amount) AS total
        FROM orders
        GROUP BY customer_id
    ) AS avg_totals
);

С CTE:

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
),
avg_total AS (
    SELECT AVG(total) AS avg_val FROM customer_totals
)
SELECT c.customer_id, c.total
FROM customer_totals c
CROSS JOIN avg_total a   -- avg_total содержит одну строку, CROSS JOIN безопасен
WHERE c.total > a.avg_val;

CTE-версия читается сверху вниз, как текст. Каждый блок — один смысловой шаг. CROSS JOIN здесь — намеренное декартово произведение: avg_total содержит ровно одну строку со средним значением, и мы «прикладываем» её к каждой строке customer_totals как константу для сравнения.

Практические примеры

Ранжирование внутри групп

WITH ranked AS (
    SELECT
        employee_id,
        department_id,
        salary,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT * FROM ranked WHERE rnk = 1;

Многоступенчатая аналитика

WITH
  daily_sales AS (
      SELECT DATE(created_at) AS day, SUM(amount) AS revenue
      FROM orders
      WHERE status = 'paid'
      GROUP BY DATE(created_at)
  ),
  weekly_avg AS (
      SELECT
          day,
          revenue,
          AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
      FROM daily_sales
  )
SELECT * FROM weekly_avg
ORDER BY day;

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

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

WITH RECURSIVE subordinates AS (
    -- Базовый случай: начальник
    SELECT employee_id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Рекурсивный шаг: подчинённые
    SELECT e.employee_id, e.name, e.manager_id, s.level + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates ORDER BY level, name;

Генерация последовательности дат

WITH RECURSIVE dates AS (
    SELECT '2026-01-01'::date AS day
    UNION ALL
    SELECT day + 1 FROM dates WHERE day < '2026-01-31'
)
SELECT day FROM dates;

Это полезно для отчётов — заполнить «дыры» в данных нулями.

Важно: всегда следите за условием выхода из рекурсии (WHERE day < '2026-01-31' или ON e.manager_id = s.employee_id). Некорректный JOIN или пропущенное условие приведут к бесконечному циклу. В PostgreSQL по умолчанию ограничение — 32 768 итераций, после чего запрос упадёт с ошибкой. Не полагайтесь на это ограничение как на защиту — проверяйте логику заранее.

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

Про производительность: в PostgreSQL CTE до версии 12 был «забором оптимизатора» — он всегда материализовался. С версии 12 оптимизатор сам решает, встраивать CTE или нет. Можно явно указать: WITH cte AS MATERIALIZED (...) или WITH cte AS NOT MATERIALIZED (...).

Правило выбора: если CTE тяжёлое, но используется в запросе один раз — разрешите оптимизатору встроить его (NOT MATERIALIZED). Если CTE лёгкое, но на него ссылаются несколько раз — материализуйте (MATERIALIZED), чтобы не выполнять одно и то же дважды.

Про отладку: в отличие от временных таблиц, CTE нельзя выполнить и проверить отдельно — только в составе всего запроса. Если логика сложная, удобнее на время заменить CTE на CREATE TEMP TABLE, проверить данные, и потом вернуть обратно.

Когда использовать CTE

• Когда запрос содержит 3+ уровня вложенности — декомпозируй в CTE
• Когда один и тот же подзапрос нужен несколько раз в запросе
• Когда нужна рекурсия (иерархии, деревья, последовательности)
• Когда хочешь объяснить запрос коллеге — CTE читается как псевдокод