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

Обобщённое табличное выражение (англ. Common Table Expression, CTE) — это временный именованный набор результатов, который можно использовать в SQL-запросах в рамках одной инструкции SELECT, INSERT, UPDATE, DELETE или MERGE. CTE облегчает написание сложных запросов, делает их более читаемыми и поддерживаемыми, позволяя выносить подзапросы в отдельные логические блоки. В отличие от подзапроса, CTE может быть рекурсивным и ссылаться сам на себя, что особенно полезно для работы с иерархическими данными (например, деревьями, графами, оргструктурами).

История

Концепция обобщённых табличных выражений была введена в стандарте SQL:1999. Первыми СУБД, реализовавшими поддержку CTE, стали Oracle (начиная с версии 9.2, 2002 год) и PostgreSQL (начиная с версии 8.4, 2009 год). Впоследствии CTE были реализованы в Microsoft SQL Server (2005), IBM Db2, SQLite (3.8.3, 2014 год), MySQL (8.0, 2018 год) и других популярных реляционных системах управления базами данных.

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

Описание и синтаксис

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

Базовый синтаксис:

``sql WITH cte_name (column1, column2, ...) AS ( SELECT column1, column2, ... FROM some_table WHERE condition ) SELECT * FROM cte_name WHERE another_condition; ``

• cte_name — произвольное имя выражения.
• (column1, column2, ...) — необязательный список имён столбцов, возвращаемых CTE. Если не указан, имена берутся из подзапроса.
• SELECT ... FROM some_table — подзапрос, определяющий содержимое CTE.
• Основной SELECT (или другая инструкция) может многократно обращаться к CTE.

Преимущества использования CTE

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

Отличие от подзапросов

Подзапросы могут использоваться внутри WHERE, HAVING, FROM и других частей запроса, но они не могут быть переиспользованы в одном запросе без повторения. CTE, напротив, объявляется один раз и может многократно упоминаться в основном запросе и в других CTE (через цепочку). Кроме того, подзапросы нельзя ссылаться на себя (не рекурсивны), а CTE — можно.

Виды CTE

1. Не рекурсивные CTE

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

Пример. Поиск среднего количества продаж по категориям товаров и выделение категорий выше среднего:

``sql WITH category_sales AS ( SELECT category_id, AVG(amount) AS avg_amount FROM sales GROUP BY category_id ) SELECT c.category_name, cs.avg_amount FROM categories c JOIN category_sales cs ON c.id = cs.category_id WHERE cs.avg_amount > 100; ``

2. Рекурсивные CTE

Рекурсивное CTE — это выражение, которое ссылается само на себя в своём определении. Оно обязательно состоит из двух частей, объединённых ключевым словом UNION ALL (или UNION): базового (терминального) подзапроса и рекурсивного подзапроса.

• Базовый подзапрос — начальный набор данных, возвращающий первую итерацию (например, корневые узлы дерева).
• Рекурсивный подзапрос — ссылается на само CTE и на каждой итерации добавляет новые строки, пока не будет выполнено условие завершения (количество итераций ограничено, обычно до 100–1000 по умолчанию, в зависимости от СУБД).

Пример. Построение иерархии подчинённых для заданного руководителя:

``sql WITH RECURSIVE subordinates AS ( -- базовый подзапрос: выбираем начального руководителя SELECT id, name, manager_id, 1 AS level FROM employees WHERE id = 5 UNION ALL -- рекурсивный подзапрос: выбираем подчинённых SELECT e.id, e.name, e.manager_id, s.level + 1 FROM employees e JOIN subordinates s ON e.manager_id = s.id ) SELECT * FROM subordinates; ``

В данном примере subordinates последовательно расширяется, пока есть записи, ссылающиеся на уже найденные. Уровень (level) увеличивается с каждым шагом.

Ограничения рекурсии: большинство СУБД устанавливают максимальную глубину рекурсии (например, в PostgreSQL — 100 итераций по умолчанию, можно изменить с помощью SET max_recursive_iterations = 1000; в MySQL — ограничение cte_max_recursion_depth). Это предотвращает зацикливание.

Применение

CTE широко используются в следующих сценариях:

• Работа с иерархическими структурами: каталоги товаров, дерево категорий, организационная структура, вложенные комментарии.
• Разбивка сложных аналитических запросов: например, последовательные вычисления (сначала группировка по регионам, затем ранг по продажам).
• Замена временных таблиц: если данные нужны только на время выполнения одного запроса.
• Многоэтапные преобразования: CTE могут быть соединены цепочкой — одно CTE использует другое.
• Рекурсивное обход графов: поиск маршрутов в транспортных сетях, топологическая сортировка.

Поддержка в СУБД (краткий обзор)

Интересные факты

• Термин «обобщённое табличное выражение» происходит от английского «Common Table Expression». В русскоязычной технической литературе также встречаются сокращения ОТВ и КТЭ (калька).
• В стандарте SQL:1999 CTE называются «with clause expression» (выражение с предложением WITH).
• CTE не создаёт постоянных объектов в базе данных — оно существует только во время выполнения содержащего его запроса. В этом его отличие от представлений (views) и временных таблиц.
• Некоторые СУБД (например, PostgreSQL) позволяют использовать CTE не только после WITH, но и в выражениях INSERT ... RETURNING, UPDATE ... RETURNING, DELETE ... RETURNING.

Критика

Несмотря на удобство, у CTE есть недостатки:

• Оптимизация запросов: некоторые СУБД (исторически PostgreSQL до версии 12) обрабатывали CTE как «ограничитель плана» — они не проталкивали фильтры внутрь CTE, что могло приводить к избыточной обработке данных. В новых версиях этот недостаток исправлен (PostgreSQL 12+ может выполнять материализацию CTE или встраивать его в основной запрос).
• Ограниченная область видимости: CTE нельзя использовать в нескольких независимых запросах или в хранимых процедурах в том же виде, что и временные таблицы.
• Проблемы с производительностью при больших рекурсиях: рекурсивные CTE могут потреблять много памяти и времени, если не настроить ограничение глубины.

Источники

• Стандарт SQL:1999, ISO/IEC 9075:1999
• Документация PostgreSQL (раздел «WITH Queries (Common Table Expressions)»)
• Документация Microsoft SQL Server (раздел «WITH common_table_expression (Transact-SQL)»)
• Документация MySQL 8.0 (раздел «WITH (Common Table Expressions)»)
• Бенган С., «SQL. Сборник рецептов», 2-е издание
• Электронные ресурсы: SQLite documentation, Oracle Database SQL Language Reference