Работа аналитика очень разнообразна и часто нам приходится писать достаточно большие запросы - более 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 является очень удобным инструментом в руках аналитика. Использование его в работе значительно облегчает процесс написания скриптов и его редактирование.