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

Общие табличные выражения (англ. Common Table Expressions, CTE) — это временный именованный набор результатов, который определяется в рамках выполнения одного SQL-запроса и может быть многократно использован в этом же запросе. CTE являются частью стандарта SQL:1999 и реализованы в большинстве современных систем управления базами данных (СУБД), включая PostgreSQL, MySQL (начиная с версии 8.0), Microsoft SQL Server, Oracle Database и SQLite. Основное назначение CTE — упрощение сложных запросов, повышение их читаемости и возможность рекурсивных операций.

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

Концепция общих табличных выражений была впервые формализована в стандарте SQL:1999, который ввёл синтаксис с ключевым словом WITH. До этого для создания временных наборов данных разработчики использовали вложенные подзапросы, временные таблицы или представления (views). Однако подзапросы часто приводили к громоздким и трудночитаемым конструкциям, особенно при многократном обращении к одному и тому же подзапросу в разных частях запроса. Временные таблицы требовали явного создания и удаления, что увеличивало накладные расходы. CTE предложили лаконичное и декларативное решение.

Первыми коммерческими СУБД, поддержавшими CTE, стали Oracle Database (с версии 9i, 2001 год) и IBM DB2. Microsoft SQL Server добавил поддержку в версии 2005 года. PostgreSQL поддерживает CTE с версии 8.4 (2009 год). MySQL, долгое время отстававший в этом отношении, ввёл поддержку только в версии 8.0 (2018 год). На сегодняшний день CTE являются стандартным инструментом в арсенале разработчика баз данных.

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

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

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

• Ключевое слово WITH — начинает определение CTE.
• cte_name — имя временного набора результатов, которое будет использоваться в основном запросе.
• Список столбцов (необязательно) — позволяет явно задать имена столбцов CTE. Если не указан, имена столбцов наследуются из запроса внутри CTE.
• Тело CTE — это оператор SELECT, который формирует набор данных. Тело может содержать любые допустимые конструкции SQL: объединения (JOIN), агрегации (GROUP BY), оконные функции, подзапросы и другие CTE.
• Основной запрос — следует после определения CTE и может ссылаться на cte_name как на обычную таблицу или представление. Основной запрос может содержать SELECT, INSERT, UPDATE или DELETE.

Пример простого CTE, вычисляющего среднюю зарплату по отделам:

``sql WITH dept_avg_salary AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) SELECT e.employee_id, e.salary, d.avg_salary FROM employees e JOIN dept_avg_salary d ON e.department_id = d.department_id WHERE e.salary > d.avg_salary; ``

В этом примере CTE dept_avg_salary вычисляет среднюю зарплату один раз, а основной запрос использует её для фильтрации сотрудников, чья зарплата выше средней по отделу.

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

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

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

Синтаксис:

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

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

``sql WITH RECURSIVE org_tree AS ( -- Анкер: сам менеджер SELECT employee_id, manager_id, employee_name, 1 AS level FROM employees WHERE employee_id = 101 UNION ALL -- Рекурсия: подчинённые SELECT e.employee_id, e.manager_id, e.employee_name, t.level + 1 FROM employees e JOIN org_tree t ON e.manager_id = t.employee_id ) SELECT * FROM org_tree; ``

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

Виды и классификация

CTE можно классифицировать по нескольким признакам:

• По рекурсивности: нерекурсивные (простые) и рекурсивные.
• По количеству: одиночные (одно CTE в блоке WITH) и множественные (несколько CTE, разделённых запятыми). Множественные CTE могут ссылаться друг на друга, образуя цепочку.
• По области видимости: обычные CTE (видны только в одном запросе) и материализованные CTE (в некоторых СУБД, например, PostgreSQL, можно управлять материализацией через подсказки MATERIALIZED / NOT MATERIALIZED). Материализованное CTE сохраняет результат во временном хранилище, что может улучшить производительность при многократном обращении, но увеличивает потребление памяти.

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

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

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

1. Многократное использование подзапроса. Если один и тот же подзапрос встречается в нескольких местах основного запроса (например, в WHERE и в SELECT), CTE позволяет вычислить его один раз и ссылаться по имени, что повышает производительность и снижает вероятность ошибок.

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

1. Замена представлений (views) для одноразовых задач. Если временный набор данных нужен только в одном запросе, CTE удобнее, чем создание представления, которое остаётся в схеме базы данных.

1. Оконные функции и агрегации. CTE часто используются в сочетании с оконными функциями (ROW_NUMBER, RANK, LAG и др.) для выполнения сложных аналитических вычислений.

Ограничения и критика

Несмотря на удобство, CTE имеют ряд ограничений:

• Производительность. В некоторых СУБД (например, в PostgreSQL до версии 12) CTE по умолчанию материализуются, то есть их результат полностью вычисляется и сохраняется во временной памяти. Это может привести к избыточному потреблению ресурсов, если CTE используется только один раз или если его можно было бы оптимизировать как подзапрос. Начиная с PostgreSQL 12, оптимизатор может «инлайнить» CTE, если это выгодно, но поведение можно явно контролировать ключевыми словами MATERIALIZED и NOT MATERIALIZED.
• Отсутствие индексов. CTE не являются физическими таблицами, поэтому к ним нельзя применять индексы напрямую. Оптимизатор строит план выполнения на основе статистики исходных таблиц.
• Ограничение на рекурсию. Глубина рекурсии ограничена настройками СУБД, что может быть проблемой для очень глубоких иерархий.
• Не все СУБД поддерживают рекурсивные CTE. Например, MySQL добавил поддержку только в версии 8.0, а SQLite — в версии 3.8.3 (2014 год). Старые версии этих СУБД не поддерживают рекурсию.

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

Примеры использования в различных СУБД

Хотя синтаксис CTE стандартизирован, в разных СУБД есть нюансы:

• PostgreSQL: поддерживает рекурсивные CTE, управление материализацией (MATERIALIZED / NOT MATERIALIZED), множественные CTE, а также модифицирующие CTE (с INSERT, UPDATE, DELETE внутри тела CTE).
• MySQL: поддерживает рекурсивные CTE с версии 8.0. Не поддерживает модифицирующие CTE.
• Microsoft SQL Server: поддерживает рекурсивные CTE, множественные CTE, но не поддерживает явное управление материализацией.
• Oracle Database: поддерживает рекурсивные CTE (начиная с 11g Release 2 — синтаксис WITH ... AS (...) SELECT ...), а также альтернативный синтаксис CONNECT BY для иерархических запросов, который исторически был более распространён.
• SQLite: поддерживает рекурсивные CTE с версии 3.8.3, но с некоторыми ограничениями (например, нельзя использовать ORDER BY в рекурсивном элементе).

Источники

• ISO/IEC 9075-2:2016, Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation).
• PostgreSQL Documentation: “WITH Queries (Common Table Expressions)”.
• MySQL 8.0 Reference Manual: “WITH (Common Table Expressions)”.
• Microsoft SQL Server Documentation: “WITH common_table_expression (Transact-SQL)”.
• Oracle Database SQL Language Reference: “WITH Clause”.
• SQLite Documentation: “WITH clause”.