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