MSSql: Использование оператора APPLY в TSql. Ms sql outer apply

U-SQL SELECT, выбрав из CROSS APPLY и OUTER APPLY

Часто при обработке некоторых более сложных значение в столбце, такие как массив байтов, строка, документ КАРТЫ, МАССИВ, JSON или XML это необходимо для получения более чем для одного значения, например весь набор строк информации на значение столбца.

U-SQL предоставляет оператор CROSS APPLY и OUTER APPLY, который оценивает выражения создания набора строк с правой стороны от каждой строки и ее ячеек отдельных столбцов набора строк в левой части окна. Результатом является сочетание столбцов из обоих наборов строк, где получить повторяющиеся значения левой набора строк для каждого результата выражения справа набора строк.

Если указано CROSS APPLY, выражение справа строк возвращает пустой набор строк для этой строки строки не создаются для строки левой набора строк.

При указании OUTER APPLY создается одна строка для каждой строки набора строк слева даже в том случае, если выражение справа строк возвращает пустой набор строк для этой строки.

Этот оператор также известен как РАСПРЕДЕЛИТЕЛЬНЫЙ в других диалектами SQL.

Так как реализация полностью обобщенных CROSS APPLY процессора запроса масштабирования — сложная задача, U-SQL поддерживает лишь несколько специальных типов выражений, которые могут быть применены к источнику данных набора строк. Первый тип выражения использует встроенные выражения EXPLODE() и второй — выражение, предоставляющее определяемый пользователем оператор, называется применения.

Синтаксис

Apply_Expression := Rowset_Source Apply_Operator Explode_Expression | Rowset_Source Apply_Operator Applier_Expression

Семантика элементы синтаксиса

Rowset_SourceОпределяет входные данные, на котором Взрыв или применения выражений, применяются построчно. Дополнительные сведения об источнике строк см. в разделе SELECT (U-SQL): Предложение FROM [AnchorLink].
Apply_OperatorЗадает тип операции применить: INNER или OUTER APPLY.
Apply_Operator := 'CROSS' 'APPLY' | 'OUTER' 'APPLY'.
Если указано CROSS APPLY, выражение справа строк возвращает пустой набор строк для этой строки строки не создаются для строки левой набора строк.

При указании OUTER APPLY создается одна строка для каждой строки набора строк слева даже в том случае, если выражение справа строк возвращает пустой набор строк для этой строки.
Explode_Expression [AnchorLink]Включает экземпляр типа SQL. МАССИВ или SQL. СОПОСТАВЛЕНИЕ в набор строк, где каждая пара ключ значение или элемента соответственно сопоставлен в строку. Дополнительные сведения см. в разделе РАЗВЕРНУТЬ выражение (U-SQL). [AnchorLink].
Applier_Expression [AnchorLink]Является выражением U SQL, возвращающий экземпляр IApplier определяемый пользователем оператор, который в свою очередь принимает каждой строки входного набора строк в 0 для n строк указанной схемы объекта применения предложения. Дополнительные сведения см. в разделе с помощью применения с применения определяемого пользователем ОПЕРАТОРА (U-SQL) [AnchorLink].

msdn.microsoft.com

Использование оператора APPLY в TSql / Хабр

Недавно, реализуя некоторый код доступа к данным, я столкнулся с задачей выбора последних N записей для каждой сущности. Пользователь kuda78 подсказал вместо многоэтажной выборки использовать метод SelectMany. Исследуя, какой SQL код создает LinqToSQL, я натолкнулся на интересный SQL оператор APPLY.

Как гласит MSDN эта команда выполняет следующее: http://technet.microsoft.com/en-us/library/ms175156.aspxОператор APPLY позволяет вызывать возвращающую табличное значение функцию для каждой строки, возвращаемой внешним табличным выражением запроса. Возвращающая табличное значение функция выступает в роли правого входа, а внешнее табличное выражение — в роли левого входа. Правый вход оценивается для каждой строки из левого входа, а созданные строки объединяются для конечного вывода. Список столбцов, созданных оператором APPLY, является набором столбцов в левом входе, за которым следует список столбцов, возвращенный правым входом. Как оказалось APPLY очень хорошо подходит к решению поставленной задачи.

Давайте рассмотрим на примере:Задача: Выбрать 10 последних заказов для каждого заказчика. Пускай имеем следующую простую структуру БД:

CREATE TABLE Customer ( CustomerID INT PRIMARY KEY, CustomerName NVARCHAR(30) NOT NULL ) CREATE TABLE Nomenclature ( NomenclatureID INT PRIMARY KEY, NomenclatureName NVARCHAR(30) NOT NULL, Price MONEY NOT NULL ) CREATE TABLE Deal ( DealID INT IDENTITY(1, 1) PRIMARY KEY, CustomerID INT NOT NULL, NomenclatureID INT NOT NULL, [Count] DECIMAL(8,2) NOT NULL, DealDate DATETIME NOT NULL )

Теперь нам надо выбрать 10 последних заказов для каждого заказчика. Раньше мы пользовались следующим подходом: сначала для каждого заказчика выбирал дату, начиная с которой у него было 10 заказов, а потом выбирал все заказы с этой даты.

SELECT d.DealDate, c.CustomerName, n.NomenclatureName, n.Price, d.Count FROM Customer c JOIN Deal d ON d.CustomerID = c.CustomerID JOIN (SELECT c.CustomerID, (SELECT MIN(lastDeals.DealDate) FROM (SELECT TOP 10 d1.DealDate FROM Deal d1 WHERE d1.CustomerID = c.CustomerID ORDER BY d1.DealDate DESC) LastDeals) LastDealDate FROM Customer c) ld ON ld.CustomerID = c.CustomerID JOIN Nomenclature n ON n.NomenclatureID = d.NomenclatureID WHERE d.DealDate >= ld.LastDealDate ORDER BY c.CustomerName, d.DealDate DESC * Для простоты я специально сделал допущение, что 2 заказа в один и тот же момент времени быть не могут.

С использованием APPLY SQL код приобрел большую читаемость:

SELECT d.DealDate, c.CustomerName, n.NomenclatureName, n.Price, d.Count FROM Customer c OUTER APPLY (SELECT TOP 10 d1.* FROM Deal d1 Where d1.CustomerID = c.CustomerID ORDER BY d1.DealDate DESC) d INNER JOIN Nomenclature n ON n.NomenclatureID = d.NomenclatureID ORDER BY c.CustomerName, d.DealDate DESC План и время выполнения запроса при наличии всех необходимых индексов также вселяют уверенность, что эта функция еще неоднократно нам пригодится.

Файл создания бд с индексами: CreateDB.txt Файл с SQL запросами: Queries.txt

habr.com

sql - Пример реальной жизни, когда использовать OUTER/CROSS APPLY в SQL

Существуют различные ситуации, когда вы не можете избежать CROSS APPLY или OUTER APPLY.

У вас есть две таблицы.

МАСТЕР-ТАБЛИЦА

x------x--------------------x | Id | Name | x------x--------------------x | 1 | A | | 2 | B | | 3 | C | x------x--------------------x

ТАБЛИЦА ДЕТАЛЕЙ

x------x--------------------x-------x | Id | PERIOD | QTY | x------x--------------------x-------x | 1 | 2014-01-13 | 10 | | 1 | 2014-01-11 | 15 | | 1 | 2014-01-12 | 20 | | 2 | 2014-01-06 | 30 | | 2 | 2014-01-08 | 40 | x------x--------------------x-------x

&nbsp CROSS APPLY

Существует много ситуаций, когда нам нужно заменить INNER JOIN на CROSS APPLY.

1. Если мы хотим присоединиться к 2 таблицам на TOP n результатах с INNER JOIN функциональностью

Рассмотрим, нужно ли нам выбирать Id и Name из Master и последние две даты для каждого Id из Details table.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY FROM MASTER M INNER JOIN ( SELECT TOP 2 ID, PERIOD,QTY FROM DETAILS D ORDER BY CAST(PERIOD AS DATE)DESC )D ON M.ID=D.ID

Вышеприведенный запрос генерирует следующий результат.

x------x---------x--------------x-------x | Id | Name | PERIOD | QTY | x------x---------x--------------x-------x | 1 | A | 2014-01-13 | 10 | | 1 | A | 2014-01-12 | 20 | x------x---------x--------------x-------x

См., он сгенерировал результаты для последних двух дат с последними двумя датами Id, а затем присоединил эти записи только к внешнему запросу на Id, что неверно. Для этого нам нужно использовать CROSS APPLY.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY FROM MASTER M CROSS APPLY ( SELECT TOP 2 ID, PERIOD,QTY FROM DETAILS D WHERE M.ID=D.ID ORDER BY CAST(PERIOD AS DATE)DESC )D

и формирует его результат.

x------x---------x--------------x-------x | Id | Name | PERIOD | QTY | x------x---------x--------------x-------x | 1 | A | 2014-01-13 | 10 | | 1 | A | 2014-01-12 | 20 | | 2 | B | 2014-01-08 | 40 | | 2 | B | 2014-01-06 | 30 | x------x---------x--------------x-------x

Вот работа. Запрос внутри CROSS APPLY может ссылаться на внешнюю таблицу, где INNER JOIN не может этого сделать (выдает ошибку компиляции). При нахождении последних двух дат, соединение выполняется внутри CROSS APPLY т.е. WHERE M.ID=D.ID.

2. Когда нам нужны функции INNER JOIN, используя функции.

CROSS APPLY может использоваться как замена INNER JOIN, когда нам нужно получить результат из таблицы Master и function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY FROM MASTER M CROSS APPLY dbo.FnGetQty(M.ID) C

И вот функция

CREATE FUNCTION FnGetQty ( @Id INT ) RETURNS TABLE AS RETURN ( SELECT ID,PERIOD,QTY FROM DETAILS WHERE ID=@Id )

который породил следующий результат:

x------x---------x--------------x-------x | Id | Name | PERIOD | QTY | x------x---------x--------------x-------x | 1 | A | 2014-01-13 | 10 | | 1 | A | 2014-01-11 | 15 | | 1 | A | 2014-01-12 | 20 | | 2 | B | 2014-01-06 | 30 | | 2 | B | 2014-01-08 | 40 | x------x---------x--------------x-------x

&nbsp ВНЕШНЕЕ ПРИМЕНЕНИЕ

1. Если мы хотим объединить 2 таблицы в TOP n с помощью LEFT JOIN функциональности

Рассмотрим, нужно ли нам выбирать Id и Name из Master и последние две даты для каждой таблицы Id из Details.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY FROM MASTER M LEFT JOIN ( SELECT TOP 2 ID, PERIOD,QTY FROM DETAILS D ORDER BY CAST(PERIOD AS DATE)DESC )D ON M.ID=D.ID

который образует следующий результат:

x------x---------x--------------x-------x | Id | Name | PERIOD | QTY | x------x---------x--------------x-------x | 1 | A | 2014-01-13 | 10 | | 1 | A | 2014-01-12 | 20 | | 2 | B | NULL | NULL | | 3 | C | NULL | NULL | x------x---------x--------------x-------x

Это приведет к неправильным результатам, т.е. приведет к появлению только последних двух данных даты из таблицы Details независимо от Id, хотя мы присоединяемся к Id. Поэтому правильное решение использует OUTER APPLY.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY FROM MASTER M OUTER APPLY ( SELECT TOP 2 ID, PERIOD,QTY FROM DETAILS D WHERE M.ID=D.ID ORDER BY CAST(PERIOD AS DATE)DESC )D

который образует следующий желаемый результат

2. Когда нам нужны функции LEFT JOIN, используя functions.

OUTER APPLY можно использовать в качестве замены LEFT JOIN, когда нам нужно получить результат из таблицы Master и function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY FROM MASTER M OUTER APPLY dbo.FnGetQty(M.ID) C

И функция здесь.

CREATE FUNCTION FnGetQty ( @Id INT ) RETURNS TABLE AS RETURN ( SELECT ID,PERIOD,QTY FROM DETAILS WHERE ID=@Id )

который породил следующий результат:

&nbsp Общая особенность CROSS APPLY и OUTER APPLY

CROSS APPLY или OUTER APPLY можно использовать для сохранения значений NULL, когда они не могут быть взаимозаменяемыми.

У вас есть таблица ниже

x------x-------------x--------------x | Id | FROMDATE | TODATE | x------x-------------x--------------x | 1 | 2014-01-11 | 2014-01-13 | | 1 | 2014-02-23 | 2014-02-27 | | 2 | 2014-05-06 | 2014-05-30 | | 3 | NULL | NULL | x------x-------------x--------------x

Когда вы используете UNPIVOT, чтобы принести FROMDATE AND TODATE в один столбец, по умолчанию будут удалены значения NULL.

SELECT ID,DATES FROM MYTABLE UNPIVOT (DATES FOR COLS IN (FROMDATE,TODATE)) P

который генерирует приведенный ниже результат. Обратите внимание, что мы пропустили запись Id number 3

x------x-------------x | Id | DATES | x------x-------------x | 1 | 2014-01-11 | | 1 | 2014-01-13 | | 1 | 2014-02-23 | | 1 | 2014-02-27 | | 2 | 2014-05-06 | | 2 | 2014-05-30 | x------x-------------x

В таких случаях полезно использовать CROSS APPLY или OUTER APPLY

SELECT DISTINCT ID,DATES FROM MYTABLE OUTER APPLY(VALUES (FROMDATE),(TODATE)) COLUMNNAMES(DATES)

который формирует следующий результат и сохраняет Id, где его значение 3

x------x-------------x | Id | DATES | x------x-------------x | 1 | 2014-01-11 | | 1 | 2014-01-13 | | 1 | 2014-02-23 | | 1 | 2014-02-27 | | 2 | 2014-05-06 | | 2 | 2014-05-30 | | 3 | NULL | x------x-------------x

qaru.site