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

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

Подписаться Бесплатная «Серебряная» новостная рассылка . Подписчиков 2.592 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.592 подписчиков
0 за неделю
←   Все выпуски   →

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


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

http://www.sql-ex.ru

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

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

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

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

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

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

§ Подлатал проверку задачи 52 в ответ на продемонстрированное Dazlak'ом подгоночное решение.

§ Принял уточнение Botch к формулировке задачи 93.

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

§ Сразу три человека на этой неделе решили 140 задачу: Gavrila (5 место), dimzv (9 место) и Fiolent (10 место).

§ В сотне появились:
Алекс (125, 49.049)
IAS56 (110, 39.737)

§ Продвинулись в рейтинге:
yaff (137, 15.010)
Tunin (128, 35.983)
Дайнин (128, 101.039)
cmalex (117, 32.866)
Fencer (116, 149.433)
Dark_Elf (109, 18.559)
Kamin (задач 108, время 30.245)

§ На этой неделе сертифицированы:
Tunin (B06013674) [AR] (г.Краснодар, Россия)

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

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

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

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

Лучшие результаты (ТОР 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 07 Dec 2006
2 Карасёва Н.В. (vlksm) 141 58 20 333 196 27.850 2.710 11 18 Nov 2006 08 Dec 2006
3 Мурашкин И.В. (lepton) 141 58 20 333 240 14.865 4.724 11 18 Nov 2006 07 Dec 2006
4 Голубин Р.С. (Roman S. Golubin) 141 58 20 333 457 54.984 33.803 11 18 Nov 2006 07 Dec 2006
5 Тарасов Д.Б. (Gavrila) 140 140 20 329 466 20.242 .513 7 05 Dec 2006 08 Dec 2006
6 Кувалкин К.С. (Cyrilus) 140 58 20 329 728 11.471 1.573 7 22 Nov 2006 08 Dec 2006
7 Васьков Е.В. (Johan) 140 58 20 329 124 12.713 11.402 7 20 Nov 2006 07 Dec 2006
8 Войнов П.Е. (pаparome) 139 58 20 326 453 2.689 .000 4 20 Nov 2006 08 Dec 2006
9 Зверев Д.Л. (dimzv) 139 140 20 326 1126 6.354 1.968 4 04 Dec 2006 08 Dec 2006
10 Валуев Д.И. (Fiolent) 139 140 20 326 1188 117.017 62.302 4 05 Dec 2006 08 Dec 2006
11 Юлдашев М.Р. (Snowbear) 138 139 20 324 441 4.106 .000 3 02 Oct 2006 08 Dec 2006
12 Держальцев В.А. (MadVet) 138 139 20 324 540 34.249 3.085 3 08 Oct 2006 19 Oct 2006
13 Палий С.А. (PS_Sergey) 139 139 20 325 212 15.756 4.188 3 01 Dec 2006 03 Dec 2006
14 Мальцев А.В. (Палкин) 138 138 20 322 101 20.184 .000 0 26 Nov 2006 04 Dec 2006
15 Утёнков М.Н. (=Maxim=) 138 58 20 322 200 24.998 .000 0 19 Nov 2006 08 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) 137 58 20 318 523 15.010 .000 0 05 Dec 2006 05 Dec 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 Galkin (galkin) 53 53 98 32 130 585 05 Dec 2006
2 Rock L. (Malfil) 56 57 104 25 129 457 05 Dec 2006
3 >Башмаков А.В. (mabus) 47 62 95 32 127 406 08 Dec 2006
4 >Касимова Л. (Lidochka) 51 51 90 32 122 682 08 Dec 2006
5 >Нурсеитов А. (frodo10) 39 48 74 26 100 812 08 Dec 2006
6 >Иняева Т.М. (tatosha) 51 51 94 0 94 1149 08 Dec 2006
7 Толстов С.А. (мультиковый) 29 29 58 32 90 1227 08 Dec 2006
8 Курочкин А.Д. (KAD) 48 48 87 0 87 1277 06 Dec 2006
9 Sergeeva (alexia1985) 29 57 58 25 83 458 06 Dec 2006
10 >Kaine G. (kaine) 43 43 72 11 83 1370 08 Dec 2006
11 Fomichev A.M. (Black Rey) 45 45 80 0 80 1428 07 Dec 2006
12 >Afo (o3) 43 43 76 0 76 1548 08 Dec 2006
13 Сапунов Н.О. (Николай Олегович) 35 35 62 9 71 1715 06 Dec 2006
14 Пец А.Н. (Сашенька) 36 36 65 3 68 1801 07 Dec 2006
15 Кемайкин Р. (Kemaikin) 33 33 58 0 58 2167 08 Dec 2006
16 Задорожная Е.В. (Zador.Ka) 28 28 49 7 56 2277 04 Dec 2006
17 Петрова О.С. (olia-ps) 28 28 49 7 56 2280 05 Dec 2006
18 Чудинова Л.А. (Любашка) 28 28 49 7 56 2284 06 Dec 2006
19 >Носков К.П. (MouseK) 15 43 28 26 54 841 08 Dec 2006
20 >Нохрин М.Б. (nmax86) 24 39 51 2 53 1767 08 Dec 2006
21 Мартынов Л.И. (martini) 9 59 20 32 52 433 08 Dec 2006
22 >Платонова Т.В. (TiVi) 26 34 50 0 50 2095 08 Dec 2006
23 Пономарев И.А. (Ебатель) 28 28 49 0 49 2585 02 Dec 2006
24 Шахторин С. (Don-Drakon) 28 28 49 0 49 2587 07 Dec 2006
25 Serbin M. (Marksman) 28 28 49 0 49 2607 06 Dec 2006
26 Новокшонова Ю.А. (Ulia) 25 53 48 0 48 1106 05 Dec 2006
27 Комарова А.Г. (AnnKom) 25 53 48 0 48 1108 05 Dec 2006
28 Гацуков Е.Н. (GENnick) 16 40 29 19 48 1413 06 Dec 2006
29 >G R.V. (GRV) 24 55 47 0 47 960 08 Dec 2006
30 Вася (Ушпулек) 21 28 42 4 46 2419 05 Dec 2006
31 slava S.S. (slava333) 13 57 29 16 45 589 08 Dec 2006
32 dddddd (asd) 22 30 43 0 43 2529 05 Dec 2006
33 >Свечинов А.В. (Алекс) 7 125 18 23 41 57 08 Dec 2006
34 Aberrant (aberrant) 8 54 18 23 41 664 08 Dec 2006
35 >mayekar S.S. (siddu) 26 26 41 0 41 3008 08 Dec 2006
36 >Ольков С.А. (Serj007) 13 13 23 17 40 3059 08 Dec 2006
37 Корман Ю.А. (dnk) 24 24 37 3 40 3062 05 Dec 2006

Изучаем SQL

Рекурсивные запросы в SQL:1999 и SQL Server 2005

Frederic BROUARD (оригинал: Recursive Queries in SQL:1999 and SQL Server 2005)
Перевод Моисеенко С.И.

Резюме:
Многие ли из вас писали рекурсивные запросы на SQL или, возможно, на каком-нибудь другом языке после школы? Не много людей пишут рекурсивные запросы из-за сложности, трудности понимания того, как они работают, и перспективы переполнения кучи. Однако SQL Server 2005 таким способом выполняет общие табличные выражения (CTE) и рекурсию, что ее стало намного легче кодировать при гарантиях некоторой безопасности. Новый автор, SQL Server MVP, Фредерик Броуард написал фантастическую статью, рассматривающую рекурсивные запросы.

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

В механических ситуациях мы не принимаем бесконечную рекурсию. В реальном мире мы должны иметь точку останова, потому что наша вселенная замкнута. Ожидание окончания бесконечного процесса, который фактически является вечностью, является тяжкой работой! Как говорит Вуди Аллен: "вечность действительно длинна, особенно вблизи конца ..."

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

Но наша цель сейчас состоит в том, чтобы поговорить о РЕКУРСИВНЫХ ЗАПРОСАХ в SQL в части стандарта ISO (Международной Организации по Стандартизации) и того, как MS SQL Server 2005 обошелся с ними.

Стандарт ISO SQL:1999

Вот краткий синтаксис РЕКУРСИВНОГО ЗАПРОСА:

WITH [ RECURSIVE ] <имя_алиаса_запроса> [ ( <список столбцов> ) ]
AS (<запрос select> )
<запрос, использующий имя_алиаса_запроса>

Просто! Не так ли? Фактически, вся механика находится внутри <запрос select >. Мы рассмотрим сначала простые не рекурсивные запросы, и когда поймем, что мы можем сделать с ключевым словом WITH, мы сорвем занавес, чтобы обнажить рекурсию в SQL.

Простой CTE

Использование только предложения WITH (без ключевого слова RECURSIVE) призвано построить Общее Табличной Выражение (CTE). Фактически CTE - это представление, построенное специально для запроса и используемое однократно: всякий раз, когда мы выполняем запрос. В некотором смысле его можно назвать "непостоянным представлением".

Основное назначение CTE - сделать более понятным некоторое выражение, которое неоднократно включается в более сложный запрос. Основной пример:

-- если существует таблица для примера, удалить ее
IF EXISTS (SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = USER
    AND TABLE_NAME = 'T_NEWS')
    DROP TABLE T_NEWS
GO
-- создать таблицу
CREATE TABLE T_NEWS
(NEW_ID INTEGER NOT NULL PRIMARY KEY,
NEW_FORUM VARCHAR(16),
NEW_QUESTION VARCHAR(32))
GO
-- заполнить данными
INSERT INTO T_NEWS VALUES (1, 'SQL', 'What is SQL ?')
INSERT INTO T_NEWS VALUES (2, 'SQL', 'What do we do now ?')
INSERT INTO T_NEWS VALUES (3, 'Microsoft', 'Is SQL 2005 ready for use ?')
INSERT INTO T_NEWS VALUES (4, 'Microsoft', 'Did SQL2000 use RECURSION ?')
INSERT INTO T_NEWS VALUES (5, 'Microsoft', 'Where am I ?')

-- традиционный запрос:
SELECT COUNT(NEW_ID) AS NEW_NBR, NEW_FORUM
FROM T_NEWS
GROUP BY NEW_FORUM
HAVING COUNT(NEW_ID) = ( SELECT MAX(NEW_NBR)
        FROM ( SELECT COUNT(NEW_ID) AS NEW_NBR, NEW_FORUM
         FROM T_NEWS
         GROUP BY NEW_FORUM ) T )
-- Результат :
NEW_NBR     NEW_FORUM
----------- ----------------
3                 Microsoft

Этот запрос один из самых популярных на многих форумах, то есть он вызывает наибольшее число вопросов. Чтобы построить запрос, нам необходимо определить MAX(COUNT (...), что не допускается и, таким образом, должно быть выполнено с помощью подзапросов. Но в вышеупомянутом запросе содержится два абсолютно одинаковых оператора SELECT:

SELECT COUNT(NEW_ID) AS NEW_NBR, NEW_FORUM
FROM T_NEWS
GROUP BY NEW_FORUM

С использованием CTE мы можем теперь сделать запрос более читабельным:

WITH
     Q_COUNT_NEWS (NBR, FORUM)
     AS
     (SELECT COUNT(NEW_ID), NEW_FORUM
     FROM T_NEWS
     GROUP BY NEW_FORUM)
SELECT NBR, FORUM
FROM Q_COUNT_NEWS
WHERE NBR = (SELECT MAX(NBR)
         FROM Q_COUNT_NEWS)

Фактически, мы используем непостоянное представление Q_COUNT_NEWS, вводимое выражением WITH, чтобы написать в более изящной форме решение нашей задачи. Подобно представлению, Вы должны дать имя CTE, а также дать новые имена столбцам, которые содержатся в предложении SELECT в CTE, что не является обязательным.

Отметим, что Вы можете использовать два, три или больше CTE для построения запроса... Давайте рассмотрим еще один пример:

WITH
     Q_COUNT_NEWS (NBR, FORUM)
     AS
     (SELECT COUNT(NEW_ID), NEW_FORUM
     FROM T_NEWS
     GROUP BY NEW_FORUM),
     Q_MAX_COUNT_NEWS (NBR)
     AS (SELECT MAX(NBR)
     FROM Q_COUNT_NEWS)
SELECT T1.*
FROM Q_COUNT_NEWS T1
     INNER JOIN Q_MAX_COUNT_NEWS T2
         ON T1.NBR = T2.NBR

Он дает те же самые результаты, что и два предыдущих варианта! Первый CTE - Q_COUNT_NEWS - используется как таблица во втором, и эти два CTE соединяются в запросе, чтобы дать окончательный результат. Обратите внимание на запятую, которая разделяет два CTE.

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

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

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

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

Контакты

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