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

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

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


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

http://www.sql-ex.ru

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

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

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

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

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

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

§ Появившийся после долгого перерыва Strider5 нашел ошибку в английской формулировке задачи 58. Была указана не та таблица; исправил.

§ Решил первую задачу 3 этапа PS_Sergey - 11 место.
Участников третьего этапа прибыло - Палкин решил 138 задачу (время на втором этапе 20.184).

§ В сотне появился:
Kamin (задач 106, время 28.875)

§ Продвинулись в рейтинге:
Goga_3040 (135, 61.903)
Дайнин (127, 98.278)
ds (120, 102.083)
arm (118, 34.054)
Tunin (124, 33.547)
cmalex (114, 29.626)
Strider5 (108, 5.345)

§ На этой неделе сертифицированы:
tatyana_d (A06013264) [BK] (г.Москва, Россия)

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

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

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

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

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

No Person Number of
Sel_ex		 Last_Sel Number of
DML_ex		 Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 Агапов В. (KERBEROS) 141 58 20 333 89 6.183 1.262 11 20 Nov 2006 01 Dec 2006
2 Карасёва Н.В. (vlksm) 141 58 20 333 196 27.850 2.710 11 18 Nov 2006 01 Dec 2006
3 Мурашкин И.В. (lepton) 141 58 20 333 240 14.865 4.724 11 18 Nov 2006 01 Dec 2006
4 Голубин Р.С. (Roman S. Golubin) 141 58 20 333 457 54.984 33.803 11 18 Nov 2006 01 Dec 2006
5 Кувалкин К.С. (Cyrilus) 140 58 20 329 728 11.471 1.573 7 22 Nov 2006 01 Dec 2006
6 Васьков Е.В. (Johan) 140 58 20 329 124 12.713 11.402 7 20 Nov 2006 28 Nov 2006
7 Войнов П.Е. (pаparome) 139 58 20 326 453 2.689 .000 4 20 Nov 2006 01 Dec 2006
8 Юлдашев М.Р. (Snowbear) 138 139 20 324 441 4.106 .000 3 02 Oct 2006 01 Dec 2006
9 Тарасов Д.Б. (Gavrila) 139 139 20 325 454 20.006 .277 3 23 Nov 2006 01 Dec 2006
10 Держальцев В.А. (MadVet) 138 139 20 324 540 34.249 3.085 3 08 Oct 2006 19 Oct 2006
11 Палий С.А. (PS_Sergey) 139 139 20 325 212 15.756 4.188 3 01 Dec 2006 01 Dec 2006
12 Зверев Д.Л. (dimzv) 138 58 20 322 1114 4.386 .000 0 22 Nov 2006 01 Dec 2006
13 Мальцев А.В. (Палкин) 138 138 20 322 101 20.184 .000 0 26 Nov 2006 01 Dec 2006
14 Утёнков М.Н. (=Maxim=) 138 58 20 322 200 24.998 .000 0 19 Nov 2006 01 Dec 2006
15 Валуев Д.И. (Fiolent) 138 58 20 322 1173 54.715 .000 0 20 Nov 2006 01 Dec 2006
16 Slobodcicov A.N. (Testo) 136 138 20 319 337 7.171 .000 0 25 Aug 2006 10 Nov 2006
17 Иванов А.Н. (Goapsy) 136 138 20 319 270 18.911 .000 0 07 Aug 2006 09 Oct 2006
18 Абашин П.И. (Dizil) 137 58 20 318 453 3.966 .000 0 20 Nov 2006 30 Nov 2006
19 Крижевич С.А. (yaff) 136 137 20 317 481 14.998 .000 0 24 Oct 2006 25 Oct 2006
20 Вязовецков А.С. (alex_v) 136 137 20 317 141 22.788 .000 0 20 Oct 2006 20 Oct 2006

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 Кордюм А. (avk) 46 46 83 28 111 783 01 Dec 2006
2 >Khomchenko (soft) 42 42 76 32 108 834 01 Dec 2006
3 >Aberrant (aberrant) 46 46 83 0 83 1352 01 Dec 2006
4 Андрианов И.А. (IgorAndrianov) 44 44 77 0 77 1489 26 Nov 2006
5 Шабоха В.Д. (SVD) 39 45 74 0 74 1408 29 Nov 2006
6 >Коваленко К.Е. (kke) 40 40 73 0 73 1617 01 Dec 2006
7 >Старкова М. (Morozka) 19 50 45 23 68 767 01 Dec 2006
8 Мартынов Л.И. (martini) 32 50 66 0 66 1227 30 Nov 2006
9 >slava S.S. (slava333) 26 44 55 7 62 1301 01 Dec 2006
10 >Бородкина М.И. (marishkin) 23 72 50 9 59 269 01 Dec 2006
11 Kosolapov S. (lmario) 35 35 59 0 59 2094 30 Nov 2006
12 Sergeeva (alexia1985) 28 28 49 7 56 2241 28 Nov 2006
13 >Семёненко К.А. (semen_ssem) 32 32 56 0 56 2244 01 Dec 2006
14 >G R.V. (GRV) 31 31 56 0 56 2247 01 Dec 2006
15 Лазаренко А.В. (arfix) 29 29 52 3 55 2296 27 Nov 2006
16 Чирков (cpp_2003) 32 32 53 0 53 2369 27 Nov 2006
17 Бурлаков А. (eifory) 20 39 41 9 50 1667 01 Dec 2006
18 Моржов М.С. (Galliot) 33 33 50 0 50 2499 29 Nov 2006
19 Жирнов Д.А. (ДимонХХХ) 16 59 34 15 49 424 01 Dec 2006
20 Новокшонова Ю.А. (Ulia) 28 28 49 0 49 2543 28 Nov 2006
21 Комарова А.Г. (AnnKom) 28 28 49 0 49 2578 28 Nov 2006
22 >Талалаев Д.А. (Satana) 27 27 42 5 47 2693 01 Dec 2006
23 >Ерофеева Е. (elenae) 28 28 45 0 45 2783 01 Dec 2006
24 >Hasnaruni H. (hasulnarul) 22 55 44 0 44 942 01 Dec 2006
25 >Виноградов С.М. (Botch) 20 95 43 0 43 136 01 Dec 2006
26 Алпысов (Maverick_kz) 20 37 43 0 43 1890 30 Nov 2006
27 Тихонова (Kitto) 26 26 43 0 43 2863 30 Nov 2006
28 Шваков И. (Drossel) 24 24 37 3 40 3038 27 Nov 2006

Изучаем SQL

Пропущенные даты

На одном форуме был задан вопрос о том, как вывести все даты, отсутствующие в некоторой последовательности дат.

Например, в списке
2006-11-16
2006-11-18
2006-11-19
2006-11-23

отсутствуют даты: 2006-11-17
2006-11-20
2006-11-21
2006-11-22

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

Однако в SQL Server 2005 появилась возможность писать рекурсивные запросы с помощью CTE (общие табличные выражения). Кстати говоря, рекурсивные запросы уже стандартизованы, так что их использование не является лишь особенностью конкретной реализации, хотя имеются различия в синтаксисе.

Итак, я решил написать CTE-запрос, чтобы ответить на вопрос и приобрести некоторый практический опыт.

Создадим временную табличку с тестовыми данными:

CREATE TABLE #Tdates(DT DATETIME)
INSERT INTO #Tdates
SELECT '20061116' AS DT
UNION ALL
SELECT '20061118'
UNION ALL
SELECT '20061119'
UNION ALL
SELECT '20061123'

Идея решения очень проста:

- С помощью рекурсивного CTE-запроса строим последовательность дат, т.е. ряд значений с шагом 1 от минимальной до максимальной даты из таблицы #Tdates. Это может выглядеть следующим образом:

WITH t(d,lim) AS
(
SELECT MIN(dt) d, MAX(dt) lim FROM #Tdates
UNION ALL
SELECT t.d+1,lim FROM t
WHERE d < lim
)

Ограничение d < lim необходимо, чтобы рекурсия не была бесконечной. Кстати говоря, по умолчанию используется 100 итераций, т.е. если предел не будет указан или результат не будет получен за 100 итераций, то появится следующее сообщение:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
(Выполнение оператора прервано. Максимальное число итераций 100 было исчерпано до завершения выполнения оператора).

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

option (maxrecursion 0)

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

Итак,
1. Использование предиката NOT IN

WITH t(d,lim) AS
(
SELECT MIN(dt) d, MAX(dt) lim FROM #Tdates
UNION ALL
SELECT t.d+1,lim FROM t
WHERE d < lim
)
SELECT t.d FROM t
WHERE t.d NOT IN (SELECT dt FROM #Tdates)
OPTION (MAXRECURSION 0)

2. Использование предиката NOT EXISTS

WITH t(d,lim) AS
(
SELECT MIN(dt) d, MAX(dt) lim FROM #Tdates
UNION ALL
SELECT t.d+1,lim FROM t
WHERE d < lim
)
SELECT t.d FROM t
WHERE t.d NOT EXISTS (SELECT * FROM #Tdates WHERE dt=t.d)
OPTION (MAXRECURSION 0)

3. Использование внешнего соединения

WITH t(d,lim) AS
(
SELECT MIN(dt) d, MAX(dt) lim FROM #Tdates
UNION ALL
SELECT t.d+1,lim FROM t
WHERE d < lim
)
SELECT t.d FROM t LEFT JOIN #Tdates t1
ON t.d=t1.dt
WHERE t1.dt IS NULL
OPTION (MAXRECURSION 0)

4. Использование явной операции разности (EXCEPT) из стандарта SQL-92, появившейся в SQL Server только в версии 2005.

WITH t(d,lim) AS
(
SELECT MIN(dt) d, MAX(dt) lim FROM #Tdates
UNION ALL
SELECT t.d+1,lim FROM t
WHERE d < lim
)
SELECT t.d FROM t
EXCEPT
SELECT dt FROM #Tdates T1
OPTION (MAXRECURSION 0)

Я уже приготовился к тому, что на таком незначительном объеме данных все алгоритмы дадут примерно одинаковый результат (по фактическому плану выполнения). Так оно и оказалось, но только для первых трех "классических" методов. Если выполнить сразу пакет из вышеприведенных 4 запросов, то почти половина времени уйдет на выполнения последнего запроса.

Я внимательно не анализировал его план, но обратил внимание на то, что большая часть времени здесь ушла на выполнение сортировки. Оно и понятно, если вспомнить о том, что эта операция должна оставить только уникальные строки, т.е. удалить дубликаты, что выполняется с помощью сортировки. Первое, что пришло в голову, написать EXCEPT ALL. Однако оказалось, что такая конструкция пока не поддерживается.

Вывод, который я для себя сделал, заключается в том, что не следует использовать EXCEPT, если вам попутно не нужно устранить дубликаты.

§ Напомню, что если вы не являетесь участником третьего этапа, то можете писать CTE-запросы на сайте, поставив флажок "Без проверки" на странице с упражнениями на SELECT.

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

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

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

Контакты

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