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

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

Подписаться Бесплатная «Серебряная» новостная рассылка . Подписчиков 2.594 RSS
←   Октябрь 2009   →
 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
28.10.2009
22:20:48
22:21:04
29
30
31


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


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

Автор
Serge I

Статистика

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

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


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

Выпуск 262 от 03 октября 2009 г.

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

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

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

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

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


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

§ Изменения среди лидеров (решенные за неделю задачи третьего этапа):
2. Ozzy (159)
7. Faust_zp (157)
26. Wiedzmen (145)

§ К третьему этапу подошел:
58. Eagleoff (задач 138, время 26.387)

§ Продвинулись в рейтинге:
58. Eagleoff (139, 26.392)
59. VOVA_KHILKO (132, 59.391)
60. Fomichev (137, 25.713)
81. alx_ol (127, 169.989)

§ Новые лица в ТОР 100 и вернувшиеся туда:
89. SergLK (125, 14.393)
98. _irina_ (124, 31.810)

§ Продвижение ближайших претендентов на попадание в ТОР 100:
105. HandKot_ (120, 284.381)
152. kostik (115, 89.369)
162. Creol (114, 66.852)
172. lilyok (113, 73.304)

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

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

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

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

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

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

No Person Number of
Sel_ex		 Last_Sel Number of
DML_ex		 Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 Креславский О.М. (Arcan)1 161 161 22 391 955 105.388 86.774 68 11 Sep 2009 02 Oct 2009
2 Зотов П.Г. (Ozzy)1 161 159 22 391 567 196.171 209.314 68 28 Sep 2009 02 Oct 2009
3 Сальников С.А. ($erges)1 160 161 22 388 560 5.641 5.864 65 08 Sep 2009 02 Oct 2009
4 Умрихина Е.В. (Umrikhina)1 160 161 22 388 489 41.362 58.776 65 26 Aug 2009 29 Sep 2009
5 Дроздков А.Н. (anddros)5 159 161 22 385 464 7.479 8.046 62 03 Sep 2009 02 Oct 2009
6 Карасёва Н.В. (vlksm)1 159 161 22 385 1214 98.555 69.157 62 01 Sep 2009 02 Oct 2009
7 Яцук А.А. (Faust_zp)7 159 157 22 385 968 103.473 658.510 62 29 Sep 2009 02 Oct 2009
8 Сенкевич С.В. (GreyC)1 141 152 21 351 358 57.860 27.102 44 26 Jan 2009 29 Jul 2009
9 Селезнёв А.С. (Артём С.)1 139 152 21 345 444 47.248 37.524 44 25 Jan 2009 03 Mar 2009
10 Никотин В.М. (@Nikotin)1 142 150 21 348 283 8.371 3.751 38 06 Jun 2009 30 Sep 2009
11 Печатнов В.В. (pvv)1 151 161 21 359 679 37.011 19.973 37 28 Aug 2009 02 Oct 2009
12 Мурашкин И.В. (lepton)1 135 156 21 331 1139 69.490 59.005 36 05 May 2009 24 Sep 2009
13 Муллаханов Р.Х. (rem)9 145 152 22 350 649 14.627 20.056 33 20 Jun 2009 03 Aug 2009
14 Тарасов Д.Б. (Gavrila)14 149 144 22 355 1490 47.703 17.099 32 24 Sep 2009 02 Oct 2009
15 Шиндин А.В. (AlShin)10 136 150 21 335 79 20.458 7.203 28 05 Jan 2009 25 May 2009
16 Анисимов Д. (danilko)13 148 160 22 348 32 12.722 9.542 28 12 Aug 2009 17 Aug 2009
17 Держальцев В.А. (MadVet)7 128 146 21 315 1509 60.783 28.482 26 03 Jun 2009 16 Jun 2009
18 Любченко В.А. (IAS56)6 138 146 21 332 1048 420.338 373.617 26 18 Jul 2009 28 Jul 2009
19 Голубин Р.С. (Roman S. Golubin)3 132 145 21 321 1413 93.162 58.822 23 01 Jul 2009 13 Sep 2009
20 Nikolaenko A.V. (Shadow77)12 146 161 22 345 763 89.561 14.019 22 14 Sep 2009 29 Sep 2009

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 >Корниенко С.А. (korsal) 49 49 90 35 125 963 02 Oct 2009
2 Zhidkov Y.I. (Jurii) 46 67 78 35 113 542 01 Oct 2009
3 Халиман В.С. (Dark Voltage) 41 41 80 15 95 1312 01 Oct 2009
4 >Litovchenko R. (roman_ol) 44 44 83 0 83 1926 02 Oct 2009
5 >Makarov (dmak) 21 44 40 35 75 1055 02 Oct 2009
6 Иванов (serenity) 33 33 69 0 69 2507 01 Oct 2009
7 >Бичев А.В. (Arcady) 24 48 45 0 45 979 02 Oct 2009
8 Заворотнев В.В. (vvz) 17 109 41 0 41 196 02 Oct 2009
9 Байбузский А. (the sky is blue) 18 18 40 1 41 5190 29 Sep 2009
10 Petrosyan D.V. (Davo) 13 17 29 8 37 4014 02 Oct 2009
11 >Гугняева (Rufina) 16 16 35 0 35 6091 02 Oct 2009
12 >mozilya (moz_ilya) 17 17 31 0 31 6658 02 Oct 2009
13 >Яковенко Р. (RomanY) 15 26 30 0 30 2072 02 Oct 2009
14 >Киракосянц Виталий (Robb) 13 104 29 0 29 230 02 Oct 2009
15 Левитин И.А. (Spider244) 14 86 29 0 29 316 02 Oct 2009
16 >Иващенов И.В. (iv_2609) 14 45 29 0 29 1834 02 Oct 2009
17 bitsadze K.R. (furie) 10 40 15 12 27 1181 01 Oct 2009
18 >Сергеев (ilin200) 10 11 27 0 27 6910 02 Oct 2009
19 Воевуцкий С.А. (SergLK) 10 125 25 0 25 89 02 Oct 2009
20 Романов К.Ю. (kostik) 9 115 24 0 24 152 02 Oct 2009
21 >Казазаев А.М. (J2k) 12 66 23 0 23 559 02 Oct 2009
22 Рожнов В.А. (gnus) 0 0 23 23 8094 01 Oct 2009
23 Levadnaya T. (L.Tamara) 10 30 19 3 22 2858 01 Oct 2009
24 >Поляков С.Г. (Ivanoff Alex) 8 108 21 0 21 203 02 Oct 2009
25 Крутоголов С.В. (bgs) 10 36 21 0 21 2333 30 Sep 2009
26 Филилеева (MarijkaFMM) 7 32 15 5 20 1605 02 Oct 2009
27 Овсиенко А. (anabella) 0 32 0 20 20 1836 02 Oct 2009
28 Bzik (Bzik) 4 22 6 14 20 3534 01 Oct 2009

Изучаем SQL

Функции RANK() и DENSE_RANK()

Моисеенко С.И.

Эти функции, как и функция ROW_NUMBER(), тоже нумеруют строки, но делают это несколько отличным способом. Это отличие проявляется в том, что строки, которые имеют одинаковые значения в столбцах, по которым выполняется упорядочивание, получают одинаковые номера (ранги). Например, значения (отсортированные по возрастанию)

1
5
6
6
6
получат такие номера: 
1  1
5  2
6  3
6  3
6  3

Возникает вопрос, с какого номера продолжится нумерация, если, скажем, в последовательности чисел появится 7 и т.д.? Здесь есть два варианта:

1) с номера 4, т.к. это следующий номер по порядку;
2) с номера 6, т.к. следующая строка будет шестая по счету.

Такая "неоднозначность" и привела к появлению двух функций вместо одной - RANK и DENSE_RANK, первая из которых продолжит нумерацию с 6, а вторая (плотная) - с 4.

Рассмотрим несколько примеров. Начнем с демонстрации отличия в поведении функций RANK и ROW_NUMBER:

SELECT *, ROW_NUMBER() OVER(ORDER BY type) num,
RANK() OVER(ORDER BY type) rnk
FROM Printer

Здесь в двух последних столбцах выводятся значения сравниваемых функций при упорядочивании строк по столбцу type:

code    model   color   type    price   num     rnk
2  1433  y  Jet  270,00  1  1
3  1434  y  Jet  290,00  2  1
1  1276  n  Laser  400,00  3  3
6  1288  n  Laser  400,00  4  3
4  1401  n  Matrix  150,00  5  5
5  1408  n  Matrix  270,00  6  5

Как и следовало ожидать, ROW_NUMBER пронумеровывает весь набор строк, возвращаемых запросом. Функция RANK, как оказалось, работает по второму из рассмотренных выше варианту, т.е. следующим номером после строк с одинаковым рангом будет номер строки.

А теперь сравним "плотный" и "неплотный" ранги: 

SELECT *, RANK() OVER(ORDER BY type) rnk,
DENSE_RANK() OVER(ORDER BY type) rnk_dense
FROM Printer
 
code    model   color   type    price   rnk     rnk_dense
2  1433  y  Jet  270,00  1  1
3  1434  y  Jet  290,00  1  1
1  1276  n  Laser  400,00  3  2
6  1288  n  Laser  400,00  3  2
4  1401  n  Matrix  150,00  5  3
5  1408  n  Matrix  270,00  5  3

Следует также обратить вниманию на порядок, в котором выводятся строки результирующего набора. Поскольку оператор SELECT в нашем примере не имеет предложения ORDER BY, а для вычисления рангов используется одинаковое упорядочивание по столбцу type, то и результат выводится в том же порядке. В целях оптимизации, если вам не нужно какое-либо другое упорядочение результирующего набора, используйте этот факт, чтобы не выполнять лишние сортировки, которые ухудшают производительность запроса.

Как и для функции ROW_NUMBER, в предложении OVER может использоваться конструкция PARTITION BY, разбивающая весь набор строк, возвращаемых запросом, на группы, к которым затем применяется соответствующая функция.

Запрос

SELECT *, RANK() OVER(PARTITION BY type ORDER BY price) rnk
FROM Printer

позволяет в каждой группе, определяемой типом принтера, ранжировать модели по цене в порядке ее возрастания:

code     model   color   type    price   rnk
2  1433  y  Jet  270,00  1
3  1434  y  Jet  290,00  2
1  1276  n  Laser  400,00  1
6  1288  n  Laser  400,00  1
4  1401  n  Matrix  150,00  1
5  1408  n  Matrix  270,00  2

А вот как можно выбрать самые дешевые модели в каждой категории:

SELECT model, color, type, price
FROM (
SELECT *, RANK() OVER(PARTITION BY type ORDER BY price) rnk
 FROM Printer
) Ranked_models
WHERE rnk = 1
 
model color type price  
1433  y  Jet  270,00
1276  n  Laser  400,00
1288  n  Laser  400,00
1401  n  Matrix  150,00

Запрос можно было бы написать короче, если бы функцию RANK можно было бы применять в предложении WHERE, т.к. само значение ранга нам не требуется. Однако это запрещено (как и для других ранжирующих функций), по крайней мере, в SQL Server.

Наконец, рассмотрим еще один пример.

Пример. Найти производителей, которые производят более 2-х моделей PC.

Эта задача имеет традиционное решение через агрегатные функции:

SELECT maker FROM Product
WHERE type = 'PC'
GROUP BY maker
HAVING COUNT(*) > 2

Однако эту задачу можно решить и с помощью функции RANK. Идея состоит в следующем: ранжировать модели каждого производителя по уникальному ключу и выбрать только тех производителей, модели которых достигают ранга 3:

SELECT maker
FROM (
SELECT maker, RANK() OVER(PARTITION BY maker ORDER BY model) rnk
FROM Product
WHERE type = 'PC'
) Ranked_makers
WHERE rnk = 3

И в одном, и в другом случае, естественно, мы получим один и тот же результат:

maker
E

Еще раз повторю: упорядочивание в последнем случае должно быть выполнено по уникальной комбинации столбцов, т.к., в противном случае, моделей может быть больше трех, а ранг меньше (например, 1, 2, 2,...). В нашем случае данное условие выполнено, т.к. упорядочивание выполняется по столбцу model, который является первичным ключом в таблице Product.

Кстати, планы выполнения этих запросов демонстрируют одинаковые стоимости наиболее расходных операций - сканирования таблицы и сортировку (которая я первом случае присутствует неявно и вызвана операцией группировки).

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

§ Приглашаем вас посетить Интерактивный учебник по SQL.
   Ресурс позиционируется как "справочное обеспечение" для сайта SQL-EX.RU, но может использоваться и независимо от него.

§ Онлайновый выпуск рассылки можно почитать на сайте.

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

§ Хотите поддержать проект? Вот инструкция по применению.

Контакты

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