SQL CTE: как разделить сложный запрос на понятные шаги

Коротко

CTE начинается с WITH, получает имя и содержит обычный SELECT. Следующий CTE может использовать предыдущий, а финальный SELECT возвращает итог. Это способ организовать логику запроса, а не отдельная метрика и не гарантия высокой производительности.

• Один CTE — один понятный аналитический шаг.
• Сначала зафиксируй grain каждого промежуточного результата.
• Проверяй CTE отдельно до того, как добавлять следующий JOIN.
• Не прячь в одном блоке фильтрацию, агрегацию и бизнес-правила без необходимости.
• CTE улучшает читаемость, но не исправляет неверное определение метрики.

Что такое CTE на простом примере

Представим запрос “найди каналы, которые привели активных пользователей и хотя бы одного плательщика”. Его можно написать одним длинным SELECT, но тогда сложно понять, где именно появилась ошибка. В CTE сначала создаём список активных пользователей, затем агрегируем платежи, а финальным SELECT соединяем две уже понятные таблицы.

Первый CTE: собрать активных пользователей

В таблице events одна строка означает одно событие, поэтому сначала убираем повторы до зерна “один пользователь”. Это защищает следующие шаги от повторного подсчёта одного и того же человека.

with active_users as (
  select distinct
    user_id
  from events
  where event_name = 'core_action'
    and event_date >= date '2026-06-01'
    and event_date < date '2026-07-01'
)
select *
from active_users
order by user_id;

Несколько CTE как последовательность

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

В active_users одна строка — один пользователь. После JOIN с users это зерно сохраняется, поэтому COUNT(*) здесь безопасен. Если бы первый CTE оставлял все события, понадобился бы COUNT(DISTINCT a.user_id) или предварительная дедупликация.

with active_users as (
  select distinct
    user_id
  from events
  where event_name = 'core_action'
    and event_date >= date '2026-06-01'
    and event_date < date '2026-07-01'
),
active_by_channel as (
  select
    u.channel,
    count(*) as active_users
  from active_users as a
  join users as u using (user_id)
  group by u.channel
)
select
  channel,
  active_users
from active_by_channel
order by active_users desc, channel;

CTE для сравнения двух аудиторий

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

with active_by_channel as (
  select
    u.channel,
    count(distinct e.user_id) as active_users
  from events as e
  join users as u using (user_id)
  where e.event_name = 'core_action'
  group by u.channel
),
paying_by_channel as (
  select
    u.channel,
    count(distinct p.user_id) as paying_users
  from payments as p
  join users as u using (user_id)
  group by u.channel
)
select
  a.channel,
  a.active_users,
  coalesce(p.paying_users, 0) as paying_users,
  round(
    coalesce(p.paying_users, 0)::numeric / nullif(a.active_users, 0),
    3
  ) as payer_rate
from active_by_channel as a
left join paying_by_channel as p using (channel)
order by payer_rate desc nulls last;

Когда CTE становится слишком длинным

Именованные шаги не означают, что запрос можно расширять бесконечно. Если CTE превращаются в двадцать блоков, а один и тот же слой используется в нескольких отчётах, стоит вынести стабильную логику в view или отдельную модель данных. Временный аналитический расчёт и повторно используемый слой — разные задачи.

• Дай CTE имя по смыслу результата: active_users, а не step_1.
• Не дублируй один и тот же фильтр в пяти местах без причины.
• Проверяй промежуточный размер после каждого JOIN.
• Не называй CTE “финальной метрикой”, если он ещё содержит строки событий.

CTE как контракт между шагами

Хороший CTE не только сокращает длинный запрос, но и фиксирует договорённость о зерне. В комментарии или имени результата должно быть понятно, одна ли строка — пользователь, событие, заказ или канал. Если следующий шаг ожидает одну строку на user_id, проверь это через count(*) и count(distinct user_id) до JOIN.

Разделяй технические и бизнесовые шаги. filtered_events отвечает за период и событие, active_users — за дедупликацию, active_by_channel — за агрегацию. Когда в одном CTE одновременно меняются дата, сегмент и знаменатель, ошибку становится трудно локализовать.

Перед публикацией временно замени финальный SELECT на select count(*) или выведи первые строки каждого слоя. Это быстрый способ убедиться, что CTE возвращает ожидаемую форму. Проверка промежуточного результата — не лишний шаг, а основная причина использовать WITH.

Когда CTE стоит заменить моделью

CTE удобен для разового анализа и учебного запроса. Если один и тот же слой активных пользователей повторяется в десяти дашбордах, копирование WITH создаёт риск расхождения. Вынеси стабильную логику в view, dbt-модель или таблицу с владельцем и документацией. Тогда статья и тренажёр могут объяснять шаги, а production-отчёт использует единый слой.

Длинный CTE-запрос также может быть сигналом, что отсутствует промежуточная модель данных. Не оптимизируй его только перестановкой запятых. Посмотри на повторяющиеся фильтры, одинаковые JOIN и расчёты, которые можно материализовать по расписанию.

Производительность зависит от СУБД и плана выполнения. Не делай универсальный вывод, что CTE всегда медленнее или всегда быстрее подзапроса. Проверь EXPLAIN, объём данных и повторное использование результата, но сначала сохрани корректность зерна.

Мини-практика и следующий шаг

Собери CTE active_users из таблицы events, присоедини users и посчитай пользователей по каналам. Затем добавь второй слой paying_users и вычисли payer rate. Если результат будет неожиданным, проверяй каждый CTE отдельно — это и есть главная практическая ценность конструкции WITH.