Оператор CROSS APPLY в T-SQL. Cross apply t sql

SQL.RU | Чудесный оператор CROSS APPLY

Сегодня я хочу рассказать более подробно об операторе APPLY, а конкретнее о его типе CROSS APPLY. Этот оператор появился впервые в SQL Server 2005, но к сожалению многие так и не научились им пользоваться, возможно это из-за того, что в BOL (SQL Server Books Online) этот оператор плохо описан и имеет очень "сухие" примеры его использования. В этой статье я покажу несколько интересных демонстраций, где этот оператор может пригодиться.

Основная фича оператора заключается в том, что APPLY позволяет вызывать табличную функцию для каждой строки, возвращаемой внешним табличным выражением запроса. Именно этот пример есть в BOL. Оператор CROSS APPLY возвращает только строки из внешней таблицы, которые создает результирующий набор из возвращающего табличное значение функции. Оператор OUTER APPLY возвращает и строки, которые формируют результирующий набор, и строки, которые этого не делают, со значениями NULL в столбцах, созданных возвращающей табличное значение функцией.

Для демонстрации некоторых фич оператора APPLY, создадим тестовую БД и пару таблиц:

use master go --Создаю тестовую БД, --для демонстрации возможностей оператора CrossApply if db_id ( 'CrossApplyDemo' ) is not null drop database CrossApplyDemo go create database CrossApplyDemo go use CrossApplyDemo go --Создаю тестовую таблицу стран if object_id ( 'dbo.Countries', 'U' ) is not null drop table dbo.Countries go create table dbo.Countries ( CountryID int, Country nvarchar(255) ) go --Добавим 5 стран, используя синтаксис SQL Server 2008 insert into dbo.Countries ( CountryID, Country ) values ( 1, N'Россия' ), ( 2, N'США' ), ( 3, N'Германия' ) , ( 4, N'Франция' ), ( 5, N'Италия' ), ( 6, N'Испания' ) go --Создаю тестовую таблицу городов if object_id ( 'dbo.Cities', 'U' ) is not null drop table dbo.Cities go create table dbo.Cities ( CityID int, CountryID int, City nvarchar(255) ) go --Добавим несколько городов insert into dbo.Cities ( CityID, CountryID, City ) values ( 1, 1, N'Москва' ), ( 2, 1, N'Санкт-Петербург' ), ( 3, 1, N'Екатеринбург' ) , ( 4, 1, N'Новосибирс' ), ( 5, 1, N'Самара' ), ( 6, 2, N'Чикаго' ) , ( 7, 2, N'Вашингтон' ), ( 8, 2, N'Атланта' ), ( 9, 3, N'Берлин' ) , ( 10, 3, N'Мюнхен' ), ( 11, 3, N'Гамбург' ), ( 12, 3, N'Бремен' ) , ( 13, 4, N'Париж' ), ( 14, 4, N'Лион' ), ( 15, 5, N'Милан' ) go Основное назначение оператора - это работа с табличными функциями. Создадим функцию, которая возвращает список городов по входному параметру @CountyID: --Табличная функция create function dbo.GetCities( @CountyID int ) returns table as return ( select CityID, City from dbo.Cities where CountryID = @CountyID ) go Результат вызова функции представлен ниже: select * from dbo.GetCities (1) ------------------------------ --Результат: ------------------------------ --CityID City ------------- --------------------- --1 Москва --2 Санкт-Петербург --3 Екатеринбург --4 Новосибирс --5 Самара А теперь с помощью оператора APPLY я выведу список городов для каждой страны из таблицы Countries select * from dbo.Countries c cross apply dbo.GetCities ( c.CountryID ) ap ------------------------------ --Результат: ------------------------------ --CountryID Country CityID City ------------- --------------- ----------- --------------- --1 Россия 1 Москва --1 Россия 2 Санкт-Петербург --1 Россия 3 Екатеринбург --1 Россия 4 Новосибирс --1 Россия 5 Самара --2 США 6 Чикаго --2 США 7 Вашингтон --2 США 8 Атланта --3 Германия 9 Берлин --3 Германия 10 Мюнхен --3 Германия 11 Гамбург --3 Германия 12 Бремен --4 Франция 13 Париж --4 Франция 14 Лион --5 Италия 15 Милан Очень удобно, но подобный пример описан в БОЛ, а я покажу, как можно ещё использовать оператор CROSS APPLY. Часто бывает задача вывести несколько первых значений из группы. Например, как вывести по 3 города для каждой страны, отсортированных по алфавиту!? С помощью оператора APPLY это сделать достаточно легко: select * from dbo.Countries c cross apply ( select top 3 City from dbo.Cities where CountryID = c.CountryID order by City ) ap ------------------------------ --Результат: ------------------------------ --CountryID Country City ------------- --------------- --------------- --1 Россия Екатеринбург --1 Россия Москва --1 Россия Новосибирс --2 США Атланта --2 США Вашингтон --2 США Чикаго --3 Германия Берлин --3 Германия Бремен --3 Германия Гамбург --4 Франция Лион --4 Франция Париж --5 Италия Милан Теперь попробуем ещё более усложнить наш запрос. Выведем первую букву каждого из 3х городов каждой страны и общее количество этих букв среди ВСЕХ городов текущей страны: select * from dbo.Countries c cross apply ( select top 3 City from dbo.Cities where CountryID = c.CountryID order by City ) ap cross apply ( select l 'Letter', sum (cl) 'LetterCount' from (select left( ap.City, 1 ) l, len( City ) - len ( replace ( City, left( ap.City, 1 ) ,'' ) ) cl from dbo.Cities where CountryID = c.CountryID ) t group by l ) apLetters ------------------------------ --Результат: ------------------------------ --CountryID Country City Letter LetterCount ------------- --------------- --------------- ------ ----------- --1 Россия Екатеринбург Е 4 --1 Россия Москва М 2 --1 Россия Новосибирс Н 3 --2 США Атланта А 5 --2 США Вашингтон В 1 --2 США Чикаго Ч 1 --3 Германия Берлин Б 3 --3 Германия Бремен Б 3 --3 Германия Гамбург Г 2 --4 Франция Лион Л 1 --4 Франция Париж П 1 --5 Италия Милан М 1

Кроме того оператор может пригодиться для распарсивания значений из строки с разделителем. Предположим, что у нас есть таблица-помойка в которой хранится информация о каких-то людях: фамилия, Имя, Отчество, год рождения и город в котором он проживает. Все параметры разделены запятой, при этом в строке могут храниться не все эти значения.

--Создаю ещё одну тестовую таблицу if object_id ( 'dbo.TestTable', 'U' ) is not null drop table dbo.TestTable go create table dbo.TestTable ( val nvarchar(1024) ) insert into dbo.TestTable select N'Иванов,Иван,Иванович,1980,Москва' union all select N'Петров,,,1988' union all select N'Сидоров,Иван,Юрьевич,,Саратов' union all select N',Степан,,,Екатеринбург' union all select N'Кузнецов,,Иванович' union all select N'Путин' select * from dbo.TestTable ------------------------------ --Результат: ------------------------------ --val ----------------------------------- --Иванов,Иван,Иванович,1980,Москва --Петров,,,1988 --Сидоров,Иван,Юрьевич,,Саратов --,Степан,,,Екатеринбург --Кузнецов,,Иванович --Путин Задача: вытащить каждое значение в отдельную колонку, своего рода попытка нормализации. Вариантов для распарсивания этих строк много, но мы попробуем это сделать с помощью оператора CROSS APPLY. Для начала мы дополним каждую строку несколькими запятыми в конце строки, а именно 5 (по максимальному кол-ву параметров в строке): select string from dbo.TestTable cross apply ( select string = val + ',,,,,' ) f1 ------------------------------ --Результат: ------------------------------ --string ----------------------------------- --Иванов,Иван,Иванович,1980,Москва,,,,, --Петров,,,1988,,,,, --Сидоров,Иван,Юрьевич,,Саратов,,,,, --,Степан,,,Екатеринбург,,,,, --Кузнецов,,Иванович,,,,, --Путин,,,,, А теперь объясню, как это нам поможет. Дополнив строку запятыми мы можем однозначно вытаскивать значения, делать мы это будем с помощью (опять же) CROSS APPLY и строковой функции CHARINDEX. Для окончательного разрезания строки, необходимо получить позицию (порядковый номер в строке) каждой запятой: select p1, p2, p3, p4, p5 from dbo.TestTable cross apply ( select string = val + ',,,,,' ) f1 cross apply ( select p1 = charindex( ',', string ) ) ap1 cross apply ( select p2 = charindex( ',', string, p1 + 1 ) ) ap2 cross apply ( select p3 = charindex( ',', string, p2 + 1 ) ) ap3 cross apply ( select p4 = charindex( ',', string, p3 + 1 ) ) ap4 cross apply ( select p5 = charindex( ',', string, p4 + 1 ) ) ap5 ------------------------------ --Результат: ------------------------------ --string ----------------------------------- --p1 p2 p3 p4 p5 ------------- ----------- ----------- ----------- ----------- --7 12 21 26 33 --7 8 9 14 15 --8 13 21 22 30 --1 8 9 10 23 --9 10 19 20 21 --6 7 8 9 10 Теперь у нас есть все, для того, чтобы разделить нашу таблицу с одной колонкой на таблицу, где каждое значение хранится в отдельной колонке. И опять же с помощью CROSS APPLY: select NewTable.* from dbo.TestTable cross apply ( select string = val + ',,,,,' ) f1 cross apply ( select p1 = charindex( ',', string ) ) ap1 cross apply ( select p2 = charindex( ',', string, p1 + 1 ) ) ap2 cross apply ( select p3 = charindex( ',', string, p2 + 1 ) ) ap3 cross apply ( select p4 = charindex( ',', string, p3 + 1 ) ) ap4 cross apply ( select p5 = charindex( ',', string, p4 + 1 ) ) ap5 cross apply ( select LastName = substring( string, 1, p1-1 ) , MiddleName = substring( string, p1+1, p2-p1-1 ) , FirstName = substring( string, p2+1, p3-p2-1 ) , Year = substring( string, p3+1, p4-p3-1 ) , City = substring( string, p4+1, p5-p4-1 ) ) NewTable

В последнем, на сегодня, примере использования оператора CROSS APPLY попробуем разделить строки на отдельные слова и посчитаем их общее количество, вот тестовые данные:

declare @t table ( Message varchar(255)) insert into @t select 'Киев' union all select 'Киев Моссква' union all select 'Киев Моссква Екатеринбург' union all select 'Лондон Екатеринбург Донецк' union all select 'Моссква Самара Саратов Самара' union all select 'Киев Моссква Киев Воронеж' select * from @t ------------------------------ --Результат: ------------------------------ --Message -------------------------------- --Киев --Киев Моссква --Киев Моссква Екатеринбург --Лондон Екатеринбург Донецк --Моссква Самара Саратов Самара --Киев Моссква Киев Воронеж Ну и сам запрос, подсчитывающий сколько каждый из городов встречается в этой таблице: select Word, count(*) cl from @t join master..spt_values on substring( ' '+Message, Number, 1 ) = ' ' and Number 0 group by Word ------------------------------ --Результат: ------------------------------ --Word cl ---------------------- ----------- --Воронеж 1 --Донецк 1 --Екатеринбург 2 --Киев 5 --Лондон 1 --Моссква 4 --Самара 2 --Саратов 1

Вы можете сказать, что почти все примеры, которые я рассмотрел в этой статье, можно реализовать и без оператора CROSS APPLY. Я, конечно же с вами соглашусь, но цель была показать на сколько оператор удобен в использовании и думаю этот топик найдет своих читателей.

www.t-sql.ru

Оператор CROSS APPLY в T-SQL | Info-Comp.ru

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

Мы с Вами оператор CROSS APPLY как в справочнике T-SQL, так и в материале про основы программирования на T-SQL не рассматривали. Честно говоря, и в официальной документации данный оператор описан кратко, а он действительно полезный, поэтому сегодня я предлагаю рассмотреть возможности CROSS APPLY, в том числе и те, которые не описаны в документации.

CROSS APPLY

CROSS APPLY – это тип оператора APPLY, который позволяет вызывать табличную функцию для каждой строки внешнего табличного выражения. Вместо табличной функции можно также использовать и подзапрос. Есть еще и другой тип OUTER APPLY, он в отличие от CROSS APPLY возвращает и строки, которые формируют результирующий набор и те, которые этого не делают, т.е. со значениями NULL в столбцах.

Примечание! Примеры ниже будут рассмотрены в MS SQL Server 2008 R2.

Примеры использования CROSS APPLY

Для начала давайте определимся с исходными данными, допустим, у нас есть таблица товаров (Products) и таблица продаж (Sales) вот с такими данными:

--ProductName – Наименование товара SELECT * FROM Products --ProductID – Идентификатор товара --DT – Дата продажи SELECT * FROM Sales

А также у нас есть функция (FT_GET_Sales), которая просто выводит список продаж по идентификатору товара.

CREATE FUNCTION dbo.FT_GET_Sales(@ProductID INT) RETURNS TABLE AS RETURN ( SELECT S.ID, S.DT, P.ProductName FROM Sales S LEFT JOIN Products P ON S.ProductID = P.ID WHERE S.ProductID = @ProductID )

Пример ее работы:

SELECT Sales.DT, Sales.ProductName FROM dbo.FT_GET_Sales(3) AS Sales

Пример использования CROSS APPLY с табличной функцией

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

SELECT P.ProductName, Sales.DT FROM Products P CROSS APPLY dbo.FT_GET_Sales(P.ID) AS Sales

Где как Вы понимаете, табличная функция FT_GET_Sales была вызвана для каждой строки таблицы Products.

Пример использования CROSS APPLY с подзапросом

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

SELECT Products.ProductName, Sales.DT FROM Products CROSS APPLY ( SELECT TOP 1 P.DT, P.ProductName FROM dbo.FT_GET_Sales(Products.ID) AS P ORDER BY P.DT DESC )AS Sales

Как видим, после CROSS APPLY у нас идет подзапрос.

Пример использования OUTER APPLY

Если Вы заметили, у нас в исходных данных есть позиция «Клавиатура», по которой не было продаж. И в случае возникновения необходимости получить последнюю дату продажи включая товары, по которым не было продаж, т.е. выводить NULL значения, чтобы мы видели, какие товары не продавались вообще, тип CROSS APPLY можно заменить на OUTER APPLY.

Пример:

SELECT Products.ProductName, Sales.DT FROM Products OUTER APPLY ( SELECT TOP 1 P.DT, P.ProductName FROM dbo.FT_GET_Sales(Products.ID) AS P ORDER BY P.DT DESC )AS Sales

Теперь видно, что некоторые товары продавались и последняя дата их продажи такая то, а некоторые товары не продавались, т.е. у них отсутствует дата продажи.

Заметка! Начинающим программистам рекомендую почитать мою книгу «Путь программиста T-SQL», в ней я подробно, с большим количеством примеров, рассказываю про другие полезные возможности языка Transact-SQL.

На этом все, удачи!

performance - T-SQL CROSS APPLY с GROUP BY

Интересное поведение. Строго говоря, ваш запрос неверен - если у вас нет заказов для Мэри, но вы хотите обновить ее запись, вы должны использовать outer apply вместо cross. Кроме того, было бы лучше обработать этот сценарий "без записей" с обертками isnull() в части set.

Прямо сейчас значения в строке Мэри не переписаны нулями - они остались без изменений, потому что apply ничего не возвращает для нее. Вы можете увидеть это, изменив инициализацию таблицы следующим образом:

insert into @cust values (01, 'Fred', -1, -1, -1) insert into @cust values (02, 'Mary', -1, -1, -1) insert into @cust values (03, 'Karl', -1, -1, -1)

С group by на месте, строка Мэри не получает нулей, она все еще имеет все эти -1. Это точно такое же поведение, когда вы пытаетесь присвоить значение скалярной переменной с запросом, который не возвращает строк, после этого переменная по-прежнему будет сохранять свое предыдущее значение. Это документированная и хорошо известная функция.

Сказав это, мне все же очень интересно (по крайней мере, мне), почему комментирование group by так сильно меняет поведение. Мы можем сузить его, посмотрев результаты подзапроса apply, например:

select MAX(AMOUNT) MaxOrder, SUM(AMOUNT) TotalAmountSpent, COUNT(OID) OrderCount from @order o where o.CUSTID = 2; select MAX(AMOUNT) MaxOrder, SUM(AMOUNT) TotalAmountSpent, COUNT(OID) OrderCount from @order o where o.CUSTID = 2 group by o.CUSTID;

Как представляется, задание критериев группировки работает как дополнительный фильтр. Вероятно, это то, как агрегация реализована в SQL Server.

EDIT: после некоторого поиска я обнаружил, что Oracle работает точно так же. Кажется, это стандартное поведение. Кроме того, здесь обсуждается этот эффект: Count Возврат пустой, а не 0

Короче говоря, group by отфильтровывает группы, которых нет, поэтому, когда вы указываете клиента без продаж, вы ничего не получаете. Однако без группировки такой стадии фильтрации нет, поэтому вы получаете агрегаты для всей таблицы - null для max и sum и ноль для count. И в вашем конкретном примере group by фактически не нужен, потому что все возвращенные столбцы являются агрегатами (что довольно редко).

qaru.site