Работа аналитика очень разнообразна, и часто ему приходится писать достаточно большие запросы — более 100 строк на один SELECT. Такое количество строк формируется за счёт большого количества джоинов со вложенными подзапросами.
Если такой скрипт передать другому коллеге или вернуться к нему через несколько месяцев, то разобраться в скрипте становится сложно. Чтобы упростить восприятие скрипта, аналитики используют CTE.
Что такое CTE
CTE (Common Table Expression, или обобщённые табличные выражения) — это временный результат запроса SQL, который можно использовать в рамках последующих SELECT, INSERT, UPDATE и DELETE. Проще говоря, это временная таблица, которая удаляется после выполнения запроса.
CTE позволяет упрощать читаемость сложных запросов, а также ускорять их за счёт однократной материализации данных. Ещё одна особенность CTE — возможность обращения к самой себе, то есть рекурсивность.
Когда нужно использовать
CTE уместно использовать в следующих случаях:
- При написании сложных запросов. CTE позволяет выделять все вложения в отдельные блоки без использования громоздких подзапросов, в том числе многоуровневых.
- Для создания рекурсивных запросов — например, для извлечения данных из таблиц иерархии.
- При использовании в рамках запроса одной таблицы многократно.
Синтаксис
CTE имеет следующий синтаксис:
WITH [RECURSIVE] CTE_имя AS (SQL_выражение)
SQL_выражение;
Обязательные элементы CTE — ключевые слова WITH и AS. В качестве SQL-выражения внутри CTE нужно указать запрос к таблице, в том числе с учётом агрегации и условий. Второе SQL-выражение может содержать SELECT, INSERT, UPDATE и DELETE.
Типы CTE в SQL
CTE бывают рекурсивные и нерекурсивные. В основном, в своей практике многие используют нерекурсивные, так как таблиц с иерархией обычно немного.
Нерекурсивные
Нерекурсивные CTE позволяют сделать временные запросы и выглядят следующим образом:
with s as (
select * from sales
where pricewithdisc >= 600
and date >= '2024-12-01'
)
select * from s
В результате получим следующее:
Как видите, такой запрос позволяет предобработать таблицу, забрав только необходимые данные. После окончания запроса CTE удаляется и повторно использовать его нельзя.
Рекурсивные
Рекурсивные CTE позволяют обращаться сами к себе. Это удобно, когда у вас есть таблица с иерархией, например, таблица сотрудников в компании или таблица с многоуровневыми ответами пользователей.
Для запуска рекурсивного CTE нужно после WITH указать RESURSIVE. Выглядит это следующим образом:
with resursive s as (
select * from sales
where pricewithdisc >= 600
and date >= '2024-12-01'
and countryname = 'Казахстан'
union all
select s1.* from sales s1
JOIN s on s.countryname = s1.countryname and s1.product = 'Витамин D3'
where s1.date >= '2024-12-01'
)
select * from s
В результате выполнения запроса мы получим следующее:
В данном CTE мы обращаемся к первой части CTE прямо внутри текущего запроса, что очень удобно.
Недостатки CTE
CTE — мощный инструмент, но не универсальный. Проблема в том, что CTE достаточно ресурсоёмкий, так как данные хранятся в кэше. При использовании в качестве SQL-выражения внутри CTE объёмной таблицы, запрос может стать неоптимальным и значительно увеличить время выполнения скрипта.
Дополнительные возможности CTE
Благодаря CTE можно создавать ручные справочники. Например, вам нужно сделать справочник для одного поля и сразу же к нему обратиться.
В данном случае вы можете прописать CASE WHEN прямо в SELECT’e и отфильтровать по нему, но выглядеть это будет громоздко и неоптимально. CTE упростит этот процесс:
with s as (
select 1 as ID, 'Коллаген' as product
UNION ALL
select 2 as ID, 'Омега 3' as product
)
select * from sales s1
left join s on s.product = s1.product
where pricewithdisc >= 600
and date >= '2024-12-01'
and countryname = 'Казахстан'
and s.id = 1
А вот как та же самая запись делается через CASE WHEN:
select s1.*,
case when product = 'Коллаген' then 1
when product = 'Омега 3' then 2
end as ID
from sales s1
where pricewithdisc >= 600
and date >= '2024-12-01'
and countryname = 'Казахстан'
and (case when product = 'Коллаген' then 1
when product = 'Омега 3' then 2
end) = 1
Согласитесь, что CTE смотрится более элегантно и читабельно.
Если вы работаете в Oracle, то для CTE вы можете использовать хинты. Хинты — это команды для указания, как читать запрос. В случае с CTE можно использовать 2 основных:
/*+inline*/не материализует таблицу. Оптимально использовать, где запрос используется один раз — например, при обработке справочника./*+materialize*/материализует таблицу, то есть при обращении к ней несколько раз, скорость обращения возрастёт.
Конечно, кроме этих хинтов можно одновременно указывать и другие, например full, no_index, parallel и другие. Но два указанных выше хинта применимы именно к CTE.
Использование нескольких СТЕ
В запросах часто используется множество JOIN’ов и подзапросов, поэтому в этом случае можно использовать множество CTE. Для этого необходимо поставить запятую после окончания первого CTE и написать второе через синтаксис:
, CTE_имя_2 AS (SQL_выражение)
На реальных данных это выглядит следующим образом:
with s as (
select product,sum(cnt) as sales from sales
WHERE date >= '2024-12-01'
group by product)
, r as (
SELECT product,sum(quantity) as quantity from remains
group by product)
SELECT s.*,r.quantity from s
left join r on r.product = s.product
Таким образом, нам удалось получить две таблицы, предобработать в них данные и после этого объединить.
CTE — очень удобный инструмент в руках аналитика. Использование его в работе значительно облегчает процесс написания скриптов и его редактирование.