В блог
CTE - важный помощник аналитика - IT Resume

CTE - важный помощник аналитика

Дата последнего обновления: 05.03.2025
Дата размещения: 31.01.2025
Евгений Буторин

Работа аналитика очень разнообразна и часто нам приходится писать достаточно большие запросы - более 100 строк на один SELECT. Такое количество строк формируется за счет большого количества джоинов с вложенными подзапросами.

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

Что такое CTE

CTE (Common Table Expression или Обобщенные Табличные Выражения) - это временный результат запроса SQL, который можно использовать в рамках последующих SELECT, INSERT, UPDATE и DELETE. Проще говоря, это временная таблица, которая удаляется после выполнения запроса.

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

Когда нужно использовать

CTE уместно использовать в следующих случаях:

  1. При написании сложных запросов. CTE позволяет выделять все вложения в отдельные блоки, без использования громоздких подзапросов, в том числе многоуровневых.
  2. Для создания рекурсивных запросов, например, для извлечения данных из таблиц иерархии.
  3. При использовании в рамках запроса одной таблицы многократно.

Синтаксис

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

  1. Благодаря 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 смотрится более элегантно и читабельно.

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

Подпишитесь на нашу рассылку
Имя*
Email*
Номер телефона*
Заполняя данную форму, Вы соглашаетесь с политикой конфиденциальности
Никакого спама. Только точечные рассылки с лучшими материалами.