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

Коротко

CTE — это именованный подзапрос, объявленный перед основным запросом через WITH. По результату он ничем не отличается от подзапроса в FROM. Отличие в том, что у шага есть имя, шаги читаются сверху вниз, а каждый можно выполнить и посчитать отдельно.

• Синтаксис: WITH имя AS (запрос), следующее AS (запрос) SELECT …. Каждый следующий шаг видит все предыдущие, основной запрос видит все.
• CTE живёт в пределах одного запроса. Для другого запроса его не существует, и повторно использовать его можно только копированием.
• Имя шага должно называть зерно: first_payment — «одна строка — пользователь и дата его первой оплаты». Имена step1 и tmp не говорят ничего.
• У каждого шага есть контрольное число строк. В примере ниже: 4 613 → 3 661 → 912 → 527 → 4.
• Рекурсивный CTE (WITH RECURSIVE) строит календарь, в котором дни без событий не пропадают: у paid_search 60 дней с регистрациями из 90.
• В PostgreSQL с 12-й версии CTE, на который ссылаются один раз, встраивается в запрос и не мешает оптимизатору. Управлять этим можно через MATERIALIZED и NOT MATERIALIZED.

Синтаксис WITH на одном примере

Простейший CTE — это подзапрос из FROM, вынесенный наверх. В статье про подзапросы есть задача: сколько плательщиков принесли больше средней выручки на плательщика. Сначала нужна таблица «одна строка — человек и его сумма», затем порог по ней же. Через подзапросы таблицу пришлось бы написать дважды: один раз в FROM, второй — внутри порога. Через CTE она объявляется один раз и используется в двух местах.

Читается такой запрос как рецепт. per_user — промежуточная таблица из 912 строк. Основной запрос берёт её, сравнивает сумму каждого человека со средним по той же таблице и считает строки. Ответ — 359. Никаких скобок на третьем уровне, и понятно, по чему именно считается среднее: по людям, а не по платежам.

После WITH может идти несколько CTE через запятую, но ключевое слово WITH пишется один раз. Список колонок в скобках после имени необязателен: per_user(user_id, revenue) AS (…) переименует колонки подзапроса. Удобно, когда внутри стоят выражения без псевдонимов.

WITH per_user AS (
  SELECT user_id, sum(amount) AS revenue
  FROM payments
  GROUP BY user_id
)
SELECT count(*) AS above_average
FROM per_user
WHERE revenue > (SELECT avg(revenue) FROM per_user);

-- above_average = 359 из 912 плательщиков

Один расчёт в четыре шага: конверсия в оплату за 14 дней

Теперь задача из введения. База — SaaS-сервис из симулятора SQL-аналитика: 4 613 регистраций с 1 июня по 29 августа 2026 года, платежи по тарифам basic, pro и team, выгрузка закрыта 30 августа. Вопрос: какая доля пользователей каждого канала платит в первые 14 дней после регистрации.

Шаг cohort отбирает тех, у кого 14 дней уже прошли. Платежи в выгрузке есть по 30 августа, значит, полное окно есть у зарегистрированных по 16 августа включительно. Без этого шага канал с большой долей свежих регистраций выглядел бы хуже просто потому, что его пользователи ещё не успели заплатить.

Шаг first_payment сворачивает платежи до одной строки на человека. Это важно: в payments есть продления, и если соединить когорту с платежами напрямую, у плативших дважды получится две строки. Здесь соединение безопасно только благодаря тому, что справа уже одна строка на пользователя.

Шаг converted оставляет тех, чей первый платёж уложился в окно. Основной запрос соединяет когорту с конвертированными через LEFT JOIN, чтобы в знаменатель попали все пользователи когорты, включая не плативших, и считает долю по каналу.

WITH cohort AS (
  -- одна строка — пользователь, у которого 14 дней уже прошли
  SELECT user_id, channel, signup_date
  FROM users
  WHERE signup_date <= DATE '2026-08-16'
),
first_payment AS (
  -- одна строка — плательщик и дата его первой оплаты
  SELECT user_id, min(paid_at) AS first_paid_at
  FROM payments
  GROUP BY user_id
),
converted AS (
  -- одна строка — пользователь когорты, заплативший в окне
  SELECT c.user_id
  FROM cohort c
  JOIN first_payment f ON f.user_id = c.user_id
  WHERE f.first_paid_at < c.signup_date + 14
)
SELECT
  c.channel,
  count(*)                                      AS cohort_users,
  count(v.user_id)                              AS paid_14d,
  round(100.0 * count(v.user_id) / count(*), 1) AS conversion_14d
FROM cohort c
LEFT JOIN converted v ON v.user_id = c.user_id
GROUP BY c.channel
ORDER BY conversion_14d DESC;

Контрольное число для каждого шага

Главное, что даёт CTE, — возможность остановиться на любом шаге. Замените основной запрос на SELECT count(*) FROM first_payment — получите число строк этого шага, не трогая остальные. Ожидаемые числа стоит записать до того, как появится итог: тогда итог проверяется, а не принимается на веру.

В нашем расчёте цепочка такая. Из 4 613 пользователей в когорту попадают 3 661. В first_payment 912 строк — ровно столько различных user_id в платежах; если бы строк было 1 251, свёртка не сработала бы и продления просочились бы дальше. В converted 527 строк, и это число не может быть больше ни когорты, ни числа плательщиков. Итог — четыре строки, по одной на канал, и сумма paid_14d по ним снова должна дать 527.

Последняя проверка ловит самую частую ошибку многошаговых расчётов — размножение строк на соединении. Если сумма по каналам больше, чем строк в converted, где-то соединение один-ко-многим. Если меньше — где-то INNER JOIN выбросил строки, которые должны были остаться.

Что показал расчёт и чем он отличается от наивного

За 14 дней платят 21,3% пользователей referral, 16,2% organic, 11,0% partner и 6,2% paid_search. Если посчитать «плательщики за всё время / все пользователи», получится 26,8%, 23,5%, 17,0% и 8,5%. Порядок каналов тот же, а расстояния — другие. Referral опережает paid_search не в 3,2 раза, как в наивном расчёте, а в 3,4.

Разницу создаёт шаг cohort. Свежих регистраций, у которых окно ещё не закрылось, у каналов разная доля: у paid_search это 27,3%, у organic — 18,1%. Канал запущен 1 июля, и его пользователи в среднем моложе. Наивный расчёт смешивает людей с разным временем на решение, и сравнение между каналами перестаёт быть честным.

Отдельный шаг с отсечением делает это решение видимым. В одном большом запросе условие signup_date <= DATE '2026-08-16' легко потерять при правке. В CTE с именем cohort и комментарием «одна строка — пользователь, у которого 14 дней уже прошли» его потеря заметна сразу.

Имя CTE называет зерно, а не операцию

Имена шагов — единственная документация, которая гарантированно лежит рядом с кодом. step1, tmp, t2, joined сообщают, что с данными сделали, но не что получилось. first_payment отвечает на вопрос, который задаёт каждый читающий: что описывает одна строка. Если имя не придумывается, обычно шаг смешивает два зерна, и его стоит разделить.

Полезно держать одну короткую строку комментария над каждым шагом в форме «одна строка — …». Это та же фраза, которую стоит проговаривать перед любым агрегатом. В CTE она ещё и остаётся в коде: через месяц к запросу вернётся другой человек, и ему не придётся восстанавливать зерно по GROUP BY.

• Существительное во множественном числе для набора сущностей: payers, active_users.
• Составное имя, когда зерно составное: user_days — пользователь и день, channel_weeks — канал и неделя.
• Прилагательное-фильтр, когда шаг только сужает набор: mature_cohort, valid_events.
• Никаких номеров: шаги переставляют, и step3 после правки оказывается вторым.

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

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

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

Производительность: когда CTE материализуется

Про CTE до сих пор повторяют, что он «медленнее подзапроса» или, наоборот, «быстрее». Оба утверждения пришли из PostgreSQL до 12-й версии. Там каждый CTE материализовался: база сначала вычисляла его целиком, а потом основной запрос читал готовый результат. Условие из основного запроса не проталкивалось внутрь. Если вы фильтровали один день из CTE над всей таблицей событий, база всё равно считала всю таблицу.

С 12-й версии PostgreSQL встраивает CTE в основной запрос, если на него ссылаются один раз, он не рекурсивный и не изменяет данные. Тогда он работает так же, как подзапрос, и оптимизатор видит запрос целиком. Если на CTE ссылаются два и более раза, по умолчанию он материализуется, чтобы не считаться дважды. Поведение можно задать явно: WITH x AS MATERIALIZED (…) или AS NOT MATERIALIZED (…).

В DuckDB, ClickHouse, BigQuery и других системах правила свои и меняются от версии к версии. Поэтому единственный честный ответ на вопрос о скорости — план выполнения. Выбирайте между CTE и подзапросом по читаемости, а к EXPLAIN обращайтесь, когда запрос действительно медленный. Часто ускоряет не замена CTE на подзапрос, а фильтр, перенесённый в самый ранний шаг.

Рекурсивный CTE: календарь без пропущенных дней

GROUP BY signup_date возвращает строки только за дни, в которые были регистрации. Для organic это все 90 дней периода. Для paid_search — 60: канал запущен 1 июля, и за июнь строк нет вовсе. На графике по таким данным июнь исчезает, линия начинается с июля, и незаметно, что канал в июне не существовал.

Хуже становится со средними. avg(signups) по сгруппированной таблице paid_search даёт 16,9 регистрации в день. Это среднее по дням, когда канал работал. Среднее по всему периоду — 1 015 / 90 = 11,3. Оба числа правильные, но отвечают на разные вопросы. Беда в том, что первое получается само, а второе — только если дни без строк существуют в данных.

Рекурсивный CTE строит такой календарь. Первая часть — якорь, одна строка с начальной датой. Вторая часть ссылается на сам CTE и добавляет следующий день, пока не выполнится условие остановки. Потом календарь соединяется с агрегатом через LEFT JOIN, а пропуски заполняются нулём через coalesce. В PostgreSQL и DuckDB есть и generate_series, который делает то же короче. Но рекурсивный CTE переносим и показывает сам механизм, а он пригодится для иерархий — например, дерева отделов или цепочки приглашений.

WITH RECURSIVE calendar AS (
  SELECT DATE '2026-06-01' AS day            -- якорь
  UNION ALL
  SELECT day + 1 FROM calendar              -- шаг
  WHERE day < DATE '2026-08-29'             -- остановка
),
daily AS (
  SELECT signup_date AS day, count(*) AS signups
  FROM users
  WHERE channel = 'paid_search'
  GROUP BY signup_date
)
SELECT
  count(*)                                  AS days,              -- 90
  count(d.signups)                          AS days_with_rows,    -- 60
  round(avg(d.signups), 1)                  AS avg_active_days,   -- 16.9
  round(avg(coalesce(d.signups, 0)), 1)     AS avg_all_days       -- 11.3
FROM calendar c
LEFT JOIN daily d ON d.day = c.day;

Дневная метрика в четырёх слоях: от 35 341 события до 91 строки

Та же раскладка по шагам работает для любой регулярной метрики. DAU по открытиям приложения в учебной базе собирается из 35 341 события. Слой событий нужного типа — 28 693 строки app_open. Слой «пользователь и день» после DISTINCT user_id, event_time::DATE — снова 28 693: в этой выгрузке открытие уже записано одной строкой на пользователя в день. Итог — 91 строка, по одной на день с 1 июня по 30 августа.

Совпадение 28 693 и 28 693 — результат проверки, а не повод убрать шаг. В живом трекинге одно открытие отправляется дважды при плохой связи, и тогда слой user_days окажется меньше слоя событий. Шаг дедупликации стоит оставить и следить за разницей: пока она ноль, всё в порядке. Когда она появится, это будет первым сигналом, что изменился сбор событий, а не поведение пользователей.

Последний слой ловит незакрытый день. В среднем с 1 по 29 августа приложение открывали 470,9 человека в день, 28 августа — 567, а 30 августа — 221. Выгрузка закрыта в 13:00, и последние сутки неполные. Если этот день попадёт на график без пометки, он будет выглядеть как обвал. Отсекать его — работа слоя, а не того, кто смотрит на дашборд.

Слой отвечает за определение, дашборд — за представление. Какое событие считается активностью, как режутся сутки, кого исключать, где кончается закрытый период — это живёт в CTE и меняется в одном месте. Фильтр по каналу и выбор недели на экране — в отчёте. Если определение активности можно переключить выпадающим списком, единого слоя нет: есть несколько определений под общим названием.

WITH app_opens AS (
  -- одна строка — открытие приложения
  SELECT user_id, event_time
  FROM events
  WHERE event_name = 'app_open'
),
user_days AS (
  -- одна строка — пользователь, который открыл приложение в этот день
  SELECT DISTINCT user_id, event_time::DATE AS day
  FROM app_opens
)
SELECT day, count(*) AS dau
FROM user_days
WHERE day < DATE '2026-08-30'  -- последний день выгрузки неполный
GROUP BY day
ORDER BY day;

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

CTE хорош, пока расчёт живёт в одном запросе. Признак, что он перерос это состояние, — копия того же WITH user_days AS (…) в третьем отчёте. Каждая копия правится отдельно, и через месяц в одной из них появится исключение тестовых аккаунтов, которого нет в других. Метрика с одним названием начинает давать разные числа на разных экранах.

Следующая ступень — view или модель в dbt с тем же содержимым. Раскладка на шаги при этом не меняется: app_opens и user_days становятся отдельными объектами с теми же именами, комментариями и контрольными числами. Поэтому разложить расчёт на именованные CTE полезно даже в разовом запросе. Если метрика переживёт ноутбук, переносить её будет просто: переписывать ничего не придётся.

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

Частые ошибки в запросах с CTE

Ошибки в CTE — это обычно не синтаксис, а смешанное зерно или потерянное условие. Синтаксические встречаются реже, но сбивают с толку, потому что сообщение об ошибке указывает не туда.

• Соединение с таблицей, где несколько строк на ключ, до свёртки: продления удваивают конверсию. Сначала first_payment, потом JOIN.
• INNER JOIN там, где нужен LEFT: не плативших нет в знаменателе, и конверсия становится 100%.
• Шаг смешивает два зерна: в одном CTE и строки платежей, и суммы по пользователю. Такой шаг нельзя проверить одним контрольным числом.
• Имя CTE совпадает с именем таблицы: внутри запроса payments начинает означать уже не таблицу, а шаг, и следующий читатель это пропустит.
• Рекурсивный CTE без условия остановки или с условием, которое никогда не станет ложным: запрос работает до лимита памяти.
• Предыдущая инструкция без точки с запятой: в некоторых клиентах WITH склеивается с ней в один запрос, и ошибка указывает на строку, где всё правильно.
• ORDER BY внутри CTE, на который рассчитывает основной запрос: порядок задаёт только внешний ORDER BY.

Практика на учебной базе

Три задачи, которые удобно решать через CTE. База та же, что в песочнице симулятора SQL-аналитика, поэтому ответы должны совпасть до десятой.

Первая. Посчитайте среднюю выручку на плательщика и число тех, кто принёс больше неё. Используйте один CTE и два обращения к нему. Ответ: $33,60 и 359 человек.

Вторая. Повторите расчёт конверсии за 14 дней, но для окна в 7 дней. Подумайте, какая дата отсечения когорты нужна для такого окна, и проверьте каждый шаг контрольным числом. Сумма конвертированных по каналам должна совпасть с числом строк шага converted.

Третья. Посчитайте средний DAU по открытиям приложения за 1–29 августа, не включая незакрытое 30-е. Ответ: 470,9. Затем добавьте календарь через WITH RECURSIVE и проверьте, что в августе нет дней без строк: их число должно быть 29.

Итог

Обычный CTE разбивает запрос на именованные этапы. Когда каждый этап должен повторяться — например, от одного аэропорта переходить к следующему до заданной глубины, — нужен отдельный разбор WITH RECURSIVE: там к именованным шагам добавляются якорь, рабочая таблица, остановка и защита от циклов.

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

CTE и работа с NULL разбираются в пятой главе симулятора SQL-аналитика, подзапросы — в одиннадцатой. Задания проверяются автоматически на той же учебной базе, что и примеры выше. Первые главы курса — устройство базы, SELECT и WHERE — открыты бесплатно.