Common Table Expressions

Common Table Expressions (CTE, обобщённые табличные выражения) — это временный именованный набор результатов, который существует в пределах одной SQL-инструкции (запроса, вставки, обновления или удаления). CTE определяется с помощью ключевого слова WITH и может многократно ссылаться на себя в рамках того же запроса, что делает его мощным инструментом для построения иерархических, рекурсивных и сложных многошаговых запросов. В отличие от временных таблиц, CTE не хранятся физически в базе данных и не требуют явного удаления; их время жизни ограничено выполнением одного оператора.

История и стандартизация

Концепция CTE была впервые реализована в коммерческих СУБД в начале 2000-х годов. В 2003 году она была включена в стандарт SQL:2003, что закрепило синтаксис WITH ... AS (...) и рекурсивные возможности. Стандарт SQL:2008 и последующие версии (SQL:2011, SQL:2016) уточнили поведение CTE, добавив поддержку модифицирующих операторов (INSERT, UPDATE, DELETE) и возможность использования CTE в подзапросах.

Первыми крупными СУБД, реализовавшими CTE, стали PostgreSQL (с версии 8.4, 2009 год), Oracle (с версии 9i, 2001 год, в виде рекурсивного CONNECT BY), Microsoft SQL Server (с версии 2005) и IBM DB2 (с версии 9.5). В настоящее время CTE поддерживается практически всеми реляционными СУБД, включая MySQL (с версии 8.0, 2018 год), SQLite (с версии 3.8.3, 2014 год) и MariaDB (с версии 10.2, 2017 год).

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

Базовый синтаксис CTE выглядит следующим образом:

``sql WITH cte_name (column1, column2, ...) AS ( SELECT ... ) SELECT * FROM cte_name; ``

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

Множественные CTE

В одном операторе можно определить несколько CTE, разделяя их запятыми:

``sql WITH cte1 AS ( SELECT id, name FROM table1 ), cte2 AS ( SELECT id, value FROM table2 ) SELECT cte1.name, cte2.value FROM cte1 JOIN cte2 ON cte1.id = cte2.id; ``

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

Виды CTE

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

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

``sql WITH customer_orders AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id ) SELECT customers.name, customer_orders.total_amount FROM customers JOIN customer_orders ON customers.id = customer_orders.customer_id; ``

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

Рекурсивные CTE — это CTE, которые ссылаются сами на себя. Они состоят из двух частей:

1. Анкерный элемент — начальный запрос, возвращающий базовый набор строк.
2. Рекурсивный элемент — запрос, который ссылается на CTE и добавляет новые строки на основе предыдущих.

Рекурсия продолжается до тех пор, пока рекурсивный элемент не вернёт пустой набор строк. Синтаксис:

``sql WITH RECURSIVE cte_name AS ( -- анкерный элемент SELECT ... UNION ALL -- рекурсивный элемент SELECT ... FROM cte_name WHERE ... ) SELECT * FROM cte_name; ``

Ключевое слово RECURSIVE обязательно для рекурсивных CTE. Используется UNION ALL (или UNION для удаления дубликатов). Рекурсивный элемент должен содержать ссылку на CTE, и рекурсия должна быть ограничена — либо естественным исчерпанием данных, либо явным условием в WHERE, либо ограничением глубины (например, WHERE level < 10).

Пример: иерархия сотрудников

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

Этот запрос строит дерево подчинённости, начиная с руководителя высшего звена и спускаясь по уровням.

Применение

Упрощение сложных запросов

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

``sql WITH category_sales AS ( SELECT category_id, AVG(price) AS avg_price FROM products GROUP BY category_id ) SELECT categories.name, category_sales.avg_price FROM categories JOIN category_sales ON categories.id = category_sales.category_id WHERE category_sales.avg_price > 100; ``

Рекурсивные запросы

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

Модификация данных

CTE могут использоваться с операторами INSERT, UPDATE и DELETE. Например, удаление дубликатов записей:

``sql WITH duplicates AS ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users ) DELETE FROM users WHERE id IN (SELECT id FROM duplicates WHERE rn > 1); ``

Оконные функции и CTE

CTE часто комбинируют с оконными функциями (ROW_NUMBER, RANK, LAG, LEAD и др.) для выполнения сложных аналитических вычислений. Например, для получения топ-3 продуктов по продажам в каждой категории:

``sql WITH ranked_products AS ( SELECT category_id, product_id, sales, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn FROM product_sales ) SELECT category_id, product_id, sales FROM ranked_products WHERE rn <= 3; ``

Преимущества и ограничения

Преимущества

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

Ограничения

• Время жизни — CTE существуют только в рамках одного оператора; их нельзя использовать в нескольких запросах или хранить для последующего использования.
• Производительность — в некоторых СУБД (например, MySQL до версии 8.0.14) CTE всегда материализуются, что может привести к избыточному потреблению памяти при больших объёмах данных. В других СУБД (например, SQL Server) оптимизатор может развернуть CTE в подзапрос, что иногда ухудшает план выполнения.
• Отсутствие индексов — CTE не имеют собственных индексов; они полагаются на индексы базовых таблиц.
• Ограничения рекурсии — глубина рекурсии может быть ограничена настройками СУБД (например, max_recursive_iterations в PostgreSQL) или производительностью.

Сравнение с другими конструкциями

CTE и подзапросы

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

CTE и временные таблицы

Временные таблицы (CREATE TEMPORARY TABLE) физически создаются в базе данных и могут быть проиндексированы, а также использоваться в нескольких запросах. CTE же не создают физических объектов и существуют только в памяти. Временные таблицы предпочтительны для больших объёмов данных, которые нужно обрабатывать в несколько этапов, или для данных, которые требуют индексации.

CTE и представления (VIEW)

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

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

• В PostgreSQL существует возможность материализации CTE с помощью ключевого слова MATERIALIZED (по умолчанию) или NOT MATERIALIZED (для встраивания в основной запрос). Это позволяет тонко настраивать производительность.
• В Oracle рекурсивные CTE появились в версии 11g, но до этого рекурсия реализовывалась через оператор CONNECT BY, который остаётся доступным и сейчас.
• В SQLite рекурсивные CTE используются для генерации последовательностей, например, для заполнения календаря датами.
• Стандарт SQL:2016 добавил возможность использования CTE в MERGE (оператор слияния), что расширило их применение для синхронизации данных.

Источники

• Стандарт ISO/IEC 9075-2:2016 (SQL/Foundation), раздел 7.13 «<query expression>».
• Документация PostgreSQL: «WITH Queries (Common Table Expressions)».
• Документация Microsoft SQL Server: «WITH common_table_expression (Transact-SQL)».
• Документация MySQL: «WITH (Common Table Expressions)».
• Книга: Joe Celko, «SQL for Smarties: Advanced SQL Programming», 5th edition, 2014.
• Статья: «Common Table Expressions: The Power of WITH» на сайте SQLPerformance.com.