Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 09 July 2020 20:20:08


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

SQL Server 2005: Использование PARTITION и RANK в ваших критериях

Jeff Smith (оригинал: SQL Server 2005: Using PARTITION and RANK in your criteria)
Перевод Моисеенко С.И.

Возможности RANK и PARTITION в 2005 просто удивительны. Они делают решение очень многих "классических" проблем SQL весьма легким. Например, рассмотрим следующую таблицу:

create table Batting
(Player varchar(10), Year int, Team varchar(10), HomeRuns int, primary key(Player,Year))

insert into Batting
select 'A',2001,'Red Sox',13 union all
select 'A',2002,'Red Sox',23 union all
select 'A',2003,'Red Sox',19 union all
select 'A',2004,'Red Sox',14 union all
select 'A',2005,'Red Sox',11 union all
select 'B',2001,'Yankees',42 union all
select 'B',2002,'Yankees',39 union all
select 'B',2003,'Yankees',42 union all
select 'B',2004,'Yankees',29 union all
select 'C',2002,'Yankees',2 union all
select 'C',2003,'Yankees',3 union all
select 'C',2004,'Red Sox',6 union all
select 'C',2005,'Red Sox',9

Предположим, что мы хотим узнать, в каком году каждый игрок совершил максимальное число хоумранов (бейсбол), и за какую команду он играл. Если таких годов окажется несколько, вывести последний год.

В SQL 2000 нам пришлось бы это делать в два этапа: Сначала получить MAX(HomeRuns) для каждого игрока, а затем опять выполнить соединение с таблицей Batting, чтобы получить остальную часть данных:

select b.*
from
batting b
inner join
(     select player, Max(HomeRuns) as MaxHR
     from Batting
     group by player
) m
on b.Player = m.player and b.HomeRuns = m.MaxHR

Player Year Team HomeRuns
---------- ---------- ---------- ----------
A 2002 Red Sox 23
B 2001 Yankees 42
B 2003 Yankees 42
C 2005 Red Sox 9

(4 row(s) affected)

Отметим, что для игрока 'B' мы получили 2 строки, поскольку он показывал свой наилучший результат по хоумранам дважды (в 2001 и 2003 годах). Как нам теперь вернуть только наибольший год? Как можно догадаться, это потребует еще одного прохода по таблице:

select b.*
from
batting b
inner join
(     select player, Max(HomeRuns) as MaxHR
     from Batting
     group by player
) m
     on b.Player = m.player and b.HomeRuns = m.MaxHR
inner join
(     select player, homeRuns, Max(Year) as MaxYear
     from Batting
     group by Player, HomeRuns
) y
     on m.player= y.player and m.maxHR = y.HomeRuns and b.Year = y.MaxYear

Player Year Team HomeRuns
---------- ---------- ---------- ----------
A 2002 Red Sox 23
B 2003 Yankees 42
C 2005 Red Sox 9

(3 row(s) affected)

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

select b.*,
     (select count(*) from batting b2 where b.player = b2.player and b2.HomeRuns >= b.HomeRuns) as Rank
from batting b

Player Year Team HomeRuns Rank
---------- ---------- ---------- ---------- ----------
A 2001 Red Sox 13 4
A 2002 Red Sox 23 1
A 2003 Red Sox 19 2
A 2004 Red Sox 14 3
A 2005 Red Sox 11 5
B 2001 Yankees 42 2
B 2002 Yankees 39 3
B 2003 Yankees 42 2
B 2004 Yankees 29 4
C 2002 Red Sox 2 4
C 2003 Red Sox 3 3
C 2004 Red Sox 6 2
C 2005 Red Sox 9 1

Отменим однако, что мы еще не обработали одинаковые результаты (обратите внимание, что игрок 'B' не имеет ранга #1, зато имеет два ранга #2!) Чтобы сделать это, мы должны немного усложнить запрос:

select b.*,
     (select count(*) from batting b2 where b.player = b2.player and (b2.HomeRuns > b.HomeRuns or (b2.HomeRuns = b.HomeRuns and b2.Year >= b.Year))) as Rank
from batting b

Player Year Team HomeRuns Rank
---------- ---------- ---------- ---------- ----------
A 2001 Red Sox 13 4
A 2002 Red Sox 23 1
A 2003 Red Sox 19 2
A 2004 Red Sox 14 3
A 2005 Red Sox 11 5
B 2001 Yankees 42 2
B 2002 Yankees 39 3
B 2003 Yankees 42 1
B 2004 Yankees 29 4
C 2002 Red Sox 2 4
C 2003 Red Sox 3 3
C 2004 Red Sox 6 2
C 2005 Red Sox 9 1

(13 row(s) affected)

Теперь, чтобы получить нужный результат, мы можем использовать нашу формулу "ранжирования" для возвращения только рангов #1, переместив для этого подзапрос в предложение WHERE:

select b.*
from batting b
where (select count(*) from batting b2 where b.player = b2.player and
(b2.HomeRuns > b.HomeRuns or (b2.HomeRuns = b.HomeRuns and b2.Year >= b.Year))) =1

Player Year Team HomeRuns
---------- ---------- ---------- ----------
C 2005 Red Sox 9
A 2002 Red Sox 23
B 2003 Yankees 42

(3 row(s) affected)

И это все, что вам нужно сделать в SQL 2000! Легко, правда? Хммм..., видимо, не очень!

Новые функции ранжирования в SQL Server 2005 позволяют вам быстро вычислить ранг каждой строки в пределах набора, основанного на разделении (partition) и сортировке. По аналогии, можно представить себе разделение подобным группировке (GROUP BY), где использование слова "по" в вашем описании часто указывает, по каким столбцам выполняется группировка или разделение. Поскольку мы хотим получить год максимального результата для каждого игрока, мы выполняем разбиение по Player. Это означает, что строки для каждого игрока получат значение 1 - Х при их ранжировании. Само ранжирование проводится по HomeRuns, поскольку мы хотим получить ранг для каждого разбиения, упорядоченного (ORDER BY) по хоумранам от больших значений к меньшим (DESC).

Применяя новые возможности SQL 2005, мы можем использовать следующий SELECT для получения ранжирования каждого годового результата по хоумранам для каждого игрока:

select Player, Year, HomeRuns, Rank() over (Partition BY Player order by HomeRuns DESC) as Rank
from Batting

Теперь, как и ранее, мы должны разобраться с повторениями. Но сейчас это стало намного проще - мы просто добавим вторичную сортировку. Поскольку мы хотим, чтобы более поздний год ранжировался выше, просто добавим "Year DESC" в наше предложение ORDER BY:

select Player, Year, HomeRuns,Rank() over (Partition BY Player order by HomeRuns DESC, Year DESC) as Rank
from Batting

Намного проще, чем при старом способе! Однако мы еще не все сделали. Вышеприведенный SQL-запрос возвращает нам все годы наряду с ранжированием. Нам же нужно получить только строки с рангом 1. Однако если мы попробуем сделать так:

select Player, Year, HomeRuns
from Batting
where Rank() over (Partition BY Player order by HomeRuns DESC, Year DESC) = 1

то возникает ошибка:

Windowed functions can only appear in the SELECT or ORDER BY clauses
(оконные функции могут использоваться только в предложениях SELECT или ORDER BY).

Мы сможем легко это поправить, заключая в SELECT производную таблицу:

select * from
(

select Player, Year, HomeRuns, Rank() over (Partition BY Player order by HomeRuns DESC, Year DESC) as Rank
from Batting
) tmp
where Rank = 1

Теперь прошло... Это легко читать и редактировать, и необходима только одна ссылка на таблицу! Предварительное тестирование также показывает, что такой подход намного более эффективен, чем запрос в стиле SQL 2000.

Другие сложные в написании операторы SQL в эпоху до 2005 также легко разрешаются в SQL 2005. Например, классическая проблема SQL возвращения "первых Х на группу", определяемую сортировкой, или возвращения "номера строки" последовательности для всего результирующего набора или групп в пределах множества строк теперь очень, очень легко разрешается при использовании возможностей RANK(). Попробуйте!

28.03.2007

На главную страницу

Print  Версия для печати


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.