Как использовать CTE в PostgreSQL для сложных запросов (2026-03-19 17:14:10) | Блог ГК ЖУР

Вы когда-нибудь сталкивались с SQL-запросом, который напоминает лабиринт из вложенных подзапросов? Читать его почти невозможно, а внести изменения — задача для отчаянных. Многие разработчики, работающие с PostgreSQL, проходят через это, не зная о мощном инструменте, который может превратить спагетти-код в элегантную и понятную конструкцию. Этот инструмент — Common Table Expressions, или CTE.

CTE — это временный результат запроса, которому можно дать имя и использовать в рамках выполнения основного запроса. Грубо говоря, это способ создать виртуальную таблицу на лету и обращаться к ней так же просто, как к обычной. Но его истинная сила раскрывается не только в структурировании. В PostgreSQL CTE бывают двух видов: обычные и рекурсивные. Последние открывают двери к решению задач, которые стандартными средствами SQL кажутся невыполнимыми — например, обход древовидных структур.

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

С CTE это выглядит гораздо чище. Сначала мы создаем CTE для расчета средней суммы заказа за месяц. Затем создаем вторую CTE, которая агрегирует данные по пользователям. И наконец, в основном запросе просто соединяем эти два временных результата и применяем условие фильтрации. Каждый шаг логически изолирован и может быть протестирован отдельно. Это как разбить сложную задачу на несколько простых функций в программировании.

Однако настоящая магия начинается с рекурсивных CTE (RECURSIVE). Они предназначены для обработки иерархических данных. Классический пример — таблица сотрудников с полями id, name и manager_id (ссылка на руководителя). Как получить полную иерархию подчинения для конкретного директора?

Рекурсивный CTE состоит из двух частей: якорного (нерекурсивного) члена и рекурсивного члена. Якорный член выбирает корневую запись — того самого директора. Рекурсивный член присоединяется к самому CTE, выбирая сотрудников, чей руководитель уже находится в результатах предыдущей итерации. PostgreSQL будет выполнять эту операцию циклически до тех пор, пока не будут выбраны все записи дерева.

Этот же принцип работает для построения путей в графах (пусть и с ограничениями), генерации последовательностей дат или разворачивания вложенных структур данных. Например, вы можете использовать рекурсивный CTE для автоматического создания календаря мероприятий на год вперед на основе шаблона повторений.

Но у каждой мощной технологии есть свои нюансы. Долгое время существовало важное правило оптимизации в PostgreSQL: материалзация CTE. Дословно движок вычислял результат CTE и сохраняал его во временной области памяти перед выполнением основного запроса. Это могло как помочь (если CTE используется несколько раз), так и серьезно навредить производительности, поскольку оптимизатор не мог «заглянуть» внутрь CTE и перепланровать запрос целиком.

Начиная с версии PostgreSQL 12 появилось ключевое слово MATERIALIZED / NOT MATERIALIZED, которое позволяет управлять этим поведением явно. По умолчанию теперь оптимизатор сам решает — материализовать ли результат или «встроить» вычисления как подзапрос. Для сложных вычислений, которые используются многократно, принудительная материализация через MATERIALIZED может ускорить выполнение. Для простых выражений лучше использовать NOT MATERIALIZED или полагаться на умолчания, чтобы позволить СУБД найти самый быстрый план.

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

Вот как можно решить это элегантно: создать серию CTE, каждая из которых отвечает за свой этап. CTE_visits: уникальные сессии за период. CTE_carts: сессии, в которых было событие добавления в корзину. CTE_orders: сессии с оформленными заказами. CTE_paid: сессии с оплаченными заказами.

Затем финальный запрос просто делает LEFT JOIN этих CTE к базовому списку визитов и считает конверсию как отношение количества строк следующего этапа к предыдущему. Такой отчет легко модифицировать, добавляя новые этапы или условия фильтрации на каждом шаге. Его сможет понять даже не-технический специалист, взглянув на структуру запроса.

Еще один важный аспект — использование CTE для модификации данных (INSERT, UPDATE, DELETE). Вы можете подготовить набор данных для изменения внутри CTE, а затем обратиться к нему в основном операторе. Это особенно полезно для каскадных обновлений или удаления устаревших данных по сложным условиям.

Например, вам нужно архивировать все заказы старше пяти лет, но только те, по которым нет открытых обращений в службу поддержки. Сначала в CTE вы находите ID таких заказов, соединяя таблицы orders и support_tickets. А затем используете этот ID спискок в DELETE FROM orders WHERE id IN (SELECT id FROM archive_cte). Логика отбора становится прозрачной и сосредоточена в одном месте.

В заключение стоит сказать, что Common Table Expressions — это не просто синтаксический сахар. Это философия написания понятных, поддерживаемых и мощных SQL-запросов в PostgreSQL. Они позволяют декомпозировать сложные проблемы, работать с иерархиями и дают контроль над выполнением плана запроса. Начните внедрять их постепенно: сначала для упрощения больших SELECT-ов, а затем осваивайте рекурсию для решения действительно нетривиальных задач. Ваш код станет чище, а коллеги скажут вам спасибо за читаемые отчеты и аналитику