Разработка
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).

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

{ "author_name": "NTA", "author_type": "editor", "tags": [], "comments": 1, "likes": 2, "favorites": 2, "is_advertisement": false, "subsite_label": "dev", "id": 259356, "is_wide": true, "is_ugc": false, "date": "Wed, 16 Jun 2021 16:30:22 +0300", "is_special": false }
0
1 комментарий
Популярные
По порядку
2

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

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

Ответить
Читать все 1 комментарий
4 скрытых трат на программное обеспечение, которые необходимо предвидеть ПОСЛЕ публикации приложения

Давайте представим, что после нескольких месяцев напряженной работы ваше мобильное приложение, наконец, готово к работе. Но когда вы просматриваете свои финансовые отчеты, вы замечаете, что ваши ежемесячные расходы не уменьшились, а наоборот- выросли. Что происходит?

Можно ли продолжать работать в той же компании после выгорания: история продакт-менеджера Тинькофф

В Тинькофф — 28 000 сотрудников, и у каждого своя история. Кто-то легко справляется с работой, и после всех задач спокойно отключается и идет отдыхать. Другим все может даваться труднее, даже сложно в уйти в отпуск — думает, «как я всё здесь брошу». Мы начинаем серию статей от лица наших сотрудников, которые делятся своим опытом: какие появлялись…

«Стартап-полка»: Самокат набирает производителей альтернативных продуктов

Онлайн-ритейлер Самокат совместно с Ассоциацией Производителей Альтернативных Пищевых Продуктов объявляют сбор заявок от инновационных производителей продуктов для участия в «стартап-полке» Самоката. Лучшие продукты попадут в постоянный ассортимент Самоката в раздел «Супермаркет» уже этой осенью.

NASA и SpaceX отправят научный зонд для поиска жизни на спутнике Юпитера в 2024 году Статьи редакции

Запуск миссии Europa Clipper обойдётся в $178 млн — столько NASA выделяет компании Илона Маска по условиям контракта.

Восточная Техника успешно автоматизирует процессы управления складами на базе решения Columbus-WMS
Как я попался на офлайн-развод в «Pure», а затем нашел актрису

В этом году я встретил на «Pure» новый для меня вид мошенничества, что стоило мне больше 2352 грн. Смысл развода состоит в том, чтобы завести на свидание в подставное кафе и вынудить оплатить счет с сильно завышенной суммой.

Соцсети как инструмент поиска и привлечения сотрудников в крупную компанию

В этом кейсе расскажем, как искали новых "бойцов" для работы в одном из филиалов российского провайдера цифровых услуг и сервисов через Вонтакте и Instagram и привлекли 496 заявок по цене 120 рублей за каждую.

Возможности, как в корпорации, а гибкость, как у стартапа. Что думают о своей работе сотрудники DiDi

Пять представителей российского офиса компании рассказали о корпоративной культуре, рабочем дне, языковом барьере, профессиональных вызовах.

На конференции «B Word» Илон Маск в очередной раз поменял свою позицию по биткоину

В ходе конференции где также присутствовали Джек Дорси и Кэти Вуд, Маск отметил, что он является владельцем биткойнов не только через баланс Tesla и SpaceX, но и лично владеет биткойнами, эфиром и Dogecoin.

Как мы создали программный продукт для НКО, не имеющий аналогов в России, и (пока что) заработали на нём только геморрой

Это история о том, что бывает, когда одна типичная для некоммерческих организаций черта – ожидание всяческих преференций – выходит на первый план, превращается в требовательную инфантильность и рубит все отношения между заказчиком и исполнителем.
Мы почти бесплатно сделали ПО для НКО на базе Битрикс24, не имеющее аналогов в России, а вместо…

Пластиковый «дом будущего» должен был решить проблему нехватки жилья, а стал аттракционом в Диснейленде Статьи редакции

В конце 50-х архитекторы в США построили здание из пластмассы и «оснастили» его бытовыми предметами грядущих десятилетий — атомной микроволновкой, ультразвуковой посудомойкой, движущимися проекторами, гигантским экраном, пластиковой мебелью и посудой.

У Дома будущего Monsanto было четыре крыла Iconichouses
null