CTE в SQL: как использовать WITH

Зачем аналитику CTE

Реальные аналитические запросы редко бывают простыми. Нужно подготовить данные, отфильтровать, агрегировать, сджойнить с другой таблицей, снова агрегировать. Без CTE всё это превращается в нечитаемую кашу из вложенных подзапросов. С CTE — в последовательность именованных шагов, где каждый делает одну понятную операцию.

На собеседовании по SQL CTE встречается постоянно. Интервьюеры ожидают, что кандидат уровня middle и выше будет использовать WITH для декомпозиции сложных задач. Это не только про правильный ответ — это про читаемость и культуру написания SQL.

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

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

CTE объявляется ключевым словом WITH перед основным запросом. Внутри скобок — обычный SELECT. Результат получает имя, которое дальше используется как временная таблица.

Пример — средний чек по категориям товаров, только для категорий с выручкой больше миллиона:

WITH category_stats AS (
    SELECT
        category,
        COUNT(*) AS order_count,
        SUM(amount) AS revenue,
        AVG(amount) AS avg_check
    FROM orders
    GROUP BY category
)
SELECT *
FROM category_stats
WHERE revenue > 1000000
ORDER BY avg_check DESC

Без CTE это был бы подзапрос в FROM. С CTE — два чётких шага: сначала считаем статистику, потом фильтруем.

CTE vs подзапрос: когда что использовать

Подзапрос и CTE часто дают одинаковый результат. Разница — в читаемости и удобстве.

Подзапрос:

SELECT *
FROM (
    SELECT
        user_id,
        COUNT(*) AS sessions,
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
    FROM user_sessions
    WHERE session_date >= '2025-01-01'
    GROUP BY user_id
) ranked
WHERE rn <= 10

Тот же запрос через CTE:

WITH ranked AS (
    SELECT
        user_id,
        COUNT(*) AS sessions,
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
    FROM user_sessions
    WHERE session_date >= '2025-01-01'
    GROUP BY user_id
)
SELECT *
FROM ranked
WHERE rn <= 10

Для одного уровня вложенности разница невелика. Но как только появляется второй и третий уровень — подзапросы становятся нечитаемыми, а CTE сохраняет линейную структуру.

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

• Простое условие в WHERE: WHERE user_id IN (SELECT user_id FROM vip_users)
• Одноразовое использование, 1–3 строки

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

• Несколько шагов трансформации данных
• Один и тот же промежуточный результат используется дважды
• Нужна декомпозиция для отладки — каждый CTE можно запустить отдельно

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

Главная сила CTE — возможность объявить несколько именованных блоков и строить каждый следующий на основе предыдущего. Второе ключевое слово WITH не нужно — блоки перечисляются через запятую.

WITH daily_orders AS (
    SELECT
        DATE_TRUNC('day', order_date) AS dt,
        COUNT(*) AS order_count,
        SUM(amount) AS revenue
    FROM orders
    WHERE order_date >= '2025-01-01'
    GROUP BY DATE_TRUNC('day', order_date)
),
weekly_avg AS (
    SELECT
        dt,
        order_count,
        revenue,
        AVG(revenue) OVER (
            ORDER BY dt
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS revenue_7d_avg
    FROM daily_orders
)
SELECT
    dt,
    order_count,
    revenue,
    ROUND(revenue_7d_avg, 2) AS revenue_7d_avg
FROM weekly_avg
ORDER BY dt

Три шага: агрегация по дням, расчёт скользящего среднего, итоговый вывод. Каждый CTE занимается одной задачей. Если что-то сломалось — легко понять, на каком шаге.

Пошаговая трансформация: воронка конверсии

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

WITH registrations AS (
    SELECT user_id, created_at
    FROM users
    WHERE created_at >= '2025-01-01'
      AND created_at < '2025-02-01'
),
activated AS (
    SELECT DISTINCT r.user_id
    FROM registrations r
    JOIN user_actions a ON a.user_id = r.user_id
    WHERE a.action = 'complete_onboarding'
),
first_purchase AS (
    SELECT DISTINCT r.user_id
    FROM registrations r
    JOIN orders o ON o.user_id = r.user_id
    WHERE o.order_date <= r.created_at + INTERVAL '7 days'
),
funnel AS (
    SELECT
        (SELECT COUNT(*) FROM registrations) AS step_1_registered,
        (SELECT COUNT(*) FROM activated) AS step_2_activated,
        (SELECT COUNT(*) FROM first_purchase) AS step_3_purchased
)
SELECT
    step_1_registered,
    step_2_activated,
    ROUND(100.0 * step_2_activated / step_1_registered, 1) AS activation_pct,
    step_3_purchased,
    ROUND(100.0 * step_3_purchased / step_1_registered, 1) AS purchase_pct
FROM funnel

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

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

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

Синтаксис:

WITH RECURSIVE имя_cte AS (
    -- базовый случай (якорь)
    SELECT ...
    UNION ALL
    -- рекурсивная часть — ссылается на имя_cte
    SELECT ...
    FROM имя_cte
    JOIN ...
)
SELECT * FROM имя_cte

Классический пример — дерево сотрудников. Таблица employees содержит столбец manager_id, ссылающийся на id руководителя.

WITH RECURSIVE org_tree AS (
    -- Якорь: генеральный директор (manager_id IS NULL)
    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,
        t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT
    level,
    name,
    manager_id
FROM org_tree
ORDER BY level, name

Результат — все сотрудники с уровнем вложенности в иерархии. Генеральный директор — level 1, его прямые подчинённые — level 2, и так далее.

Рекурсивные CTE спрашивают на собеседованиях уровня middle+. Если вы идёте на позицию junior-аналитика, достаточно знать, что такая конструкция существует. Если на middle — нужно уметь написать.

CTE vs временная таблица

CTE и временная таблица (CREATE TEMP TABLE) решают похожую задачу — дать имя промежуточному результату. Но между ними есть принципиальные отличия.

В PostgreSQL 12+ оптимизатор может автоматически «развернуть» (inline) CTE, если он используется один раз — то есть CTE не создаёт накладных расходов. Если нужно принудительно материализовать CTE, используйте AS MATERIALIZED (...).

На собеседованиях этот вопрос формулируют так: «Когда вы выберете CTE, а когда временную таблицу?». Ответ: CTE — для структурирования одного запроса; временная таблица — когда промежуточный результат тяжёлый и нужен в нескольких последующих запросах.

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

1. Что такое CTE и зачем он нужен?

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

2. CTE материализуется или нет?

Зависит от СУБД. В PostgreSQL до версии 12 CTE всегда материализовался — оптимизатор не мог «заглянуть внутрь». С версии 12 CTE, используемый один раз и не являющийся рекурсивным, автоматически инлайнится. В BigQuery CTE всегда инлайнится. Чтобы принудительно материализовать — AS MATERIALIZED (...), чтобы инлайнить — AS NOT MATERIALIZED (...).

3. Найдите пользователей, у которых каждый заказ превышает их собственный средний чек.

WITH user_avg AS (
    SELECT
        user_id,
        AVG(amount) AS avg_amount
    FROM orders
    GROUP BY user_id
),
below_avg AS (
    SELECT o.user_id
    FROM orders o
    JOIN user_avg u ON o.user_id = u.user_id
    WHERE o.amount <= u.avg_amount
)
SELECT DISTINCT user_id
FROM user_avg
WHERE user_id NOT IN (SELECT user_id FROM below_avg)

Два CTE: средний чек по пользователю, затем пользователи, у которых есть хотя бы один заказ ниже среднего. Финальный SELECT находит тех, кого нет в этом списке. Такая декомпозиция проще для чтения, чем вложенные подзапросы с коррелированными условиями.

4. Напишите рекурсивный CTE: все подчинённые конкретного руководителя.

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id = 42  -- id руководителя

    UNION ALL

    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates

Якорь — прямые подчинённые сотрудника 42. Рекурсия — подчинённые подчинённых, и так далее, пока не закончатся уровни. На собеседовании обязательно спросят: «Что будет, если в данных есть цикл?». Ответ: бесконечная рекурсия. Защита — добавить LIMIT или максимальную глубину через счётчик уровней.

5. Чем CTE отличается от подзапроса?

Функционально — почти ничем: оба дают промежуточный результат. Отличия: CTE имеет имя и может использоваться в запросе несколько раз. Подзапрос нельзя переиспользовать — его придётся дублировать. CTE делает запрос линейным (сверху вниз), подзапросы — вложенным (изнутри наружу). При нескольких уровнях трансформации CTE существенно выигрывает в читаемости.

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

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

Тренажёр Карьерник содержит задачи, где нужно использовать CTE: воронки, рекурсивные иерархии, пошаговые трансформации данных. Можно тренироваться по 15 минут в день в Telegram — этого достаточно, чтобы за неделю перестать спотыкаться на декомпозиции запросов.

Больше материалов по SQL — в разделе подготовки. Если хотите разобрать конкретные конструкции, посмотрите шпаргалки по JOIN и оконным функциям.

FAQ

Можно ли использовать CTE в INSERT, UPDATE, DELETE?

Да. WITH работает не только с SELECT. В PostgreSQL можно написать WITH ... INSERT INTO, WITH ... UPDATE, WITH ... DELETE. Это удобно, когда нужно вычислить промежуточный результат и на его основе обновить данные. Например, деактивировать пользователей, не заходивших 90 дней: CTE считает таких пользователей, DELETE удаляет (или UPDATE ставит флаг).

Влияет ли CTE на производительность запроса?

В большинстве случаев — нет. Современные оптимизаторы (PostgreSQL 12+, BigQuery, SQL Server) инлайнят CTE, если он используется один раз. Запрос с CTE и эквивалентный запрос с подзапросом дадут одинаковый план выполнения. Исключение — если CTE используется несколько раз: тогда его выгодно материализовать, чтобы не вычислять повторно. Но если промежуточный результат большой и нужен один раз — инлайн лучше.

Поддерживается ли CTE в MySQL?

Да, начиная с MySQL 8.0 (2018). Рекурсивные CTE тоже поддерживаются. В MySQL 5.7 и ниже WITH недоступен — вместо него используют подзапросы или временные таблицы. На собеседовании обычно пишут на PostgreSQL или не уточняют СУБД, поэтому CTE можно использовать свободно.