9 апреля 2026 г.

CTE vs временная таблица — когда что использовать

Коротко

CTE (WITH) — именованный подзапрос внутри одного запроса. Временная таблица (CREATE TEMP TABLE) — реальная таблица, живущая до конца сессии. Правило: CTE — для читаемости и декомпозиции одного запроса. Временная таблица — когда промежуточный результат нужен в нескольких запросах или когда CTE работает медленно. На собеседованиях спрашивают разницу, плюсы-минусы и когда что выбрать.

Быстрое сравнение

Критерий CTE (WITH) Временная таблица
Создание Внутри запроса Отдельная команда
Область видимости Один запрос Вся сессия
Материализация Может не материализоваться Всегда на диске/в памяти
Индексы Нет Можно создать
Повторное использование Нет (пересчитывается) Да
Читаемость Выше Ниже (несколько команд)
Производительность Зависит от оптимизатора Предсказуемая
Статистика Нет Есть (ANALYZE)

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

Декомпозиция сложного запроса

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
),
growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
        ROUND(
            (revenue - LAG(revenue) OVER (ORDER BY month))::NUMERIC
            / LAG(revenue) OVER (ORDER BY month) * 100, 1
        ) AS growth_pct
    FROM monthly_revenue
)
SELECT * FROM growth
WHERE growth_pct IS NOT NULL
ORDER BY month;

CTE разбивает запрос на логические блоки. Каждый блок — понятный шаг. Читать проще, чем вложенные подзапросы.

Когда CTE лучше

  • Один запрос — промежуточный результат нужен только внутри этого запроса
  • Читаемость — декомпозиция сложной логики на именованные шаги
  • Рекурсия — рекурсивные CTE не заменить временной таблицей
  • Ad hoc анализ — быстро написать и выполнить один запрос
  • Без DDL-прав — иногда нет прав на CREATE TABLE

Временная таблица — когда использовать

Промежуточный результат для нескольких запросов

-- Шаг 1: создать витрину
CREATE TEMP TABLE active_users AS
SELECT
    user_id,
    COUNT(*) AS sessions,
    MAX(event_date) AS last_active
FROM user_events
WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id
HAVING COUNT(*) >= 5;

-- Шаг 2: использовать в одном запросе
SELECT
    a.user_id,
    a.sessions,
    SUM(o.amount) AS revenue
FROM active_users a
JOIN orders o ON a.user_id = o.user_id
GROUP BY a.user_id, a.sessions;

-- Шаг 3: использовать в другом запросе
SELECT segment, COUNT(*) AS cnt
FROM users u
JOIN active_users a ON u.user_id = a.user_id
GROUP BY segment;

CTE пересчитывался бы в каждом запросе. Временная таблица — один раз.

Оптимизация производительности

-- Медленно: CTE пересчитывается (в некоторых СУБД)
WITH big_cte AS (
    SELECT ... -- тяжёлый запрос с JOIN 5 таблиц
)
SELECT * FROM big_cte WHERE ...
UNION ALL
SELECT * FROM big_cte WHERE ...;

-- Быстрее: временная таблица + индекс
CREATE TEMP TABLE big_result AS
SELECT ... -- тот же тяжёлый запрос;

CREATE INDEX idx_big_result ON big_result(user_id);

SELECT * FROM big_result WHERE ...
UNION ALL
SELECT * FROM big_result WHERE ...;

Когда временная таблица лучше

  • Несколько запросов к одному промежуточному результату
  • Индексы — можно создать индекс для ускорения
  • Большие данные — предсказуемая материализация
  • Отладка — можно проверить промежуточный результат: SELECT * FROM temp_table
  • Статистика — ANALYZE temp_table помогает оптимизатору

Материализация CTE

Ключевое различие — как СУБД обрабатывает CTE:

PostgreSQL 12+: CTE по умолчанию инлайнится (не материализуется). Это значит, что оптимизатор может «развернуть» CTE в основной запрос и оптимизировать целиком.

-- Принудительная материализация (PostgreSQL 12+)
WITH cte AS MATERIALIZED (
    SELECT ... -- тяжёлый запрос
)
SELECT * FROM cte WHERE ...;

-- Принудительный инлайн
WITH cte AS NOT MATERIALIZED (
    SELECT ... -- лёгкий запрос
)
SELECT * FROM cte WHERE ...;

MySQL: CTE может материализоваться или инлайниться — решает оптимизатор.

Временная таблица: всегда материализуется. Данные записываются на диск (или в память).

Практический пример: когда CTE медленнее

-- CTE: подзапрос выполняется дважды (если не материализуется)
WITH user_stats AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
)
SELECT * FROM user_stats WHERE total > 100000
UNION ALL
SELECT * FROM user_stats WHERE total BETWEEN 50000 AND 100000;

Если СУБД инлайнит CTE — два сканирования таблицы orders. С AS MATERIALIZED или временной таблицей — одно.

-- Временная таблица: один расчёт
CREATE TEMP TABLE user_stats AS
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id;

SELECT * FROM user_stats WHERE total > 100000
UNION ALL
SELECT * FROM user_stats WHERE total BETWEEN 50000 AND 100000;

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

-- Подзапрос: компактно, но нечитаемо при вложенности
SELECT * FROM (
    SELECT user_id, SUM(amount) AS total
    FROM orders GROUP BY user_id
) sub WHERE total > 100000;

-- CTE: читаемо, один запрос
WITH user_totals AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders GROUP BY user_id
)
SELECT * FROM user_totals WHERE total > 100000;

-- Временная таблица: переиспользуемо
CREATE TEMP TABLE user_totals AS ...;
SELECT * FROM user_totals WHERE ...;
SELECT * FROM user_totals WHERE ...;

Правило: подзапрос — для простых случаев. CTE — для сложных одиночных запросов. Временная таблица — для многошаговых скриптов.

Типичные ошибки

CTE для всего. CTE не всегда бесплатен. Если CTE используется несколько раз и не материализуется — запрос может быть медленным.

Временная таблица для одного запроса. Если промежуточный результат нужен один раз — CTE проще и чище. Не нужно CREATE/DROP.

Забыли DROP. Временные таблицы живут до конца сессии. При повторном запуске скрипта CREATE TEMP TABLE может выдать ошибку «таблица уже существует». Решение: DROP TABLE IF EXISTS temp_name; или CREATE TEMP TABLE IF NOT EXISTS.

Нет индексов на временной таблице. Если к временной таблице идёт JOIN по user_id — создайте индекс. Без индекса JOIN будет sequential scan.

Вопросы с собеседований

-- Чем CTE отличается от временной таблицы? -- CTE — именованный подзапрос внутри одного запроса, не материализуется (обычно). Временная таблица — реальная таблица в памяти/на диске, живёт всю сессию, поддерживает индексы.

-- Когда CTE лучше подзапроса? -- Когда CTE используется несколько раз в одном запросе, или когда вложенность подзапросов снижает читаемость. Также для рекурсии — подзапросы рекурсию не поддерживают.

-- Когда стоит использовать временную таблицу вместо CTE? -- Когда результат нужен в нескольких запросах. Когда нужны индексы. Когда CTE пересчитывается и тормозит.

-- Материализуется ли CTE в PostgreSQL? -- С PostgreSQL 12 — по умолчанию нет (инлайнится). Можно принудительно: WITH cte AS MATERIALIZED (...). До PostgreSQL 12 — всегда материализовался.

Потренируйтесь решать задачи — откройте тренажёр с 1500+ вопросами для подготовки к собеседованиям аналитиков.

FAQ

CTE vs VIEW — в чём разница?

CTE существует только внутри одного запроса. VIEW — сохранённый запрос, доступен из любого запроса в любой момент. CTE не нужно создавать заранее, VIEW — нужно (CREATE VIEW). Подробнее — в гайде по VIEW.

Можно ли создать индекс на CTE?

Нет. CTE — виртуальная конструкция, не хранится физически. Индексы можно создать только на реальных таблицах (включая временные).

Что быстрее — CTE или подзапрос?

В большинстве СУБД — одинаково. Оптимизатор может инлайнить CTE в подзапрос. Разница — в читаемости, не в производительности.

Как тренироваться

CTE и подзапросы — must-have для SQL-собеседований. Задачи на CTE, подзапросы и оконные функции — в тренажёре Карьерник. Больше вопросов — в разделе с примерами.

Тренироваться в Telegram