Отправляет email-рассылки с помощью сервиса Sendsay

Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru)

Подписаться Бесплатная «Серебряная» новостная рассылка . Подписчиков 2.594 RSS
←   Апрель 2007   →
 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


За последние 60 дней ни разу не выходила


Сайт рассылки: http://www.sql-ex.ru
Открыта: 15-09-2004

Автор
Serge I

Статистика

2.594 подписчиков
0 за неделю
←   Все выпуски   →

Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 136


Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 136 (21 апреля 2007 г.)

SQL Exercises

Новым посетителям сайта

Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.

Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 57-ти задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений.

Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе.

Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий.

Новости сайта

§ На сайте появилась возможность оплатить сертификаты кредитными картами через систему PayPal. Эта возможность ориентирована на иностранцев, поэтому доступна в англоязычном интерфейсе. Если кого заинтересует, просто переключите язык при осуществлении оплаты.

§ PavelPS, решив две задачи третьего этапа, переместился на 11 позицию.

§ Лидеры решали пропущенные задачи:
Snowbear (138, .000)

§ Продвинулись в рейтинге:
Fomichev (130, 14.048)
Shurgenz (130, 12.325)
Kamin (125, 51.090)
Donald (123, 5.442)

§ Число подписчиков - 3542

Число участников рейтинга - 9915

Число участников второго этапа - 939

Сертифицировано на сайте - 151

Лучшие результаты (ТОР 20)

No Person Number of
Sel_ex		 Last_Sel Number of
DML_ex		 Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 Северюхин Ю.А. (Venser) 142 142 21 341 36 4.912 .655 14 08 Apr 2007 20 Apr 2007
2 Солдатенков Ю.С. (SolYUtor) 142 142 21 341 320 17.807 2.695 14 03 Apr 2007 20 Apr 2007
3 Мурашкин И.В. (lepton) 142 142 21 341 371 15.737 5.539 14 29 Mar 2007 16 Apr 2007
4 Карасёва Н.В. (vlksm) 142 142 21 341 328 31.344 5.912 14 30 Mar 2007 20 Apr 2007
5 Голубин Р.С. (Roman S. Golubin) 142 142 21 341 588 55.391 34.203 14 29 Mar 2007 20 Apr 2007
6 Агапов В. (KERBEROS) 138 141 20 330 89 6.163 1.262 11 20 Nov 2006 09 Apr 2007
7 Кувалкин К.С. (Cyrilus) 140 141 20 334 867 12.518 2.519 11 10 Apr 2007 20 Apr 2007
8 Зверев Д.Л. (dimzv) 138 141 20 330 1141 9.294 4.938 11 19 Dec 2006 22 Dec 2006
9 Войнов П.Е. (pаparome) 140 142 21 335 579 2.741 .049 10 26 Mar 2007 20 Apr 2007
10 Тарасов Д.Б. (Gavrila) 138 140 21 330 577 20.220 .513 7 26 Mar 2007 20 Apr 2007
11 Шептунов П.П. (PavelPS) 140 140 21 334 111 7.794 3.147 7 17 Apr 2007 20 Apr 2007
12 Мальцев А.В. (Палкин) 140 141 21 334 224 27.657 7.373 7 29 Mar 2007 17 Apr 2007
13 Васьков Е.В. (Johan) 140 140 21 334 253 12.786 11.402 7 29 Mar 2007 09 Apr 2007
14 Валуев Д.И. (Fiolent) 137 140 20 325 1313 116.922 62.302 4 09 Apr 2007 20 Apr 2007
15 Юлдашев М.Р. (Snowbear) 138 68 21 328 638 4.119 .000 3 17 Apr 2007 20 Apr 2007
16 Креславский О.М. (Arcan) 139 139 21 330 67 9.932 .315 3 07 Apr 2007 20 Apr 2007
17 Держальцев В.А. (MadVet) 135 139 20 321 540 34.190 3.085 3 08 Oct 2006 19 Oct 2006
18 Палий С.А. (PS_Sergey) 136 139 20 322 212 15.704 4.188 3 01 Dec 2006 03 Dec 2006
19 Бородкина М.И. (marishkin) 138 138 21 327 145 19.015 .000 0 10 Apr 2007 19 Apr 2007
20 Салимов Д.М. (Damirishe) 136 138 21 323 186 11.246 .000 0 26 Mar 2007 30 Mar 2007

Лучшие результаты за неделю

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 Смирнов С.В. (WildSery) 58 58 110 0 110 947 20 Apr 2007
2 >Потапов А.В. (Potapov) 50 50 94 0 94 1358 20 Apr 2007
3 >Кравченко Н.Н. (Limp) 42 42 75 17 92 1395 20 Apr 2007
4 Бологов В.К. (Crusader) 41 47 81 0 81 1501 19 Apr 2007
5 >чемичев (axula) 40 40 75 0 75 1802 20 Apr 2007
6 >Кононов (_Gudinya_) 38 38 71 0 71 1940 20 Apr 2007
7 Петров Е.С. (SystemOAD) 35 35 60 3 63 2264 20 Apr 2007
8 >Васильев А.А. (Andre_V) 34 34 62 0 62 2312 20 Apr 2007
9 >Савинов П.А. (Savinov) 28 28 48 11 59 2458 20 Apr 2007
10 >Шедин А.А. (Sanik) 32 32 59 0 59 2461 20 Apr 2007
11 >Гуменюк Р.В. (Romen) 30 30 54 3 57 2593 20 Apr 2007
12 Косарев А.Л. (Aleon) 17 53 35 17 52 846 19 Apr 2007
13 >Васильева Н.A. (NRed) 29 29 52 0 52 2921 20 Apr 2007
14 >Костаненков А.И. (AIK_IGZ) 14 57 32 17 49 515 20 Apr 2007
15 >Sh (SerSh) 28 28 49 0 49 3139 20 Apr 2007
16 >aaa (Izv) 28 28 49 0 49 3156 20 Apr 2007
17 MoonRabbit (MoonRabbit) 9 64 14 34 48 437 18 Apr 2007
18 >Pushkarev D.A. (Dmitry hitry) 29 29 48 0 48 3263 20 Apr 2007
19 >Анисимов (Robbin) 27 27 47 0 47 3294 20 Apr 2007
20 Semakin S.I. (Support) 27 27 39 5 44 3452 20 Apr 2007
21 >Колодий (kgv63) 26 26 43 0 43 3512 20 Apr 2007
22 Manashirov G. (Genarek) 21 28 42 0 42 3209 19 Apr 2007
23 Кокая Г. (Гига) 24 24 39 1 40 3700 20 Apr 2007
24 >Легкунец С.К. (Svetlanco) 25 25 40 0 40 3709 20 Apr 2007

Изучаем SQL

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

Jeff Smith (оригинал: SQL Server 2005: Using Rank and Partition 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

Полезная информация

§ Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL.

§ В продаже еще имеется книга SQL. Задачи и решения, посвященная анализу ошибок, допускаемых при решении задач первого этапа. На сайте издательства Питер можно сделать заказ и познакомиться с содержанием.

§ Желающих поспособствовать популяризации сайта прошу проголосовать/поставить закладку в социальных сетях:
del.icio.us
dzone.com
Digg.com

Контакты

По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77@yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.
Подписка Subscribe.Ru
Новости сайта "Упражнения по SQL"
В избранное