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

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


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

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

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

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

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

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


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

§ По просьбе Nariman Kurbanoff добавил в нижнее меню пункты, относящиеся к оптимизации запросов и оценке их стоимости.

§ Добавил проверочные данные от vlksm для задачи 148.

§ @Nikotin заметил ошибку в справке, посвященной особенностям использования функции LEN. Были переставлены местами аргументы в функции REPLICATE.
Исправил ошибку и дописал пояснение к получаемым результатам.

§ Кому интересно как выглядел сайт в прошлом, можно воспользоваться архивом интернета: на старом домене и новом домене.
Можно вспомнить прошлых лидеров и то, как реализовывались ваши предложения по развитию ресурса.

§ Идет народное голосование в рамках Премии Рунета. Просьба поддержать наш сайт, который вышел во второй тур конкурса, который продлится до конца октября. Голосовать можно раз в сутки одновременно за <=50 проектов.

§ Изменения среди лидеров (решенные за неделю задачи третьего этапа):
10. lepton (148)
33. PandNsk (142)
38. Umrikhina (139)

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

§ Новые лица в ТОР 100 и вернувшиеся туда:
60. @Nikotin (133, 4.573)
83. D2NX (127, 502.556)

§ Продвинулись в рейтинге:
45. DeadLock5 (136, 132.413)
77. Oxana (128, 55.492)

§ Продвижение ближайших претендентов на попадание в ТОР 100:
102. orange (125, 13.072)
121. Ashton (121, 14.570)
126. Sergey79 (121, 281.526)
137. Pavel_yu (120, 113.124)
157. Madest (112, 45.367)
185. TimonSP (110, 1.314)

§ На этой неделе сертифицированы:
uon (A08033517) [BK] - г.Новосибирск, Россия
mipiace (A08033286) [BK] - г.Тула, Россия
glassman (B08034479) [AR] - г.Екатеринбург, Россия
D2NX (B08013870) [AR] - г.Могилев, Беларусь

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

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

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

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

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

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

No Person Number of
Sel_ex
Last_Sel Number of
DML_ex
Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 Селезнёв А.С. (Артём С.) 148 147 21 360 322 38.520 29.234 36 25 Sep 2008 03 Oct 2008
2 Карасёва Н.В. (vlksm) 149 149 21 363 866 77.241 49.173 36 18 Sep 2008 03 Oct 2008
3 Сальников С.А. ($erges) 148 54 21 361 196 2.854 3.359 34 09 Sep 2008 03 Oct 2008
4 Креславский О.М. (Arcan) 148 148 21 360 605 40.052 28.202 33 26 Sep 2008 03 Oct 2008
5 Печатнов В.В. (pvv) 147 148 21 357 341 21.816 8.440 30 24 Sep 2008 03 Oct 2008
6 Держальцев В.А. (MadVet) 140 15 21 344 1257 60.815 28.482 28 24 Sep 2008 25 Sep 2008
7 Любченко В.А. (IAS56) 139 146 21 343 615 403.439 373.617 28 11 May 2008 16 Aug 2008
8 Голубин Р.С. (Roman S. Golubin) 143 54 21 346 1122 93.054 58.822 25 13 Sep 2008 01 Oct 2008
9 Nikolaenko A.V. (Shadow77) 143 54 21 346 410 51.575 14.010 23 26 Sep 2008 29 Sep 2008
10 Мурашкин И.В. (lepton) 142 148 21 345 921 43.485 33.011 23 29 Sep 2008 02 Oct 2008
11 Солдатенков Ю.С. (SolYUtor) 141 146 21 342 819 22.703 6.102 20 14 Aug 2008 24 Sep 2008
12 Белогурова К. (Katy_Ekb) 135 143 21 331 361 10.714 4.673 18 20 May 2008 05 Sep 2008
13 Егоров А.Б. (ABEgorov) 140 144 21 340 180 12.917 8.815 18 03 Aug 2008 12 Aug 2008
14 Войнов П.Е. (pаparome) 142 88 21 341 1125 3.132 .213 17 22 Sep 2008 03 Oct 2008
15 iglbeat (iglbeat) 140 145 21 339 360 34.601 15.225 17 08 Aug 2008 25 Aug 2008
16 Северюхин Ю.А. (Venser) 131 142 21 322 335 4.925 .655 14 01 Feb 2008 04 Feb 2008
17 Дроздков А.Н. (anddros) 142 141 21 341 121 4.095 .780 14 25 Sep 2008 03 Oct 2008
18 Борисенков Д.В. (xuser) 136 55 21 329 236 3.245 .926 14 23 Sep 2008 02 Oct 2008
19 Мишин С.А. (CepbIu) 137 142 21 331 69 9.772 2.148 14 26 Jun 2008 10 Sep 2008
20 Тарасов Д.Б. (Gavrila) 138 142 21 333 1069 24.230 2.501 14 30 Jul 2008 03 Oct 2008

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 >tomin A. (pseudo) 42 42 79 0 79 2304 03 Oct 2008
2 Коломыцев Е.В. (Lcd) 26 38 58 11 69 2154 03 Oct 2008
3 Lentsevich Y.A. (Lentsevich Y.A.) 25 36 60 5 65 2431 02 Oct 2008
4 >Monchenko S. (msv_sergey) 27 37 59 0 59 2841 03 Oct 2008
5 Демьянов Е. (йоги-джан) 25 37 57 0 57 2838 02 Oct 2008
6 B Y. (Wozz) 25 25 46 11 57 3756 03 Oct 2008
7 Grinets S.A. (Merhill) 26 26 47 9 56 3842 03 Oct 2008
8 Pastukh V. (Pastukh) 26 26 47 9 56 3843 03 Oct 2008
9 Vagner (nixxx) 26 26 47 9 56 3844 03 Oct 2008
10 >Lozinsky A.O. (loz) 25 25 46 9 55 3932 03 Oct 2008
11 >Barandiy (v0mbaT) 26 26 47 7 54 4034 03 Oct 2008
12 >Mykhalchuk (Maryan87) 26 26 47 7 54 4038 03 Oct 2008
13 >Braiko V.O. (Shacka) 25 25 46 8 54 4041 03 Oct 2008
14 >Sydor V. (_titan_) 26 26 47 7 54 4042 03 Oct 2008
15 >Pismennyj O. (polev-) 26 26 47 7 54 4044 03 Oct 2008
16 >Pashchak (Roman.P) 25 25 46 7 53 4122 03 Oct 2008
17 >Znansky A. (adonis) 27 27 48 3 51 4321 03 Oct 2008
18 >Ольшанский А.В. (alx_ol) 7 51 12 34 46 918 03 Oct 2008
19 >рослякова Т. (nosebien) 21 24 38 0 38 5545 03 Oct 2008
20 >Аверина (Camry) 21 21 37 0 37 5883 03 Oct 2008
21 >Никотин В.М. (@Nikotin) 16 133 36 0 36 60 03 Oct 2008
22 >Фалина А.И. (лай-лай) 17 29 36 0 36 4163 03 Oct 2008
23 >Karaszewski K. (Karasinho8) 21 21 36 0 36 6012 03 Oct 2008
24 Мосолов П.А. (mosolov) 20 20 33 3 36 6028 03 Oct 2008
25 >кипятков Ф.Н. (Igoryog) 17 29 35 0 35 4366 03 Oct 2008
26 Свиридов К.Ю. (orange) 14 125 34 0 34 102 03 Oct 2008
27 Малиновский И.А. (Vantol) 14 26 34 0 34 4800 29 Sep 2008
28 Сдыков Д.Ж. (dako) 4 68 10 23 33 681 03 Oct 2008
29 >Вартазарян (GANZA) 14 26 33 0 33 4964 03 Oct 2008
30 Писаренко Д.Н. (dimaP) 4 71 11 21 32 493 03 Oct 2008
31 Vasiliev A. (Andrey_knit) 7 45 15 17 32 1496 03 Oct 2008
32 serg (sergey25) 16 16 23 9 32 6502 03 Oct 2008
33 >Gulina M.V. (proliv) 10 67 18 13 31 862 03 Oct 2008
34 Михайлов В.И. (ilich_) 10 10 11 19 30 6966 03 Oct 2008

Изучаем SQL

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

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

Теперь поговорим о решении в курсорах. Сила этого решения в том, что здесь можно сканировать упорядоченные данные. Учитывая тот факт, что каждая сессия представляет 2 события: одно -увеличение количества активных сессий , другое - их уменьшение, - для курсора я воспользуюсь следующим запросом:

SELECT app, starttime AS ts, 1 AS event_type FROM dbo.Sessions
UNION ALL
SELECT app, endtime, -1 FROM dbo.Sessions
ORDER BY app, ts, event_type;

Этот запрос возвращает следующие данные для каждого старта сессии или прекращения сессии: приложение (app), временная метка (ts); тип события (event_type) значение +1 для старта сессии или -1 для конца сессии. События сортируются по app, ts и event_type. Причина для сортировки по app, ts очевидна. Добавление сортировки по event_type гарантирует, что если сессия заканчивается в то же самое время, когда начинается другая сессия, то сначала будет следовать событие завершения сессии. Код курсора будет прямолинейным - простое сканирование данных с добавлением по +1 и -1 для каждого приложения. При сканировании каждой новой строки, нужно будет проверять, является ли суммарное к этому моменту значение больше, чем текущий максимум для данного приложения, которое вы сохранеете в переменной. Если да, то это значение сохраняется как новый максимум. Когда рассмотрены все записи для текущего приложения, в табличную переменную добавляется запись, содержпащая ID приложения и соответствующий максимум. Вот полное решение в курсорах:

DECLARE
  @app AS VARCHAR(10), @prevapp AS VARCHAR (10), @ts AS datetime,
  @event_type AS INT, @concurrent AS INT, @mx AS INT;

DECLARE @Result TABLE(app VARCHAR(10), mx INT);

DECLARE C CURSOR FAST_FORWARD FOR
  SELECT app, starttime AS ts, 1 AS event_type FROM dbo.Sessions
  UNION ALL
  SELECT app, endtime, -1 FROM dbo.Sessions
  ORDER BY app, ts, event_type;

OPEN C;

FETCH NEXT FROM C INTO @app, @ts, @event_type;
SELECT @prevapp = @app, @concurrent = 0, @mx = 0;

WHILE @@fetch_status = 0
BEGIN
  IF @app <> @prevapp
  BEGIN
    INSERT INTO @Result VALUES(@prevapp, @mx);
    SELECT @prevapp = @app, @concurrent = 0, @mx = 0;
  END

  SET @concurrent = @concurrent + @event_type;
  IF @concurrent > @mx SET @mx = @concurrent;

  FETCH NEXT FROM C INTO @app, @ts, @event_type;
END

IF @prevapp IS NOT NULL
  INSERT INTO @Result VALUES(@prevapp, @mx);

CLOSE C

DEALLOCATE C

SELECT * FROM @Result;

Решение в курсорах сканирует листья индекса только дважды. Вы можете представить стоимость решения как N х 2 х V, где V - затраты на манипуляцию с каждой строкой курсора. Если траффик растет пропорционально f, то производительность падает линейно как N х 2 х V х f. Понятно, что если вы имеете дело с не очень маленьким входным набором, то решение в курсорах потенциально должно выполняеться гораздо быстрее и, как доказательство, на рисунке 4-2 приведены графики выполненных мною тестов.

Рисунок 4-2. Испытания для решения задачи максимума параллельных сессий

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

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

SELECT app, MAX(concurrent) AS mx
FROM (SELECT app, SUM(event_type)
        OVER(PARTITION BY app ORDER BY ts, event_type) AS concurrent
      FROM (SELECT app, starttime AS ts, 1 AS event_type
            FROM dbo.Sessions
            UNION ALL
            SELECT app, endtime, -1 FROM dbo.Sessions) AS D1) AS D2
GROUP BY app;

Стоимость решения в курсорах было оценено нами ранее как N х 2 х V, в то время, как цена решения с использованием предложения OVER могла бы быть N х 2 . То есть эквивалетной стоимости решения в курсорах без расхода V.

Можно было бы привести и другие примеры и бизнес-сценарии, которые бы выиграли от поддержки ORDER BY для агрегатных функций с использованием OVER, но, надеюсь, и так все понятно.

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

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

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

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

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

Контакты

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

Подписка Subscribe.Ru
Новости сайта "Упражнения по SQL"

В избранное