CTE

CTE, или Common Table Expressions — один из видов запросов в системах управления базами данных. На русском языке они называются обобщенными табличными выражениями. Результаты табличных выражений можно временно сохранять в памяти и обращаться к ним повторно.

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

Чаще всего говорят об использовании CTE в СУБД PostgreSQL. Но эту возможность поддерживают и другие системы управления, например Oracle или MySQL. Названия запроса могут различаться в разных базах данных. Например, в Oracle он называется «факторинг подзапроса».

Для чего нужны CTE

• Написание сложных запросов — использование конструкции помогает уменьшить размер кода и упростить его, сделать более читаемым.
• Ускорение работы программ в случаях, когда нужно много раз подряд обращаться к одной и той же части базы, — временное хранение помогает оптимизировать выполнение. Создается структура данных, которая временно хранится в кэше, поэтому информацию не требуется искать каждый раз.
• Рекурсивный обход таблиц, в котором помогают общие табличные выражения. Существует особый их подвид — рекурсивные CTE.
• Создание представлений, или View, в SELECT-части запроса.
• Оптимизация работы, так как другие варианты временного хранения и сложного доступа часто более ресурсоемкие.
• Создание более понятного кода, который легче поддерживать.

Каким образом используются CTE

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

Это может быть сложно понять по описанию, поэтому рассмотрим применение CTE на примере.

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

Перед началом запроса пишется ключевое слово WITH, после него — название временной структуры и ключевое слово AS. Открываются фигурные скобки, а внутри них описывается наполнение структуры.

В отличие от обычного создания таблицы, наполнение описывается не значениями, а запросами. Вместо того чтобы написать «посетитель номер 310 по имени Вася Кузнецов», мы пишем «посетитель из N-строки N-таблицы, номер получаем из N-блока N-структуры, имя — из N-блока и объединяем с фамилией, полученной из N-блока». В результате получается временный результат, с которым можно работать как с таблицей.

Особенности CTE

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

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

Табличные выражения можно использовать вместе с блоками SELECT, которые показывают те или иные данные, или вместе с другими блоками — теми, которые модифицируют информацию в таблице. Это, например, INSERT, UPDATE, DELETE (вставка, обновление, удаление данных).

Существуют рекурсивные CTE, которые мы упомянули ранее. Их особенность в том, что они могут обращаться к собственным результатам. В PostgreSQL они описываются как WITH RECURSIVE — к ключевому слову WITH добавляется пометка о том, что запрос рекурсивный.

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

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

Упрощают понимание. В рамках CTE происходит разбиение сложного запроса на несколько «блоков»: сначала описывается временная структура, а потом данные получают уже из нее. Получение данных оказывается последовательным и понятным.

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

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

Имеют широкую поддержку. CTE используются во многих популярных системах, просто называются по-разному. Если вы освоите табличные выражения, например в PostgreSQL, то в будущем вам будет легче познакомиться с похожими структурами в Oracle или других СУБД.

Недостатки CTE

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

Имеют особенности оптимизации. Есть еще одна проблема. Она существует из-за оптимизатора — внутренней структуры PostgreSQL, которая занимается тем, что упрощает и оптимизирует введенные человеком запросы. Внутреннее выражение, то, что находится после ключевых слов WITH…AS, оптимизируется не так хорошо, как более простые функции. Поэтому есть риск, что CTE будет работать медленно, несмотря на то что по своей сути должно быть быстрым.

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