Чудесный оператор CROSS APPLY

Tsql теория > Чудесный оператор CROSS APPLY
31.05.2018 15:44:28



Статья:

Основная фича оператора заключается в том, что 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           Берлин
Очень удобно, но подобный пример описан в БОЛ, а я покажу, как можно ещё использовать оператор 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х городов каждой страны и общее количество этих букв среди ВСЕХ городов текущей страны:

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