Время прочтения: 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).
Заключение:
Таким образом мы познакомились с обобщенными табличными выражениями и убедились в том, что использование данного инструмента, совместно с остальными методами оптимизации запросов, помогает увеличить эффективность извлечения и обработки данных.