Конструкция WITH в T-SQL или обобщенное табличное выражение (ОТВ)
Всем привет! Тема сегодняшнего материала будет посвящена обобщенным табличным выражениям языка T-SQL, мы с Вами узнаем, что это такое, а также рассмотрим примеры написания запросов с использованием этих самых обобщённых табличных выражений.
Для начала, конечно же, давайте поговорим о том, что вообще из себя представляют обобщенные табличные выражения, какие они бывают, рассмотрим синтаксис, для чего их можно использовать и в заключение разберем несколько примеров.
Что такое обобщенное табличное выражение?
Common Table Expression (CTE) или обобщенное табличное выражение (OTB) – это временные результирующие наборы (т.е. результаты выполнения SQL запроса), которые не сохраняются в базе данных в виде объектов, но к ним можно обращаться.
Главной особенностью обобщенных табличных выражений является то, что с помощью них можно писать рекурсивные запросы, но об этом чуть ниже, а сейчас давайте поговорим о том, в каких случаях нам могут пригодиться OTB, в общем, для чего они предназначены:
Обобщенное табличное выражение определяется с помощью конструкции WITH, и определить его можно как в обычных запросах, так и в функциях, хранимых процедурах, триггерах и представлениях.
Синтаксис:
После обобщенного табличного выражения, т.е. сразу за ним должен идти одиночный запрос SELECT, INSERT, UPDATE, MERGE или DELETE.
Какие бывают обобщенные табличные выражения?
Они бывают простые и рекурсивные.
Простые не включают ссылки на самого себя, а рекурсивные соответственно включают.
Рекурсивные ОТВ используются для возвращения иерархических данных, например, классика жанра это отображение сотрудников в структуре организации (чуть ниже мы это рассмотрим).
Примечание! Все примеры ниже будут рассмотрены в MS SQL Server 2008 R2.
В качестве тестовых данных давайте использовать таблицу TestTable, которая будет содержать идентификатор сотрудника, его должность и идентификатор его начальника.
Как видите, у директора отсутствует ManagerID, так как у него нет начальника. А теперь переходим к примерам.
Пример простого обобщенного табличного выражения
Для примера давайте просто выведем все содержимое таблицы TestTable с использованием обобщенного табличного выражения
Где TestCTE это и есть псевдоним результирующего набора, к которому мы и обращаемся.
В данном случае мы могли и не перечислять имена столбцов, так как они у нас уникальны. Можно было просто написать вот так:
Пример рекурсивного обобщенного табличного выражения
Теперь допустим, что нам необходимо вывести иерархический список сотрудников, т.е. мы хотим видеть, на каком уровне работает тот или иной сотрудник. Для этого пишем рекурсивный запрос:
В итоге, если мы захотим, мы можем легко получить список сотрудников определенного уровня, например, нам нужны только начальники отделов, для этого мы просто в указанный выше запрос добавим условие WHERE LevelUser = 1
При написании рекурсивного ОТВ нужно быть внимательным, так как неправильное его составление может привести к бесконечному циклу. Поэтому для этих целей есть опция MAXRECURSION, которая может ограничивать количество уровней рекурсии. Давайте представим, что мы не уверены, что написали рекурсивное обобщенное выражение правильно и для отладки напишем инструкцию OPTION (MAXRECURSION 5), т.е. отобразим только 5 уровня рекурсии, и если уровней будет больше, SQL инструкция будет прервана.
Запрос у нас отработал, что говорит о том, что мы написали его правильно и соответственно OPTION (MAXRECURSION 5) можно смело убрать.
Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.
Общие табличные выражения (CTE) стр. 1
Чтобы выяснить назначение общих табличных выражений, давайте начнем с примера.
Найти максимальную сумму прихода/расхода среди всех 4-х таблиц базы данных «Вторсырье», а также тип операции, дату и пункт приема, когда и где она была зафиксирована.
Задачу можно решить, например, следующим способом.
Здесь мы сначала объединяем всю имеющуюся информацию, а затем выбираем только те строки, у которых сумма не меньше, чем каждая из сумм той же выборки из 4-х таблиц.
Фактически, мы дважды написали код объединений четырех таблиц. Как избежать этого? Можно создать представление, а затем адресовать запрос уже к нему:
Так вот, CTE играет роль представления, которое создается в рамках одного запроса и, не сохраняется как объект схемы. Предыдущий вариант решения можно переписать с помощью CTE следующим образом:
Как видите, все аналогично использованию представления за исключением обязательных скобок, ограничивающих запрос; формально, достаточно лишь заменить CREATE VIEW на WITH. Как и для представления, в скобках после имени CTE может быть указан список столбцов, если нам потребуется включить их не все из подлежащего запроса и/или переименовать. Например, (я добавил дополнительно определение минимальной суммы в предыдущий запрос),
Общие табличные выражения позволяют существенно уменьшить объем кода, если многократно приходится обращаться к одним и тем же производным таблицам.
WITH обобщенное_табличное_выражение (Transact-SQL)
Задается временно именованный результирующий набор, называемый обобщенным табличным выражением (ОТВ). Он получается при выполнении простого запроса и определяется в области выполнения одиночной инструкции SELECT, INSERT, UPDATE, DELETE или MERGE. Это предложение может использоваться также в инструкции CREATE VIEW как часть определяющей ее инструкции SELECT. Обобщенное табличное выражение может включать ссылки на само себя. Такое выражение называется рекурсивным обобщенным табличным выражением.
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
column_name
Задается имя столбца в обобщенном табличном выражении. Повторяющиеся имена в определении одного обобщенного табличного выражения не допускаются. Количество заданных имен столбцов должно совпадать с количеством столбцов в результирующем наборе CTE_query_definition. Список имен столбцов необязателен только в том случае, если всем результирующим столбцам в определении запроса присвоены уникальные имена.
CTE_query_definition
Задается инструкция SELECT, результирующий набор которой заполняет обобщенное табличное выражение. Инструкция SELECT для CTE_query_definition должна соответствовать таким же требованиям, что и при создании представления, за исключением того, что обобщенное табличное выражение (ОТВ) не может определять другое ОТВ. Дополнительные сведения см. в подразделе «Замечания» и разделе CREATE VIEW (Transact-SQL).
Если определено несколько параметров CTE_query_definition, определения запроса должны быть соединены одним из следующих операторов над множествами: UNION ALL, UNION, EXCEPT или INTERSECT.
Remarks
Рекомендации по созданию и использованию обобщенных табличных выражений
Следующие рекомендации относятся к нерекурсивным обобщенным табличным выражениям. Рекомендации, применимые к рекурсивным обобщенным табличным выражениям, см. в расположенном ниже разделе Рекомендации по определению и использованию рекурсивных обобщенных табличных выражений.
Задание в одном обобщенном табличном выражении нескольких предложений WITH недопустимо. Например, если CTE_query_definition содержит вложенный запрос, этот вложенный запрос не может содержать вложенное предложение WITH, определяющее другое обобщенное табличное выражение.
Следующие предложения не могут использоваться в CTE_query_definition:
ORDER BY (за исключением случаев задания предложения TOP )
Предложение OPTION с указаниями запроса
Если обобщенное табличное выражение используется в инструкции, являющейся частью пакета, то за инструкцией, стоящей перед ней, должен следовать символ точки с запятой.
Запрос, ссылающийся на обобщенное табличное выражение, может использоваться для определения курсора.
В обобщенном табличном выражении могут быть ссылки на таблицы, находящиеся на удаленных серверах.
При выполнении обобщенного табличного выражения (ОТВ) между указаниями, ссылающимися на ОТВ, может быть конфликт с другими указаниями, обнаруживаемыми, когда ОТВ обращаются к их базовым таблицам, так же, как если бы указания ссылались на представления в запросах. Когда это происходит, запрос возвращает ошибку.
Рекомендации по созданию и использованию рекурсивных обобщенных табличных выражений
Следующие рекомендации применимы к определению рекурсивных обобщенных табличных выражений.
Определение рекурсивного обобщенного табличного выражения должно содержать по крайней мере два определения обобщенного табличного выражения запросов — закрепленный элемент и рекурсивный элемент. Может быть определено несколько закрепленных элементов и рекурсивных элементов, однако все определения запросов закрепленного элемента должны быть поставлены перед первым определением рекурсивного элемента. Все определения обобщенных табличных выражений запросов (ОТВ) являются закрепленными элементами, если только они не ссылаются на само ОТВ.
Закрепленные элементы должны объединяться одним из следующих операторов над множествами: UNION ALL, UNION, INTERSECT или EXCEPT. UNION ALL является единственным оператором над множествами, который может находиться между последним закрепленным элементом и первым рекурсивным элементом, а также может применяться при объединении нескольких рекурсивных элементов.
Количество столбцов членов указателя и рекурсивных элементов должно совпадать.
Тип данных столбца в рекурсивном элементе должен совпадать с типом данных соответствующего столбца в закрепленном элементе.
Предложение FROM рекурсивного элемента должно ссылаться на обобщенное табличное выражение expression_name только один раз.
Следующие элементы недопустимы в определении CTE_query_definition рекурсивного элемента:
PIVOT (Если уровень совместимости базы данных имеет значение 110 или больше. См. раздел Критические изменения в функциях компонента ядра СУБД в SQL Server 2016).
Указание, применимое к рекурсивной ссылке на обобщенное табличное выражение в определении CTE_query_definition.
Следующие рекомендации применимы к использованию рекурсивных обобщенных табличных выражений.
Представление, содержащее рекурсивное обобщенное табличное выражение, не может использоваться для обновления данных.
Курсоры могут определяться на запросах при помощи обобщенных табличных выражений. Обобщенное табличное выражение является аргументом select_statement, который определяет результирующий набор курсора. Для рекурсивных обобщенных табличных выражений допустимы только однонаправленные и статические курсоры (курсоры моментального снимка). Если в рекурсивном обобщенном табличном выражении указан курсор другого типа, тип курсора преобразуется в статический.
Возможности и ограничения общих табличных выражений в Azure Synapse Analytics и Система платформы аналитики (PDW)
Текущая реализация обобщенных табличных выражений в Azure Synapse Analytics и Система платформы аналитики (PDW) имеет следующие возможности и ограничения:
Обобщенное табличное выражение можно задать в инструкции CREATE TABLE AS SELECT (CTAS).
Обобщенное табличное выражение можно задать в инструкции CREATE REMOTE TABLE AS SELECT (CRTAS).
Обобщенное табличное выражение можно задать в инструкции CREATE EXTERNAL TABLE AS SELECT (CETAS).
Обобщенное табличное выражение может ссылаться на внешнюю таблицу.
Обобщенное табличное выражение может ссылаться на внешнюю таблицу.
В обобщенном табличном выражении можно задать несколько определений запросов обобщенных табличных выражений (ОТВ).
Обобщенное табличное выражение, которое включает ссылки на себя (рекурсивное обобщенное табличное выражение), не поддерживается.
Если обобщенное табличное выражение используется в инструкции, являющейся частью пакета, то за инструкцией, стоящей перед ней, должен следовать символ точки с запятой.
Примеры
A. Создание простого обобщенного табличного выражения
В следующем примере выводится общее количество заказов на продажу в год для каждого коммерческого представителя в Компания Adventure Works Cycles.
Б. Использование обобщенного табличного выражения для ограничения общего и среднего количества отчетов
В следующем примере выводится среднее количество заказов на продажу за все годы для коммерческих представителей.
В. Использование нескольких определений ОТВ (обобщенных табличных выражений) в одном запросе
В следующем примере показано, как определить несколько ОТВ в одном запросе. Обратите внимание, что для разделения определений запросов обобщенных табличных выражений используется запятая. Функция FORMAT, используемая для отображения денежных сумм в формате валюты, доступна в SQL Server 2012 и более поздних версиях.
Здесь приводится частичный результирующий набор.
Г. Использование рекурсивного обобщенного табличного выражения для отображения нескольких уровней рекурсии
Использование рекурсивного обобщенного табличного выражения для отображения двух уровней рекурсии
В следующем примере представлены руководители и отчитывающиеся перед ними служащие. Количество возвращаемых уровней ограничено двумя.
Использование рекурсивного обобщенного табличного выражения для отображения иерархического списка
В следующем примере добавляются имена руководителя и сотрудников, а также соответствующие им должности. Иерархия руководителей и служащих дополнительно выделяется с помощью соответствующих отступов на каждом уровне.
Использование подсказки MAXRECURSION для отмены инструкции
Подсказка MAXRECURSION может использоваться для предотвращения входа в бесконечный цикл из-за неверно сформированного рекурсивного CTE-выражения. В следующем примере преднамеренно формируется бесконечный цикл и используется указание MAXRECURSION для ограничения числа уровней рекурсии двумя.
После исправления ошибки в коде подсказка MAXRECURSION больше не нужна. В следующем примере приводится правильный код.
Д. Использование обобщенного табличного выражения для выборочного прохождения рекурсивной связи в инструкции SELECT
Е. Использование рекурсивного обобщенного табличного выражения в инструкции UPDATE
З. Использование нескольких привязок и рекурсивных элементов
В следующем примере несколько членов указателя и рекурсивных элементов используются для возврата всех предков указанного лица. Создается и заполняется значениями таблица для формирования генеалогии семьи, возвращаемой рекурсивным обобщенным табличным выражением.
I. Использование аналитических функций в рекурсивном обобщенном табличном выражении
Следующий пример демонстрирует проблему, которая может возникнуть при использовании аналитической или агрегатной функции в рекурсивной части обобщенного табличного выражения.
Следующие результаты являются ожидаемыми результатами выполнения запроса.
Следующие результаты являются фактическими результатами выполнения запроса.
N возвращает 1 для каждого прохода рекурсивной части ОТВ, так как в ROWNUMBER передается только подмножество данных для данного уровня рекурсии. Для каждой итерации рекурсивной части запроса в ROWNUMBER передается только одна строка.
Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)
К. Использование обобщенного табличного выражения в инструкции CTAS
В следующем примере создается новая таблица, содержащая общее количество заказов на продажу в год для каждого коммерческого представителя в Компания Adventure Works Cycles.
Л. Использование обобщенного табличного выражения в инструкции CETAS
В следующем примере создается новая внешняя таблица, содержащая общее количество заказов на продажу в год для каждого коммерческого представителя в Компания Adventure Works Cycles.
М. Использование нескольких разделенных запятыми обобщенных табличных выражений в инструкции
В следующем примере показано включение двух обобщенных табличных выражений в одну инструкцию. Обобщенные табличные выражения не поддерживают вложение (рекурсию).
Табличные выражения
называются подзапросы, которые используются там, где ожидается наличие таблицы. Существует два типа табличных выражений:
обобщенные табличные выражения.
Эти две формы табличных выражений рассматриваются в следующих подразделах.
Производные таблицы
— это табличное выражение, входящее в предложение FROM запроса. Производные таблицы можно применять в тех случаях, когда использование псевдонимов столбцов не представляется возможным, поскольку транслятор SQL обрабатывает другое предложение до того, как псевдоним станет известным. В примере ниже показана попытка использовать псевдоним столбца в ситуации, когда другое предложение обрабатывается до того, как станет известным псевдоним:
Попытка выполнить этот запрос выдаст следующее сообщение об ошибке:
Причиной ошибки является то обстоятельство, что предложение GROUP BY обрабатывается до обработки соответствующего списка инструкции SELECT, и при обработке этой группы псевдоним столбца enter_month неизвестен.
Эту проблему можно решить, используя производную таблицу, содержащую предшествующий запрос (без предложения GROUP BY), поскольку предложение FROM исполняется перед предложением GROUP BY:
Результат выполнения этого запроса будет таким:
Обычно табличное выражение можно разместить в любом месте инструкции SELECT, где может появиться имя таблицы. (Результатом табличного выражения всегда является таблица или, в особых случаях, выражение.) В примере ниже показывается использование табличного выражения в списке выбора инструкции SELECT:
Результат выполнения этого запроса:
Обобщенные табличные выражения
называется именованное табличное выражение, поддерживаемое языком Transact-SQL. Обобщенные табличные выражения используются в следующих двух типах запросов:
Эти два типа запросов рассматриваются в следующих далее разделах.
OTB и нерекурсивные запросы
Нерекурсивную форму OTB можно использовать в качестве альтернативы производным таблицам и представлениям. Обычно OTB определяется посредством предложения WITH и дополнительного запроса, который ссылается на имя, используемое в предложении WITH. В языке Transact-SQL значение ключевого слова WITH неоднозначно. Чтобы избежать неопределенности, инструкцию, предшествующую оператору WITH, следует завершать точкой с запятой.
далее показано использование OTB в нерекурсивных запросах. В примере ниже используется стандартное решение (здесь используется тестовая база данных AdventureWorks2012 из исходников):
Запрос в этом примере выбирает заказы, чьи общие суммы налогов (TotalDue) большие, чем среднее значение по всем налогам, и плата за перевозку (Freight) которых больше чем 40% среднего значения налогов. Основным свойством этого запроса является его объемистость, поскольку вложенный запрос требуется писать дважды. Одним из возможных способов уменьшить объем конструкции запроса будет создать представление, содержащее вложенный запрос. Но это решение несколько сложно, поскольку требует создания представления, а потом его удаления после окончания выполнения запроса. Лучшим подходом будет создать OTB. В примере ниже показывается использование нерекурсивного OTB, которое сокращает определение запроса, приведенного выше:
Синтаксис предложения WITH в нерекурсивных запросах имеет следующий вид:
OTB и рекурсивные запросы
В этом разделе представляется материал повышенной сложности. Поэтому при первом его чтении рекомендуется его пропустить и вернуться к нему позже. Посредством OTB можно реализовывать рекурсии, поскольку OTB могут содержать ссылки на самих себя. Основной синтаксис OTB для рекурсивного запроса выглядит таким образом:
Параметры cte_name и column_list имеют такое же значение, как и в OTB для нерекурсивных запросов. Тело предложения WITH состоит из двух запросов, объединенных оператором UNION ALL. Первый запрос вызывается только один раз, и он начинает накапливать результат рекурсии. Первый операнд оператора UNION ALL не ссылается на OTB. Этот запрос называется опорным запросом или источником.
Второй запрос содержит ссылку на OTB и представляет ее рекурсивную часть. Вследствие этого он называется рекурсивным членом. В первом вызове рекурсивной части ссылка на OTB представляет результат опорного запроса. Рекурсивный член использует результат первого вызова запроса. После этого система снова вызывает рекурсивную часть. Вызов рекурсивного члена прекращается, когда предыдущий его вызов возвращает пустой результирующий набор.
Оператор UNION ALL соединяет накопившиеся на данный момент строки, а также дополнительные строки, добавленные текущим вызовом рекурсивного члена. (Наличие оператора UNION ALL означает, что повторяющиеся строки не будут удалены из результата.)
Наконец, параметр outer_query определяет внешний запрос, который использует OTB для получения всех вызовов объединения обеих членов.
Для демонстрации рекурсивной формы OTB мы используем таблицу Airplane, определенную и заполненную кодом, показанным в примере ниже:
Таблица Airplane состоит из следующих 11 строк:
В примере ниже показано применение предложения WITH для определения запроса, который вычисляет общую стоимость каждой сборки:
Предложение WITH определяет список OTB с именем list_of_parts, состоящий из трех столбцов: assembly1, quantity и cost. Первая инструкция SELECT в примере вызывается только один раз, чтобы сохранить результаты первого шага процесса рекурсии. Инструкция SELECT в последней строке примера отображает следующий результат:
Запрос в примере ниже вычисляет стоимость каждой сборки со всеми ее составляющими:
Результат выполнения этого запроса дает следующий результат:
В рекурсивных запросах на OTB налагается несколько следующих ограничений:
определение OTB должно содержать, по крайней мере, две инструкции SELECT (опорный член и рекурсивный член), объединенные оператором UNION ALL;
опорный член и рекурсивный член должны иметь одинаковое количество столбцов (это является прямым следствием использования оператора UNION ALL);
столбцы в рекурсивном члене должны иметь такой же тип данных, как и соответствующие столбцы в опорном члене;
предложение FROM рекурсивного члена должно ссылаться на имя OTB только один раз;
определение рекурсивного члена не может содержать следующие параметры: SELECT DISTINCT, GROUP BY, HAVING, агрегатные функции, TOP и подзапросы. Кроме этого, единственным типом операции соединения, разрешенной в определении запроса, является внутреннее соединение.
