Общие табличные выражения (CTE)

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Общие табличные выражения

Определение и назначение

Общее табличное выражение представляет собой именованное временное результирующее множество, существующее в рамках одного SQL-запроса. Общие табличные выражения позволяют разбить сложный запрос на логически связанные части, каждая из которых имеет собственное имя и может использоваться в последующих частях запроса.

Основное назначение общих табличных выражений заключается в повышении читаемости и поддерживаемости сложных SQL-запросов. Они предоставляют возможность выделить промежуточные вычисления, дать им осмысленные имена и использовать эти именованные результаты в основном запросе или в других общих табличных выражениях.

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

Синтаксис и структура

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

WITH имя_выражения (столбец1, столбец2, столбец3) AS (
    SELECT столбец1, столбец2, столбец3
    FROM таблица
    WHERE условие
)
SELECT * FROM имя_выражения;

Play ITЗагрузка интерактивного демо…

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

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

Возможности операций выборки

Общее табличное выражение может содержать любые операции выборки, доступные в SQL. Это включает соединения таблиц, агрегатные функции, оконные функции, подзапросы и другие сложные конструкции.

Пример использования соединений в общем табличном выражении:

Код ITЗагрузка примера кода…

Множественные последовательные выражения

Общие табличные выражения поддерживают определение нескольких последовательных или взаимосвязанных выражений через запятую. Каждое последующее выражение может ссылаться на предыдущие выражения, определенные в том же блоке WITH.

Код ITЗагрузка примера кода…

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

Рекурсивные общие табличные выражения

Для работы с иерархическими или древовидными данными используется рекурсивный вариант общего табличного выражения с ключевым словом RECURSIVE. Рекурсивное общее табличное выражение состоит из двух частей: начального запроса и рекурсивного запроса, разделенных оператором UNION ALL.

Код ITЗагрузка примера кода…

Поддержка в различных СУБД

Общие табличные выражения поддерживаются в большинстве современных систем управления базами данных. В разных СУБД могут существовать незначительные различия в синтаксисе и функциональности.

PostgreSQL предоставляет полную поддержку общих табличных выражений, включая рекурсивные выражения. PostgreSQL поддерживает определение нескольких общих табличных выражений в одном запросе и позволяет использовать их в различных частях запроса.

MySQL начиная с версии 8.0 предоставляет поддержку общих табличных выражений. В более ранних версиях MySQL эта функциональность отсутствует.

SQL Server поддерживает общие табличные выражения начиная с версии 2005. SQL Server предоставляет дополнительные возможности, такие как использование общих табличных выражений в инструкциях INSERT, UPDATE и DELETE.

Oracle Database поддерживает общие табличные выражения начиная с версии 9i. Oracle предоставляет расширенные возможности для работы с рекурсивными общими табличными выражениями, включая управление циклами и ограничениями рекурсии.

SQLite поддерживает общие табличные выражения начиная с версии 3.8.3. Поддержка рекурсивных общих табличных выражений также доступна в SQLite.

Производительность и оптимизация

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

Оптимизатор запросов в большинстве современных СУБД способен эффективно обрабатывать общие табличные выражения, применяя те же методы оптимизации, что и для обычных подзапросов. Однако в некоторых случаях использование общих табличных выражений может привести к снижению производительности, особенно при работе с большими объемами данных.

Для оптимизации производительности общих табличных выражений рекомендуется использовать индексы на столбцах, используемых в условиях соединения и фильтрации. Также важно избегать избыточных вычислений и выбирать только необходимые столбцы в общих табличных выражениях.

Пример оптимизированного общего табличного выражения с использованием индексов:

Код ITЗагрузка примера кода…

В этом примере используются ограничения LIMIT и временные фильтры для уменьшения объема обрабатываемых данных. Индексы на столбцах status, last_login_date и purchase_date значительно ускоряют выполнение запроса.

Материализация и повторное использование

Материализация CTE — это когда СУБД один раз выполняет подзапрос из блока WITH и сохраняет строки во временную область (в памяти или на диске). Дальше основной запрос читает уже готовый набор. Подзапрос повторно не выполняется.

Такой приём полезен, если одно и то же CTE встречается в запросе несколько раз и каждый пересчёт дорог по времени или по чтению с диска.

Код ITЗагрузка примера кода…

В примере выше CTE с активными клиентами участвует в двух ветках UNION. Планировщик может сохранить его результат один раз и переиспользовать.

Отдельная тема — материализованное представление (MATERIALIZED VIEW). Оно хранится в схеме базы и обновляется командой REFRESH. CTE существует только в рамках одного запроса.

PostgreSQL 12, встраивание CTE и MATERIALIZED

Подсказки MATERIALIZED и NOT MATERIALIZED появились в PostgreSQL 12. Они относятся только к этой СУБД; в MySQL, SQLite и T-SQL аналогов нет.

Термины, которые встретятся ниже

Планировщик (оптимизатор) — компонент СУБД, который выбирает порядок чтения таблиц, тип соединений и использование индексов. Общая схема работы — в Принципах SQL-движка.
План выполнения — пошаговая инструкция для СУБД. Её смотрят командой EXPLAIN. Разбор узлов плана — в продвинутой оптимизации PostgreSQL.
Встраивание (inline) — планировщик подставляет тело CTE прямо в основной SELECT, как обычный подзапрос. Блок WITH может исчезнуть из плана, а фильтры из внешнего запроса иногда "спускаются" внутрь CTE.
Материализация — подзапрос CTE выполняется один раз, результат сохраняется; в плане обычно виден узел CTE Scan.
Seq Scan — полный просмотр таблицы строка за строкой.
Index Scan — чтение через индекс, без полного просмотра всей таблицы.

Как вели себя CTE в разных версиях PostgreSQL

До версии 12 каждый CTE всегда материализировался. Планировщик не мог встроить его в основной запрос. Тяжёлый подзапрос в WITH гарантированно выполнялся один раз — разработчики иногда специально оборачивали в CTE дорогие вычисления.
С версии 12 планировщик по умолчанию может встроить CTE в основной запрос. Простой WITH иногда полностью исчезает из плана.

Пример, где CTE часто встраивается:

WITH data AS (
  SELECT *
  FROM orders
)
SELECT *
FROM data
WHERE user_id = 42;

Запустите EXPLAIN для такого запроса. Вместо узла CTE Scan вы увидите Seq Scan или Index Scan по таблице orders с условием user_id = 42. Планировщик объединил CTE с внешним фильтром и читает только нужные строки. На лёгких подзапросах это ускоряет работу.

На тяжёлом промежуточном расчёте встраивание может замедлить запрос: дорогая часть выполнится повторно или прочитает лишние данные. Тогда помогает явная материализация.

AS MATERIALIZED — зафиксировать результат CTE

Ключевое слово просит СУБД сначала полностью выполнить подзапрос CTE и только потом использовать сохранённые строки. Планировщик не встраивает такое CTE в основной запрос.

WITH expensive AS MATERIALIZED (
  SELECT *
  FROM huge_events
  WHERE created_at >= now() - interval '1 day'
)
SELECT COUNT(*)
FROM expensive;

Типичный план содержит узел CTE Scan по expensive. Таблица huge_events читается один раз, даже если основной запрос ссылается на expensive несколько раз или содержит тяжёлые соединения.

AS NOT MATERIALIZED — подсказка на встраивание

Ключевое слово сообщает планировщику, что CTE удобно встроить в основной запрос, как подзапрос:

WITH data AS NOT MATERIALIZED (
  SELECT *
  FROM orders
)
SELECT *
FROM data
WHERE user_id = 42;

Итоговый план часто совпадает с запросом без WITH. Решение остаётся за планировщиком: подсказка влияет на выбор, но не отменяет его полностью.

Когда что указывать

Дорогой подзапрос, результат нужен один или несколько раз в том же запросе — AS MATERIALIZED.
Простой фильтр или JOIN, выгодно применить WHERE сразу к базовой таблице — AS NOT MATERIALIZED или без ключевого слова.
Нужно поведение старых версий Postgres (однократный расчёт CTE) — AS MATERIALIZED.
Рекурсивный CTE (WITH RECURSIVE) — всегда материализуется; MATERIALIZED и NOT MATERIALIZED к нему не применяются. См. рекурсивные CTE ниже и статью иерархии в реляционной БД.
CTE с изменением данных (INSERT, UPDATE, DELETE … RETURNING) — отдельные правила планировщика; встраивание ограничено. Примеры — в шпаргалке типичных задач.

На больших объёмах данных один и тот же запрос с MATERIALIZED и без него иногда отличается по времени в десятки раз. Сравнивайте планы через EXPLAIN — универсального правила "CTE всегда быстрее" или "CTE всегда медленнее" нет.

Обновление с PostgreSQL 11 на 12 и новее

После обновления запросы с CTE могут замедлиться: планировщик начнёт встраивать CTE, и тяжёлая часть будет пересчитываться. Добавьте MATERIALIZED к проблемному CTE и сравните планы через EXPLAIN (ANALYZE, BUFFERS). Чтение плана — продвинутая оптимизация PostgreSQL, общие приёмы — оптимизация SQL-запросов.

Как проверить в psql

EXPLAIN (ANALYZE, BUFFERS)
WITH expensive AS MATERIALIZED (
  SELECT * FROM huge_events WHERE created_at >= now() - interval '1 day'
)
SELECT COUNT(*) FROM expensive;

Снимите тот же план без MATERIALIZED и сравните:

есть ли узел CTE Scan;
поля Execution Time (фактическое время);
строку Buffers (сколько страниц прочитано с диска и из кэша).

Установка PostgreSQL и работа в psql — Первые шаги с SQL, PostgreSQL — практическая работа и API.

См. также

Оптимизация SQL-запросов — индексы, статистика, типовые ошибки планировщика.
Сложные индексы — когда Index Scan выгоднее Seq Scan.
Практикум PostgreSQL 8.11 — EXPLAIN, конфигурация, мониторинг.
Справочник администратора PostgreSQL — параметры сервера.

Взаимосвязанные общие табличные выражения

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

Код ITЗагрузка примера кода…

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

Рекурсивные выражения с ограничениями

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

Код ITЗагрузка примера кода…

В этом примере ограничение dc.level < 10 предотвращает бесконечную рекурсию, ограничивая глубину обхода дерева категорий десятью уровнями. Это особенно важно при работе с данными, которые могут содержать циклические зависимости или очень глубокие иерархии.

Некоторые системы управления базами данных предоставляют дополнительные механизмы для управления рекурсией, такие как ключевые слова SEARCH и CYCLE, которые позволяют явно указывать порядок обхода и обнаруживать циклы в данных.

Когда применять общие табличные выражения

Общие табличные выражения представляют собой инструмент для организации запросов. Эффективное использование этого инструмента требует понимания его назначения и ограничений.

Целесообразность применения

Общие табличные выражения оправданы в следующих случаях:

Запрос содержит несколько логических этапов обработки данных
Необходимо повторное использование промежуточного результата в разных частях запроса
Требуется повышение читаемости сложного запроса через именование этапов
Реализуется рекурсивный обход иерархических структур
Выполняется многоуровневая агрегация или трансформация данных

Примеры излишнего использования

Простые запросы без сложной логики не требуют применения общих табличных выражений.

-- Излишнее использование CTE
WITH отфильтрованные_счета AS (
    SELECT *
    FROM счета
    WHERE статус = 'активный'
)
SELECT *
FROM отфильтрованные_счета;

-- Прямой запрос
SELECT *
FROM счета
WHERE статус = 'активный';

Код ITЗагрузка примера кода…

В этих примерах общие табличные выражения добавляют вертикальный объем кода без улучшения читаемости или функциональности.

Примеры оправданного использования

Сложные запросы с многоэтапной обработкой выигрывают от применения общих табличных выражений.

Код ITЗагрузка примера кода…

Этот запрос разбивает сложную логику на последовательные этапы с осмысленными именами, что упрощает понимание и сопровождение.

Рекурсивные структуры

Рекурсивные общие табличные выражения незаменимы при работе с иерархическими данными.

Код ITЗагрузка примера кода…

Рекурсивные общие табличные выражения предоставляют единственный стандартный способ обхода древовидных структур в SQL.

Повторное использование промежуточных результатов

Когда один и тот же промежуточный результат используется несколько раз в запросе, общее табличное выражение устраняет дублирование кода.

Код ITЗагрузка примера кода…

В этом примере общее табличное выражение используется трижды, что предотвращает повторение сложного фильтра.

Общие табличные выражения (CTE)