Всем привет, меня зовут Сергей Прощаев. Я Tech Lead и руководитель направления Java | Kotlin разработки в FinTech & E‑commerce, а также преподаю на курсах разработки и архитектуры в OTUS. И в этой статье расскажу, как перестать страдать над многотысячными портянками SQL‑кода и начать писать сложные запросы просто, используя обобщенные табличные выражения (CTE).
Я видел, как разработчики часами медитируют на тройные вложенные JOIN с подзапросами, пытаясь понять, где закралась ошибка. А через неделю они сами не могут объяснить логику своего творения. Знакомая история? Но есть способ получше.
Почему CTE, а не лапша из подзапросов?
Когда я пришел в FinTech, первое, с чем я столкнулся, — это скрипт миграции данных из монолита в микросервисы. Он состоял из 600 строк сплошного SQL с вложенными SELECTвнутри FROM, внутри JOIN, внутри еще одного SELECT. Распутать этот клубок было невозможно. Тогда я впервые по‑настоящему оценил мощь CTE, переписав этот скрипт в читаемую и поддерживаемую форму.
Давайте посмотрим правде в глаза. SQL — декларативный язык. Мы говорим, что хотим получить, а не как. Но когда вы пишете подзапрос в подзапросе, вы заставляете читателя (и базу данных) строить план выполнения в голове снаружи внутрь или изнутри наружу. Это противоречит линейному ходу чтения.
Что такое CTE простыми словами?
Это временная именованная результирующая таблица, которая существует только в рамках одного запроса. Вы определяете её до основного SELECT с помощью ключевого слова WITH.
Сравните два подхода. Допустим, нужно найти сотрудников, чья зарплата выше средней по отделу, и вывести их боссов.
Без CTE:
SELECT e.name, b.name AS boss_name
FROM employees e
JOIN employees b ON e.boss_id = b.id
WHERE e.salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);С CTE:
WITH avg_by_dept AS (
SELECT department_id, AVG(salary) as avg_sal
FROM employees
GROUP BY department_id
)
SELECT e.name, b.name as boss_name
FROM employees e
JOIN employees b ON e.boss_id = b.id
JOIN avg_by_dept a ON e.department_id = a.department_id
WHERE e.salary > a.avg_sal;В первом случае подзапрос вычисляется заново для каждой строки (теоретически), а логика «средняя по отделу» запрятана в WHERE. Во втором — логика вычисления среднего вынесена наверх, поименована, и дальше мы просто используем готовый результат как обычную таблицу. Читаем сверху вниз: сначала готовим средние, потом джойним. Это естественно.
Разбиваем слона на стейки: конвейер из CTE
Настоящая магия CTE раскрывается, когда вы начинаете выстраивать конвейер обработки данных. Вместо одного монструозного запроса вы делаете несколько последовательных шагов. И не слушайте тех, кто говорит, что это всегда медленнее. В PostgreSQL оптимизатор часто разворачивает CTE как обычный подзапрос, а если нужно — вы можете применить MATERIALIZED, чтобы явно зафиксировать результат шага и избежать повторных вычислений.
Вот реальный пример из e‑commerce. Задача: найти активных пользователей, сделавших за последние 30 дней заказов на сумму более 1000 рублей, и отнести их к сегменту лояльности на основе их истории за год.
Без CTE это был бы нечитаемый монстр с датами и суммами, размазанными по коду. С CTE мы разбиваем этот бизнес‑процесс на атомарные смысловые этапы:
WITH
-- Шаг 1: Активные пользователи за 30 дней
active_users AS (
SELECT DISTINCT user_id
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
),
-- Шаг 2: Их выручка за этот период
recent_revenue AS (
SELECT o.user_id, SUM(o.amount) as total_amount
FROM orders o
JOIN active_users u ON o.user_id = u.user_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY o.user_id
HAVING SUM(o.amount) > 1000
),
-- Шаг 3: Годовая история для этих пользователей
yearly_history AS (
SELECT o.user_id, COUNT(*) as orders_count,
MAX(o.order_date) as last_order
FROM orders o
JOIN recent_revenue r ON o.user_id = r.user_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY o.user_id
)
-- Финал: Формируем аналитическую витрину
SELECT u.name, r.total_amount, h.orders_count,
CASE
WHEN h.orders_count > 20 THEN 'VIP'
ELSE 'Regular'
END as segment
FROM users u
JOIN recent_revenue r ON u.id = r.user_id
JOIN yearly_history h ON u.id = h.user_id;Согласитесь, каждая часть запроса здесь — отдельная бизнес‑логика. Разработчик читает этот код как повествование. А главное, тестировать можно поэтапно: выполнили SELECT * FROM active_users — проверили, те ли люди попали. Затем recent_revenue — убедились в суммах. Такой подход сокращает время отладки в разы.
CTE не только для SELECT: как UPDATE и DELETE меняют правила игры
Долгое время я думал, что CTE — исключительно инструмент для выборок. Пока не столкнулся с задачей удаления дубликатов в витрине данных. Нужно было оставить только последнюю запись для каждого пользователя, а старые удалить. Раньше это была двухшаговая операция: сначала выборка ID на удаление во временную таблицу, потом DELETE. CTE позволяет сделать это в одной атомарной транзакции:
WITH ranked_events AS (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY user_id
ORDER BY created_at DESC)
as rn
FROM user_events
)
DELETE FROM user_events
WHERE id IN (SELECT id FROM ranked_events WHERE rn > 1);Или пример с UPDATE. Допустим, нужно проставить пользователям флаг премиум‑статуса на основе суммы их покупок за прошлый месяц:
WITH premium_candidates AS (
SELECT user_id
FROM orders
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
GROUP BY user_id
HAVING SUM(amount) > 50000
)
UPDATE users
SET status = 'PREMIUM'
WHERE id IN (SELECT user_id FROM premium_candidates);Такие конструкции делают код лаконичнее и безопаснее. Нет риска, что между отдельными командами кто‑то изменит данные. Всё работает как единое целое.
Рекурсивные CTE: ваш лифт по дереву иерархии
На одном из проектов в FinTech мы строили витрину организационной структуры холдинга. В таблице departments было поле parent_id. Нужно было развернуть все дерево подчиненности: от гендиректора до линейного персонала. Здесь в дело вступают рекурсивные CTE.
Механика проста: UNION ALL объединяет стартовую точку (корень дерева) с результатом рекурсивного вызова, который ссылается на сам CTE.
WITH RECURSIVE org_tree AS (
-- База: Топ-менеджмент
SELECT id, name, parent_id, 1 AS level
FROM departments
WHERE parent_id IS NULL
UNION ALL
-- Рекурсия: Ищем детей
SELECT d.id, d.name, d.parent_id,
t.level + 1
FROM departments d
JOIN org_tree t ON d.parent_id = t.id
)
SELECT * FROM org_tree
ORDER BY level, name;Я помню, как коллега, увидев это, воскликнул: «И всё? Без PL/pgSQL и циклов?». Да, именно так. База данных сама идет вглубь, пока не упрется в листья дерева.
Взгляните на эту схему (рис. 2): рекурсивный CTE работает не как бесконечный цикл, а как управляемый итеративный процесс. Сначала выполняется базовая часть — она даёт «затравку», исходный набор строк. Дальше срабатывает рекурсивная часть, которая ищет строки, связанные с уже найденными.
Если такие строки появились, они добавляются к общему результату и снова подаются на вход рекурсивной части — и так до тех пор, пока очередной шаг не вернёт пустой набор. Тогда рекурсия останавливается, и мы получаем финальный результат. Именно эту механику и важно понимать: без чёткого «дна» рекурсия либо упадёт по limit, либо зациклится.
Еще один кейс — генерация последовательностей. Нужно было разбить бюджетный период на недели для отчета. Вместо использования таблицы‑календаря мы сгенерировали даты на лету:
WITH RECURSIVE weeks AS (
SELECT DATE '2026-01-01' AS week_start
UNION ALL
SELECT (week_start + INTERVAL '7 days')::DATE
FROM weeks
WHERE week_start < DATE '2026-12-31'
)
SELECT week_start,
LEAST(week_start + INTERVAL '6 days',
DATE '2026-12-31')::DATE AS week_end
FROM weeks;Особенности оптимизации: что нужно знать
И вот здесь начинается то, что отличает просто разработчика от специалиста, который думает о производительности. Не все CTE одинаково полезны для оптимизатора.
В PostgreSQL до 11 версии включительно все CTE всегда материализовались (вычислялись и сохранялись во временную память), выступая как барьер для оптимизации (optimization fence). Планировщик не мог протолкнуть условия WHERE внутрь CTE и не использовал индексы таблиц, стоящих внутри выражения. Если вы писали WITH cte AS (...SELECT FROM big_table...) SELECT FROM cte WHERE id = 1, база сначала читала всю big_table целиком, и только потом фильтровала. На миллионах строк это катастрофа.
Начиная с PostgreSQL 12, поведение изменилось. Теперь по умолчанию MATERIALIZED применяется, только если CTE используется в запросе больше одного раза или содержит рекурсию. Во всех остальных случаях планировщик разворачивает CTE «на месте» (inlined).
Что это значит для вас как для аналитика или разработчика?
Не надейтесь на CTE как на хинт для исправления плана запроса (как это было раньше, когда через
WITHпринудительно фиксировали порядок выполнения).Если вы используете тяжелый CTE несколько раз, имеет смысл явно указать
AS MATERIALIZED, чтобы посчитать его один раз.И наоборот, если CTE используется один раз, но вы хотите гарантированно избежать повторного разворачивания дорогой логики (например, сложный агрегат), используйте
AS MATERIALIZED.
Заключение: Пишите код для людей, а не только для машин
Обобщенные табличные выражения — это в первую очередь инструмент коммуникации. Когда вы пишете код через CTE, вы говорите своей команде: «Смотрите, сначала я вычисляю вот это, потом соединяю с этим, а в конце отдаю финальный результат». Это чистая архитектурная мысль, не замутненная синтаксическим шумом.
Умение писать чистые и эффективные CTE — это маркер сильного разработчика или администратора, который умеет думать категориями бизнес‑логики, а не только синтаксиса JOIN. Это навык, который экономит часы отладки и миллионы нервных клеток при поддержке legacy‑кода.
Если эта тема для вас актуальна и вы хотите глубже разобраться в производительности, архитектуре и внутреннем устройстве PostgreSQL, приглашаю вас на открытый урок на платформе OTUS. Урок пройдет в рамках курса «PostgreSQL для администраторов баз данных и разработчиков».
21 мая в 20:00. «SQL: Обобщенное табличное выражение (CTE) — как писать сложные запросы просто».
Участие бесплатное, во время урока можно будет задать вопросы по теме и уточнить практические нюансы работы с CTE.
📌 Перед регистрацией можно также пройти бесплатное тестирование, чтобы оценить свой уровень и понять, какие темы PostgreSQL требуют дополнительного внимания.