Сегодня я хочу рассказать более подробно об операторе APPLY, а конкретнее о его типе CROSS APPLY. Этот оператор появился впервые в SQL Server 2005, но к сожалению многие так и не научились им пользоваться, возможно это из-за того, что в BOL (SQL Server Books Online) этот оператор плохо описан и имеет очень "сухие" примеры его использования. В этой статье я покажу несколько интересных демонстраций, где этот оператор может пригодиться.
Основная фича оператора заключается в том, что APPLY позволяет вызывать табличную функцию для каждой строки, возвращаемой внешним табличным выражением запроса. Именно этот пример есть в BOL.
Оператор CROSS APPLY возвращает только строки из внешней таблицы, которые создает результирующий набор из возвращающего табличное значение функции. Оператор OUTER APPLY возвращает и строки, которые формируют результирующий набор, и строки, которые этого не делают, со значениями NULL в столбцах, созданных возвращающей табличное значение функцией.
Для демонстрации некоторых фич оператора APPLY, создадим тестовую БД и пару таблиц:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | use master
go
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
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:
1 2 3 4 5 6 7 8 9 10 | create function dbo.GetCities( @CountyID int )
returns table
as
return
(
select CityID, City from dbo.Cities
where CountryID = @CountyID
)
go
|
Результат вызова функции представлен ниже:
1 2 3 4 5 6 7 8 9 10 11 | select * from dbo.GetCities (1)
|
А теперь с помощью оператора APPLY я выведу список городов для каждой страны из таблицы
Countries
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | select * from dbo.Countries c
cross apply dbo.GetCities ( c.CountryID ) ap
|
Очень удобно, но подобный пример описан в БОЛ, а я покажу, как можно ещё использовать оператор CROSS APPLY.
Часто бывает задача вывести несколько первых значений из группы. Например, как вывести по 3 города для каждой страны, отсортированных по алфавиту!? С помощью оператора APPLY это сделать достаточно легко:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | select * from dbo.Countries c
cross apply ( select top 3 City from dbo.Cities
where CountryID = c.CountryID order by City
) ap
|
Теперь попробуем ещё более усложнить наш запрос. Выведем первую букву каждого из 3х городов каждой страны и общее количество этих букв среди ВСЕХ городов текущей страны:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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
|
Кроме того оператор может пригодиться для распарсивания значений из строки с разделителем. Предположим, что у нас есть таблица-помойка в которой хранится информация о каких-то людях: фамилия, Имя, Отчество, год рождения и город в котором он проживает. Все параметры разделены запятой, при этом в строке могут храниться не все эти значения.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | 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
|
Задача: вытащить каждое значение в отдельную колонку, своего рода попытка нормализации. Вариантов для распарсивания этих строк много, но мы попробуем это сделать с помощью оператора CROSS APPLY. Для начала мы дополним каждую строку несколькими запятыми в конце строки, а именно 5 (по максимальному кол-ву параметров в строке):
1 2 3 4 5 6 7 8 9 10 11 12 13 | select string from dbo.TestTable
cross apply ( select string = val + ',,,,,' ) f1
|
А теперь объясню, как это нам поможет. Дополнив строку запятыми мы можем однозначно вытаскивать значения, делать мы это будем с помощью (опять же) CROSS APPLY и строковой функции
CHARINDEX. Для окончательного разрезания строки, необходимо получить позицию (порядковый номер в строке) каждой запятой:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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
|
Теперь у нас есть все, для того, чтобы разделить нашу таблицу с одной колонкой на таблицу, где каждое значение хранится в отдельной колонке. И опять же с помощью CROSS APPLY:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 попробуем разделить строки на отдельные слова и посчитаем их общее количество, вот тестовые данные:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 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
|
Ну и сам запрос, подсчитывающий сколько каждый из городов встречается в этой таблице:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | select Word, count(*) cl
from @t join master..spt_values on substring( ' '+Message, Number, 1 ) = ' '
and Number < len( Message ) + 1
cross apply ( select string = substring( ' ' + Message + ' ', Number + 1, len( Message ) + 1 ) ) f1
cross apply ( select p1 = charindex( ' ',string ) ) f2
cross apply ( select Word = substring ( Message, Number, p1 ) ) f4
where Type = 'P' and Number > 0
group by Word
|
Вы можете сказать, что почти все примеры, которые я рассмотрел в этой статье, можно реализовать и без оператора CROSS APPLY. Я, конечно же с вами соглашусь, но цель была показать на сколько оператор удобен в использовании и думаю этот топик найдет своих читателей.
Подписаться