Отправляет 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
31


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


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

Автор
Serge I

Статистика

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

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


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

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

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

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

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

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


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

§ anddros заметил, что оптимистический рейтинг считается на основе максимального решенного упражнения. Когда-то число решенных задач совпадало с максимальным номером решенной задачи. Сейчас это далеко не так, поскольку и задачи меняются, и на третьем этапе можно решать задачи в произвольном порядке. Поправил, теперь рейтинг исходит из числа решенных задач.

§ Выполнил очередную чистку рядов. На этот раз база облегчилась почти на 2000 случайных посетителей.
Посему не стоит удивляться возможным изменениям в нижней части рейтинга.
Хочу поделиться одним наблюдением. На протяжении нескольких лет число участников второго этапа составляло около 10% от общего числа участников рейтинга. После очередной чистки это число всегда превосходило 10%. Сейчас же этот процент стал существенно ниже 1441/16189*100%=8,9%. И это несмотря на то, что число задач первого этапа уменьшилось (с 62 до 54).
Я объясняю это усложнением задач первого этапа, которые раньше проходились без особого труда. Т.е. происходит, собственно, то, чего я и добивался, - сделать первый этап фильтром, разделяющим обучение и соревнование (тестирование специалистов).
К сентябрю я готовлю очередную серию задач от нескольких авторов, которая продолжит этот процесс. :-)

§ Со страницы голосования по задачам теперь можно перейти на форум соответствующей задачи.

§ Изменения среди лидеров (решенные за неделю задачи третьего этапа):
24. Shadow77 (142, 145, 146)

§ Продвинулись в рейтинге:
40. TomGolab (задач 136, время 47.991)
46. avk (134, 63.432)
48. _Bkmz_ (134, 3.931)
55. Ocean (132, 40.378)
57. Fencer (131, 200.277)
82. Shurgenz (127, 12.602)

§ Продвижение ближайших претендентов на попадание в ТОР 100:
106. Rash ST (122, 7.565)
118. Sergey79 (121, 263.490)
124. shadon (120, 15.949)
137. Vendigo (117, 18.729)
149. Edward_rost (112, 127.444)
155. mz (112, 25.874)
162. comrade (111, 142.061)

§ На этой неделе сертифицированы:
Aviaradist (A08028166) [BK] - г.Москва, Россия
Длинный (A08034586) [BK] - г.Воронеж, Россия
_Bkmz_ (B08030229) [AR]) - г.Сыктывкар, Россия
Skypchel (A08034979) [BK] - г.Москва, Россия
depr (A08031580) [BK] - г.Чита, Россия

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

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

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

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

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

Лучшие результаты (ТОР 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 357 118 2.842 3.359 31 23 Jun 2008 18 Jul 2008
2 Креславский О.М. (Arcan) 147 147 21 357 517 37.671 27.498 31 30 Jun 2008 18 Jul 2008
3 Карасёва Н.В. (vlksm) 147 147 21 357 778 73.340 46.383 31 22 Jun 2008 16 Jul 2008
4 Печатнов В.В. (pvv) 146 146 21 354 257 19.426 6.326 28 02 Jul 2008 18 Jul 2008
5 Держальцев В.А. (MadVet) 142 146 21 347 1128 60.815 28.482 28 18 May 2008 23 Jun 2008
6 Любченко В.А. (IAS56) 142 146 21 347 615 403.439 373.617 28 11 May 2008 08 Jun 2008
7 Голубин Р.С. (Roman S. Golubin) 144 145 21 348 1044 92.981 58.822 25 27 Jun 2008 18 Jul 2008
8 Мурашкин И.В. (lepton) 144 146 21 347 839 37.289 26.815 21 09 Jul 2008 09 Jul 2008
9 Белогурова К. (Katy_Ekb) 138 143 21 335 361 10.714 4.673 18 20 May 2008 08 Jul 2008
10 Войнов П.Е. (pаparome) 143 146 21 343 1034 3.103 .213 17 23 Jun 2008 18 Jul 2008
11 Северюхин Ю.А. (Venser) 134 142 21 326 335 4.925 .655 14 01 Feb 2008 04 Feb 2008
12 Борисенков Д.В. (xuser) 137 142 21 331 92 3.217 .926 14 02 May 2008 30 May 2008
13 Мишин С.А. (CepbIu) 140 142 21 335 69 9.772 2.148 14 26 Jun 2008 04 Jul 2008
14 Тарасов Д.Б. (Gavrila) 140 74 21 334 1054 23.637 2.501 14 15 Jul 2008 18 Jul 2008
15 Солдатенков Ю.С. (SolYUtor) 135 142 21 327 703 17.844 2.695 14 20 Apr 2008 18 Jul 2008
16 Кувалкин К.С. (Cyrilus) 135 142 21 327 1207 13.037 2.782 14 15 Mar 2008 01 Jul 2008
17 Шептунов П.П. (Dzen) 133 142 21 325 279 8.120 3.499 14 02 Oct 2007 15 Nov 2007
18 Селезнёв А.С. (Артём С.) 136 142 21 330 127 15.589 4.279 14 14 Mar 2008 31 Mar 2008
19 iglbeat (iglbeat) 142 89 21 340 336 28.490 6.641 14 15 Jul 2008 15 Jul 2008
20 Мальцев А.В. (Палкин) 133 142 21 325 422 48.779 7.690 14 13 Oct 2007 18 Jun 2008

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 >Буланов П. (rage) 78 78 152 2 154 597 18 Jul 2008
2 Petrov N. (sql chuvak) 54 54 101 0 101 1524 14 Jul 2008
3 >Сотов А.С. (Asotov) 50 50 93 0 93 1767 18 Jul 2008
4 Веселов А.А. (@ngel) 38 44 81 0 81 2018 17 Jul 2008
5 Avilov S. (serg_avilov) 34 34 61 19 80 2275 17 Jul 2008
6 >Поляков А.Н. (Warri) 26 62 45 34 79 731 18 Jul 2008
7 Чедакин С. (PowWow) 37 37 72 0 72 2675 14 Jul 2008
8 >Коломиец М. (Юзверь) 36 36 69 1 70 2789 18 Jul 2008
9 >Shevyakov M. (Mikha) 21 62 35 34 69 732 18 Jul 2008
10 Sabara A.T. (Tohich) 19 55 35 34 69 832 18 Jul 2008
11 > (am210) 35 35 65 0 65 3095 18 Jul 2008
12 >Черненко Н.В. (Chen) 28 43 63 0 63 1346 18 Jul 2008
13 >Potapchenko A. (Lightfore.sql) 20 20 30 32 62 3305 18 Jul 2008
14 Gluski R.J. (gustaw_bmf) 24 24 43 9 52 4125 15 Jul 2008
15 >Dm (duset) 24 32 51 0 51 3455 18 Jul 2008
16 Нехайчик В.А. (ev1L) 14 32 37 13 50 2347 17 Jul 2008
17 >Бррр С.А. (io) 21 31 47 0 47 3622 18 Jul 2008
18 Kushnir (OKus) 19 33 45 0 45 3385 18 Jul 2008
19 >Kolesen A. (imm0use) 24 24 44 0 44 4975 18 Jul 2008
20 Ведерников В.В. (Val Flash) 18 47 34 8 42 1588 18 Jul 2008
21 Колесников А.С. (FenixABC) 17 28 38 3 41 4038 17 Jul 2008
22 Souc J. (sulo) 4 45 6 34 40 1090 18 Jul 2008

Изучаем SQL

Требования к SQL Server по усилению поддержки: предложение OVER() и упорядоченные вычисления (продолжение, начало в вып.195)

Itzik Ben-Gan и Sujata Mehta (оригинал: SQL Server Feature Enhancement Request - OVER Clause and Ordered Calculations )
Перевод Карасева Н.В.

РАЗДЕЛ 2: Ключевые элементы в SQL, позволяющие выполнять вычисления над упорядоченными данными

Целью этого раздела является введение концепции упорядоченных вычислений в SQL и объяснение того, как эта концепция закрывает некоторые из вышеуказанных "зазоров" (курсоры против наборов, противоестественная организация вычислений против более естественной). Позже в этой статье мы подробно и обстоятельно обсудим и рассмотрим на примерах недостающие элементы для вычислений с помощью конструкции OVER.

Как уже говорилось, масса задач требует вычислений с упорядоченными данными. Однако, до введения конструкции OVER в SQL приходилось делать некий "перевод" понятного вам решения задачи на традиционный язык SQL, который ориентирован на работу с множествами и не содержит понятия упорядоченных вычислений (если вы не используете курсор). Ранее мы привели пару примеров таких "переводов". Во многих случаях это приводило к написанию неестественного, сложного и неэффективного кода. ANSI SQL (OLAP -расширения к ANSI SQL: 1999, и часть из ANSI SQL: 2003) ввел концепцию упорядоченных вычислений посредством новой конструкции OVER. Мы находим эту конструкцию весьма полезной, позволяющую, в первую очередь, производить на основе порядка и не подвергающую сомнению тот факт, что результат по-прежнему является множеством. Предложение OVER поддерживается в ANSI SQL для нескольких типов вычислений (ранжирование, агрегирование и т.д.), но её концепцию можно распространить за пределы стандартного языка SQL при помощт расширений T-SQL (например, TOP). Ниже мы подробно рассмотрим этот вопрос и приведем примеры.

Вычисления с использованием предложения OVER разрешены только в предложениях SELECT и ORDER BY. Причина такого ограничения заключается в том, что OVER оперирует с результирующей таблицей, полученной после того, как все элементы SQL-запроса уже обработаны (табличные операторы в предложениях FROM,WHERE, GROUP BY, HAVING). OVER может иметь 3 элемента (не все они применимы ко всем вычислениям)

OVER(<partitioning> <ordering> <window options>)

Partitioning - разбиение данных на наборы (предложение PARTITION BY) позволяет выполнить вычисление независимо для каждого набора строк. Например, PARTITION BY empid означает, что вычисление выполняется независимо для каждого набора строк с одним и тем же значением empid. Если предложение PARTITION BY опущено, то на стадии, когда проводятся вычисления с помощью OVER, все множество рассматривается как одно разбиение.

Элемент ordering (предложение ORDER BY) задает логический порядок, в котором должны выполняться вычисления. Например, ORDER BY ordermonth означает, что вычисление должно быть выполнено в соответствии с логическим порядком ordermonth. Самое главное, что нужно здесь понять, это то, что это предложение определяет логический порядок обработки, а не порядок представления результата (как традиционное предложение ORDER BY), точнее, оно не зависит от порядка представления результата. Оно никак не влияет на природу результата, а именно, использование ORDER BY как подчиненного предложения в OVER не означает, что результат запроса будет чем-то отличным от набора данных. Однако даже если такое предложение ORDER BY определяет логический порядок вычислений, оно предоставляет основу для хорошей оптимизации запроса (использование индекса). Если предложение ORDER BY опущено, то вычисление производят над всем разбиением (окно строк, доступных для вычисления).

Элемент window option (ROWS или RANGE) позволяет ограничивать окно строками, к которым будут применяться вычисления. Например, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW означает, что в окне строк, к которым применяются вычисления находится три строки 3, начиная со второй строки, предшествующей текущей, и сама текущая запись (на основании порядока, заданного предложением ORDER BY). Если предложение ORDER BY присутствует, а вариант окна (window option) опущен, то используется вариант по умолчанию - ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (все предшествующие текущей и текущая строка).

Мы уверены, что конструкция OVER очень полезна. Для того чтобы вы почувтвовали, насколько она все упрощает и хороша для оптимизации, вернемся к 2-м ранее рассмотренным примерам - агрегатам с накоплением и сравнению соседних строк. Вспомните противоестественность этих решений и их проблемы с производительностью.

Следующие примеры используют элементы предложения OVER, не реализованные в SQL Server 2005.

Задача агрегатов с накоплением можнет быть решена следующим образом:

SELECT empid, ordermonth, qty,
  SUM(qty) OVER(PARTITION BY empid
                ORDER     BY ordermonth) AS cumulativeqty
FROM EmpOrders;

Вспомним, что когда вариант окна не определен, вариантом по умолчанию будет ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Принимая во внимание существование индекса на ключевых столбцах (empid, ordermonth) и включенные столбцы (qty), этот запрос потенциально может быть оптимизирован путем единственного сканирования упорядоченного индекса; а именно, единственого прохода по данным без необходимости явной сортировки. Другими словами, при P разбиениях и N записях в среднем на разбиение, полная стоимость плана составит P*N.

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

SELECT col1 AS cur, LEAD(col1, 1, NULL) OVER(ORDER BY col1) AS nxt
FROM dbo.T1;

Функция LEAD возвращает элемент последующей строки, смещенной относительно текущей на значение второго аргумента, а если такая запись не найдена, то возвращается значение третьего аргумента. Значение 1 во втором аргументе дает нам "следующую запись". Принимая во внимание индекс по столбцу col1, этот запрос может быть оптимизирован до однопроходного упорядоченного сканирования индекса.

По этим примерам можно понять, как просто и естественно пишется запрос, как он прекрасно оптимизируется.

В следующих разделах сначала описывается реализация OVER в SQL Server 2005, а затем еще не реализованные возможности этой конструкции, важность которых нам кажется достаточной для того, чтобы добавить их в SQL Server в будущих версиях.

(Продолжение следует...)

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

§ Мы принимаем участие в конкурсе Ростовских сайтов. Голосовать можно один раз в день, если, конечно, есть желание :-).
Чтобы голосовать требуется авторизация.

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

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

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

Контакты

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