|
Professor Seleznov
|
Всем привет, меня зовут Сергей Прощаев. Я Tech Lead и руководитель направления Java | Kotlin разработки в FinTech & E‑commerce, а также преподаю на курсах разработки и архитектуры в OTUS. И в этой статье расскажу, как перестать страдать над многотысячными портянками SQL‑кода и начать писать сложные запросы просто, используя обобщенные табличные выражения (CTE).
Я видел, как разработчики часами медитируют на тройные вложенные JOIN с подзапросами, пытаясь понять, где закралась ошибка. А через неделю они сами не могут объяснить логику своего творения. Знакомая история? Но есть способ получше.
Рис. 1 Иллюстрация: превращение запутанного SQL в элегантную структуру с помощью CTE
Почему 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
Взгляните на эту схему (рис. 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 требуют дополнительного внимания.-Источник
|
