Разработка
NTA

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

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

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

0
2 комментария
Электрический Паша

Отлично, спасибо.
А есть ещё рекурсивные CTE.

По хорошему вложенные запросы должны быть оптимизированы во что-то типа CTE, но как минимум SQLite так не умеет делать.

Ответить
Развернуть ветку
Василий Лебедев

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

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

Разве это верно для обычных подзапросов? Повторяться для каждой строки вложенный подзапрос будет только если он коррелирующий.

Ответить
Развернуть ветку
Читать все 2 комментария
null