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

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

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


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

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

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

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

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

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


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

§ Возможно, тому виной сессиея, но в последнее время участились случаи нарушения основного правила сайта - решать задачи самостоятельно.
Поскольку я считаю непродуктивным тратить время на отлавливание "нарушителей", автоматизировал по мере возможности процесс наказания, заодно ужесточив его.
Наказание будет распространяться не только на тех, кто пользуется чужими решениями, но и на тех, кто их предоставляет.
Моя позиция обусловлена девальвацией рейтинга и подрывом доверия к нашим сертификатам, если они не будут подкрепленны знаниями их обладателей. Копипаст чужих решений знаний не дает, поэтому я намерен свою позицию подкреплять делом.
Как и у всякой другой системы здесь возможны сбои, поэтому обращайтесь по указанному адресу (в сообщении об ограничении доступа), если вы невинно пострадали. С полным сводом правил :-) можно познакомиться на странице регистрации.

§ yuriy.rozhok добавил данные для задачи 77 (предложение remi_).
Принял также уточнение формулировки этой задачи в редакции Fiolent.

§ Согласившись с _ORA_, заменил тестовое решение задачи 43, которое более точно отвечает формулировке этой задачи.
Можно было поправить формулировку, но принятое решение делает задачу немного сложнее. Речь идет о том, что если для некоторой страны нет кораблей в БД или все корабли этой страны не имеют информации о годе спуска на воду, то, в соответствии с формулировкой, страну все равно нужно выводить с результатом:
страна   NULL   NULL
Я не аннулирую старые решения, чтобы не влиять на рейтинг, но надеюсь, что решить задачу в такой постановке будет интересно.

§ Одна задача до третьего этапа осталась zerga (задач 137, время 2.293).

§ Новые лица в ТОР 100 и вернувшиеся туда:
97. zjor (126, 103.298)

§ Продвинулись в рейтинге:
37. yuriy.rozhok (137, 22.549)
53. DeadLock5 (134, 112.716)
65. Ocean (132, 40.203)
73. denzel (131, 112.004)

§ Продвижение ближайших претендентов на попадание в ТОР 100:
112. maar (123, 87.575)
143. Ozzy (118, 17.925)
181. HandKot_ (109, 151.340)

§ На этой неделе сертифицированы:
andrey_nick (A08030438) [BK] - г.Саратов, Россия

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

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

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

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

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

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

No Person Number of
Sel_ex		 Last_Sel Number of
DML_ex		 Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 Сальников С.А. ($erges) 147 147 21 359 81 2.751 3.359 31 17 May 2008 11 Jun 2008
2 Карасёва Н.В. (vlksm) 147 147 21 359 750 72.877 46.383 31 25 May 2008 10 Jun 2008
3 Печатнов В.В. (pvv) 145 146 21 354 190 19.151 6.326 28 26 Apr 2008 13 Jun 2008
4 Креславский О.М. (Arcan) 146 146 21 356 466 22.488 12.553 28 10 May 2008 13 Jun 2008
5 Держальцев В.А. (MadVet) 146 146 21 356 1128 60.815 28.482 28 18 May 2008 20 May 2008
6 Любченко В.А. (IAS56) 146 146 21 356 615 403.439 373.617 28 11 May 2008 08 Jun 2008
7 Голубин Р.С. (Roman S. Golubin) 145 145 21 353 997 92.927 58.822 25 11 May 2008 13 Jun 2008
8 Мурашкин И.В. (lepton) 143 146 21 347 767 37.107 26.815 21 28 Apr 2008 12 May 2008
9 Белогурова К. (Katy_Ekb) 142 143 21 344 361 10.714 4.673 18 20 May 2008 27 May 2008
10 Войнов П.Е. (pаparome) 143 146 21 345 992 3.045 .213 17 12 May 2008 11 Jun 2008
11 Северюхин Ю.А. (Venser) 138 142 21 335 335 4.925 .655 14 01 Feb 2008 04 Feb 2008
12 Борисенков Д.В. (xuser) 141 142 21 340 92 3.217 .926 14 02 May 2008 30 May 2008
13 Мишин С.А. (CepbIu) 142 142 21 342 40 9.754 2.148 14 28 May 2008 09 Jun 2008
14 Тарасов Д.Б. (Gavrila) 141 142 21 339 998 23.531 2.501 14 20 May 2008 11 Jun 2008
15 Солдатенков Ю.С. (SolYUtor) 139 142 21 336 703 17.844 2.695 14 20 Apr 2008 13 Jun 2008
16 Кувалкин К.С. (Cyrilus) 139 142 21 336 1207 13.037 2.782 14 15 Mar 2008 08 Jun 2008
17 Шептунов П.П. (Dzen) 137 142 21 334 279 8.120 3.499 14 02 Oct 2007 15 Nov 2007
18 Селезнёв А.С. (Артём С.) 140 142 21 339 127 15.589 4.279 14 14 Mar 2008 31 Mar 2008
19 iglbeat (iglbeat) 142 142 21 342 281 27.442 6.641 14 21 May 2008 21 May 2008
20 Мальцев А.В. (Палкин) 137 142 21 334 422 48.779 7.690 14 13 Oct 2007 20 Jan 2008

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 >Козулин А.А. (Mefistophel) 62 62 114 34 148 763 13 Jun 2008
2 Опёнов С.Л. (Enemy) 49 67 103 34 137 584 09 Jun 2008
3 >Шелковников (shelk) 65 78 121 0 121 383 13 Jun 2008
4 Баландина Т.В. (MaggyDolphin) 53 53 100 9 109 1453 11 Jun 2008
5 K (Yurko) 27 47 61 12 73 1472 13 Jun 2008
6 >Бобин А.В. (rangas) 15 70 28 34 62 515 13 Jun 2008
7 Церковный (Baly) 31 31 55 5 60 3490 12 Jun 2008
8 Чижиков П. (MadWizard) 32 32 60 0 60 3495 11 Jun 2008
9 Башков А.В. (Freestyle) 28 28 50 9 59 3579 12 Jun 2008
10 Бутримов Г.Б. (gennadiy) 30 30 57 0 57 3748 11 Jun 2008
11 Товстыко С. (Starnger) 28 28 53 3 56 3831 11 Jun 2008
12 >Воротников А. (Длинный) 29 29 55 0 55 3926 13 Jun 2008
13 Бершаков М. (max506) 19 32 45 9 54 2847 10 Jun 2008
14 Зайцев В.С. (Sleeping) 15 25 34 19 53 3230 10 Jun 2008
15 >Симохин П.А. (Reno) 26 30 51 0 51 3892 13 Jun 2008
16 Mosyagin A. (mopnux) 26 65 47 0 47 685 12 Jun 2008
17 Karginov (tty13) 11 44 27 19 46 1407 12 Jun 2008
18 Рагозина Н.В. (Altera) 14 38 34 12 46 2175 12 Jun 2008
19 Крашенинников А.П. (nebelstreif) 20 30 45 0 45 3859 11 Jun 2008
20 Евлампиев А.В. (Pyc) 4 69 10 33 43 539 11 Jun 2008
21 >Хилько В.М. (Voledy) 7 71 19 22 41 595 13 Jun 2008
22 >Лойко (URAN) 23 23 41 0 41 5385 13 Jun 2008
23 >Мартыненко Д.В. (D.Martynenko) 23 23 37 3 40 5458 13 Jun 2008

Изучаем SQL

MS SQL 2005: оконные функции (окончание, начало в вып.193)

Краткое переложение Карасевой Н.В. статьи Ивана Бодягина (оригинал)

Синтаксис прост. После функции указывается конструкция:

OVER ( [ PARTITION BY value_expression , ... [ n ] ])

где [n]- список полей, по которым производится группировка, при этом использование алиасов или выражений не допускается. Собственно, таким образом и формируется "окно" для работы аналитической функции. В "окно" попадают все записи, сгруппированные по указанной колонке. Эта группировка делает практически то же самое, что и оператор GROUP BY, но с парой отличий.

Во-первых, как уже говорилось, такая группировка производится по уже сформированной выборке, а во-вторых, она распространяется только на тот агрегат, после которого идет конструкция OVER (…), а не на все колонки. И если есть необходимость использовать две аналитические функции в одном запросе, то для каждой функции конструкция OVER (…) указывается отдельно.

Строго говоря, результат запроса с аналитической суммой полностью аналогичен результату такого запроса, написанного в "старом стиле":

SELECT A.B_DATETIME,A.B_Q_ID,A.B_V_ID,C
FROM UTB A
JOIN
(SELECT B_Q_ID,B_V_ID,COUNT(*) C
FROM UTB
GROUP BY B_Q_ID,B_V_ID) B
ON A.B_Q_ID=B.B_Q_ID AND A.B_V_ID=B.B_V_ID

Функции ранжирования

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

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

На данный момент имеется 4 функции ранжирования:

ROW_NUMBER()

Эта функция нумерует записи в указанном порядке внутри "окна". Но если в конструкции OVER опустить секцию PARTITION BY, то за "окно" будет принята вся выборка - что дает возможность пронумеровать все записи в должном порядке. Причем порядок нумерации может не совпадать с порядком записей в результирующей выборке, то есть оператор ORDER BY внутри OVER(…), определяющий порядок сортировки записей внутри "окна", и, соответственно, порядок нумерации записей может не совпадать с оператором ORDER BY в конструкции SELECT, определяющей порядок выдачи записей клиенту. Нумерация всегда начинается с единицы.

RANK()

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

DENSE_RANK()

Эта функция выполняет "плотное" ранжирование, то есть делает ровно то же самое, что и предыдущая, но без дырок" в нумерации.

NTILE()

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

Для демонстрации различий функций ранжирования можно выполнить следующий запрос:

SELECT B_Q_ID, B_V_ID,
  ROW_NUMBER() OVER(PARTITION BY B_Q_ID ORDER
                              BY B_V_ID DESC) N_Row,
  RANK() OVER(PARTITION BY B_Q_ID ORDER
                              BY B_V_ID DESC)RANK,
  DENSE_RANK() OVER(PARTITION BY B_Q_ID ORDER
                              BY B_V_ID DESC)DENSE_RANK,
  NTILE(2) OVER(PARTITION BY B_Q_ID ORDER BY B_V_ID DESC)NTILE
FROM UTB WHERE B_Q_ID = 4

Результат
B_Q_ID      B_V_ID  N_Row  RANK  DENSE_RANK  NTILE
----------- ------- ------ ----- ----------- ------
4           37      1      1     1           1
4           37      2      1     1           1
4           10      3      3     2           2

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

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

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

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

Контакты

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