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

by Alexey Knyazev 22. декабря 2010 01:03

Сегодня я хочу рассказать более подробно об операторе 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 < 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

------------------------------
--Результат:
------------------------------
--Word                 cl
---------------------- -----------
--Воронеж              1
--Донецк               1
--Екатеринбург         2
--Киев                 5
--Лондон               1
--Моссква              4
--Самара               2
--Саратов              1


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

Tags: , , ,

SQL Server

Комментарии (3) -

Alexey9
Alexey9 Russia
10.01.2011 2:57:31 #

Есть одна классная засада с этим оператором. Проверено на собственной "шкуре". Если на сервере идет нагрузка, скажем так 80-150 ожидающих задач, с загрузкой CPU примерно 60% (отчет к примеру считаем)и 100-200 батчей  в секунду выполнение этого оператора не закончится никогда при выводе примерно 100 строк, либо получаем арифметик буффер эррор, либо задача тупо валится с ошибкой(ошибку не помню). При этом в вывод не получаем ничего, либо 5-15 строк и ошибку. Вот такой классный оператор. Нет, уж лучше вложенные селекты.  

Reply

Alexey Knyazev
Alexey Knyazev Russia
14.01.2011 22:17:30 #

1) Не всегда и не все можно заменить подзапросами
2) Описанная вами проблема мне ни разу не встречалась

Reply

Alexey9
Alexey9 Russia
15.01.2011 4:51:49 #

1. Да вы правы.
2.  Долго описывать.   Да и мой случай с вашей статьей, мало соотносится. Я просто хотел сказать что cross apply ресурсоемкая штука. И так то работает не быстро, а при нагрузке и вовсе может не отработать.  Если интересно, попробуйте при max server memory 400 gb на 2tb базе получить планы из кеша с помощью cross apply и select-ом вложенным очень удивитесь.

Reply

Добавить комментарий

  Country flag

biuquote
  • Комментарий
  • Предпросмотр
Loading