SQL

SQL. Обобщенное табличное выражение и способы его использования

Время прочтения: 3 мин.

Обобщенное табличное выражение является общим инструментов для многих баз данных. Рассмотрим конкретнее, что же это такое и как с ним работать на примере средств MS SQL.

Англ. Common Table Expression (CTE) — результаты запроса, которые можно использовать множество раз в других запросах. То есть, запросом мы достаем данные, и они помещаются в пространство памяти, аналогично временному представлению, которое физически не сохраняется в виде объектов. Далее мы работаем с получившейся конструкцией как с таблицей, используя такие конструкции как SELECT, UPDATE, INSERT и DELETE.

Выведем количество сотрудников, устроившихся на работу, в разбивке по годам:

WITH TABLE_CTE (FIO, ID_DEPART, BEGIN_YEAR)
AS
(
SELECT FIO, ID_DEPART, YEAR(DATE_BEGIN) as BEGIN_YEAR
FROM EMPL
)
SELECT count(FIO) as COUNT_SOTR, BEGIN_YEAR
FROM TABLE_CTE
group by BEGIN_YEAR

Так же обобщенное табличное выражение можно составить из результатов нескольких запросов. Последний результирующий запрос обращается к данным нижнего CTE(TABLE_CTE2), но может и к любому из них:

WITH 
TABLE_CTE1(FIO, YEAR_EMPL) AS
(
SELECT FIO, YEAR(DATE_BEGIN) as YEAR_EMPL
FROM EMPL
),
TABLE_CTE2 (COUNT_FIO, YEAR_EMPL) AS
(
SELECT count(FIO) as COUNT_FIO, YEAR_EMPL
FROM TABLE_CTE1
group by YEAR_EMPL
)
SELECT *
FROM TABLE_CTE2

Основные способы использования:

— для улучшения читаемости запроса в случае сложных запросов (разительно уменьшают размер кода);

— в случаях, когда нужно много раз обращаться к одним и тем же таблицам/выборкам из таблиц;

— для создания представлений (VIEW) в части SELECT;

— для написания рекурсивных запросов.

Отличия от вложенного запроса:

— вложенный запрос повторяется для каждой строки из нашей выборки, что повышает стоимость выполнения запроса.

Отличия от временной таблицы:

— заполнение временной таблицы при больших объемах создает нагрузку на диск;

— исполнение запросов с использованием временной таблицы увеличивает время их выполнения из-за места хранения данного типа таблиц (tempdb).

Заключение:

Таким образом мы познакомились с обобщенными табличными выражениями и убедились в том, что использование данного инструмента, совместно с остальными методами оптимизации запросов, помогает увеличить эффективность извлечения и обработки данных.

Советуем почитать