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


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

SQL Exercises

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

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

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

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

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

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

§ Добавили интеллекта :-) в окно запроса (пока SELECT).
Теперь можно выполнять простое форматирование с помощью табулятора (CTRL+TAB).
Опционально работает подстановка при вводе (нужно установить флажок "Автозамена"). Просьба протестировать и сказать, чего не хватает при подстановке.

§ Уже шесть человек решили все задачи. На этой неделе dimzv решил последнюю задачу и переместился на 5-ю позицию.

§ Новое лицо в сотне:
marishkin (задач 111, время 7.361)

§ Продвинулись в рейтинге:
ValdemarES (136, 7.873)
Дайнин (136, 111.981)
VIG (133, 15.917)
Botch (задач 133, время 16.710)
Алекс (130, 55.089)
ds (125, 119.521)
SoVa (124, 157.562)
Damirishe (111, 5.644)
Fomichev (111, 9.003)
snikol (110, 256.968)

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

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

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

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

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

Лучшие результаты (ТОР 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 21 Dec 2006
2 Кувалкин К.С. (Cyrilus) 141 141 20 333 746 12.417 2.519 11 10 Dec 2006 22 Dec 2006
3 Карасёва Н.В. (vlksm) 141 58 20 333 196 27.850 2.710 11 18 Nov 2006 22 Dec 2006
4 Мурашкин И.В. (lepton) 141 58 20 333 240 14.865 4.724 11 18 Nov 2006 19 Dec 2006
5 Зверев Д.Л. (dimzv) 141 141 20 333 1141 9.324 4.938 11 19 Dec 2006 22 Dec 2006
6 Голубин Р.С. (Roman S. Golubin) 141 58 20 333 457 54.984 33.803 11 18 Nov 2006 22 Dec 2006
7 Тарасов Д.Б. (Gavrila) 140 140 20 329 466 20.242 .513 7 05 Dec 2006 22 Dec 2006
8 Васьков Е.В. (Johan) 140 58 20 329 124 12.713 11.402 7 20 Nov 2006 16 Dec 2006
9 Войнов П.Е. (pаparome) 139 58 20 326 453 2.689 .000 4 20 Nov 2006 22 Dec 2006
10 Валуев Д.И. (Fiolent) 139 140 20 326 1188 117.017 62.302 4 05 Dec 2006 22 Dec 2006
11 Юлдашев М.Р. (Snowbear) 138 139 20 324 441 4.106 .000 3 02 Oct 2006 21 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 21 Dec 2006
15 Утёнков М.Н. (=Maxim=) 138 58 20 322 200 24.998 .000 0 19 Nov 2006 22 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 18 Dec 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 Петрова О.С. (olishna) 44 44 77 32 109 862 18 Dec 2006
2 Елисеев А.С. (Meteorite) 56 56 105 0 105 932 22 Dec 2006
3 Серебренникова Е.А. (Zhentosik) 53 53 97 0 97 1142 21 Dec 2006
4 Корнилов Д.М. (dk1976) 35 35 53 32 85 1356 21 Dec 2006
5 Елькина (Elechka) 28 57 55 25 80 476 18 Dec 2006
6 Феттер Р. (fetterroman) 25 25 48 32 80 1468 19 Dec 2006
7 >Мриль С.М. (Goog) 46 46 79 1 80 1471 22 Dec 2006
8 Петрова О.С. (olia-ps) 27 55 52 25 77 549 19 Dec 2006
9 Чудинова Л.А. (Любашка) 27 55 52 25 77 550 19 Dec 2006
10 >Кузьмина Е.В. (Fiction) 34 34 60 11 71 1749 22 Dec 2006
11 >Доронин Г.А. (dorg) 39 39 63 3 66 1889 22 Dec 2006
12 SShh K. (Dreamer_007) 37 37 60 0 60 2112 18 Dec 2006
13 >g (ilnoor) 33 34 59 0 59 2134 22 Dec 2006
14 bolotova (Zyaba) 13 26 32 25 57 1417 19 Dec 2006
15 >Grinik E.V. (EugeneGrinik) 29 29 47 8 55 2382 22 Dec 2006
16 Вася (Ушпулек) 13 53 27 25 52 621 19 Dec 2006
17 trespalacios (JeffTres) 31 31 52 0 52 2510 21 Dec 2006
18 Бойцова Т.В. (Боец) 26 55 51 0 51 1029 19 Dec 2006
19 ekho (Ekho) 26 26 41 9 50 2600 19 Dec 2006
20 >Соколов С.А. (delirium) 29 29 50 0 50 2601 22 Dec 2006
21 Левин Г. (Глючный Бобер) 33 33 46 3 49 2679 22 Dec 2006
22 Шолина Н. (needle) 22 57 46 0 46 895 18 Dec 2006
23 >A. S. (15th) 27 27 46 0 46 2822 22 Dec 2006
24 Швецова Е.В. (nereida) 21 53 42 0 42 1157 19 Dec 2006
25 Токмачев И. (Kventin) 21 28 42 0 42 2723 18 Dec 2006
26 Чечетко В.И. (Loklo) 21 28 41 0 41 2678 18 Dec 2006
27 >Соловьева Е.И. (zhenda) 25 25 40 0 40 3108 22 Dec 2006
28 >Копылов А.А. (Kopylov) 28 28 37 3 40 3143 22 Dec 2006

Изучаем SQL

Рекурсивные запросы в SQL:1999 и SQL Server 2005 (продолжение, начало в вып. #117)

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

5 Иерархическое представление

Еще одна важная вещь, связанная со структурированными в форме деревьев данными, - это визуализация данных в виде дерева ..., что означает наличие отступов в иерархии при извлечении данных. Возможно ли это? Да, конечно. Порядок требует знания пути, уровня для размещения пробельных символов и id или timestamp строки в случае наличия строк с одинаковым размещением в дереве (многолистовые данные). Это может быть сделано вычислением пути внутри рекурсии. Вот пример такого запроса:

WITH tree (data, id, level, pathstr)
AS (SELECT VHC_NAME, VHC_ID, 0,
         CAST('' AS VARCHAR(MAX))
     FROM T_VEHICULE
     WHERE VHC_ID_FATHER IS NULL
     UNION ALL
     SELECT VHC_NAME, VHC_ID, t.level + 1, t.pathstr + V.VHC_NAME
     FROM T_VEHICULE V
         INNER JOIN tree t
             ON t.id = V.VHC_ID_FATHER)
SELECT SPACE(level) + data as data, id, level, pathstr
FROM tree
ORDER BY pathstr, id

data id level pathstr
ALL 1 0
    AIR 4 4 AIR
        PLANE 11 2 AIRPLANE
        ROCKET 10 2 AIRROCKET
    EARTH 3 1 EARTH
        CAR 7 2 EARTHCAR
        TRUCK 9 2 EARTHTRUCK
        TWO WHEELES 8 2 EARTHTWO WHEELES
            BYCYCLE 13 3 EARTHTWO WHEELESBYCYCLE
            MOTORCYCLE 12 3 EARTHTWO WHEELESMOTORCYCLE
    SEA 2 1 SEA
        BOAT 6 2 SEABOAT
        SUBMARINE 5 2 SEASUBMARINE

Чтобы сделать это, нам потребовалось использовать новый тип данных в SQL 2005, который называется VARCHAR (MAX), поскольку мы не знаем максимального количества символов, которое потребуется при конкатенации VARCHAR (16) в рекурсивном запросе, который может оказаться очень глубоким. Заметим, что это не очень хорошая идея строить путь с помощью VARCHAR, поскольку это может привести к некоторым граничным эффектам типа конкатенации 'LAND' и 'MARK' на уровне 2 дерева, что может быть ошибочно воспринято как 'LANDMARK' уровня 1 в другой ветви того же самого дерева. Таким образом, Вы должны предусмотреть пробел в конкатенированном пути, чтобы избежать подобных проблем. Это может быть сделано преобразованием типа VHC_NAME к CHAR.

6 Деревья без рекурсии

Однако я должен сказать, что иерархические данные не очень интересны! Почему? Поскольку есть другие способы обработки данных. Помните, я говорил Вам, что математик может сказать, что "Вы можете избежать рекурсии при использовании стека"? Действительно ли это возможно в нашем случае?

Да!
Только поместите стек внутрь таблицы. Как? При помощи двух новых столбцов:

ALTER TABLE T_VEHICULE
ADD RIGHT_BOUND INTEGER
ALTER TABLE T_VEHICULE
ADD LEFT_BOUND INTEGER

Теперь, как фокусник, я заполню эти новые столбцы некоторыми хитрыми числами:

UPDATE T_VEHICULE SET LEFT_BOUND = 1 , RIGHT_BOUND = 26 WHERE VHC_ID = 1
UPDATE T_VEHICULE SET LEFT_BOUND = 2 , RIGHT_BOUND = 7 WHERE VHC_ID = 2
UPDATE T_VEHICULE SET LEFT_BOUND = 8 , RIGHT_BOUND = 19 WHERE VHC_ID = 3
UPDATE T_VEHICULE SET LEFT_BOUND = 20, RIGHT_BOUND = 25 WHERE VHC_ID = 4
UPDATE T_VEHICULE SET LEFT_BOUND = 3 , RIGHT_BOUND = 4 WHERE VHC_ID = 5
UPDATE T_VEHICULE SET LEFT_BOUND = 5 , RIGHT_BOUND = 6 WHERE VHC_ID = 6
UPDATE T_VEHICULE SET LEFT_BOUND = 9 , RIGHT_BOUND = 10 WHERE VHC_ID = 7
UPDATE T_VEHICULE SET LEFT_BOUND = 11, RIGHT_BOUND = 16 WHERE VHC_ID = 8
UPDATE T_VEHICULE SET LEFT_BOUND = 17, RIGHT_BOUND = 18 WHERE VHC_ID = 9
UPDATE T_VEHICULE SET LEFT_BOUND = 21, RIGHT_BOUND = 22 WHERE VHC_ID = 10
UPDATE T_VEHICULE SET LEFT_BOUND = 23, RIGHT_BOUND = 24 WHERE VHC_ID = 11
UPDATE T_VEHICULE SET LEFT_BOUND = 12, RIGHT_BOUND = 13 WHERE VHC_ID = 12
UPDATE T_VEHICULE SET LEFT_BOUND = 14, RIGHT_BOUND = 15 WHERE VHC_ID = 13

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

SELECT *
FROM T_VEHICULE
WHERE RIGHT_BOUND > 12
    AND LEFT_BOUND < 13

VHC_ID VHC_ID_FATHER VHC_NAME RIGHT_BOUND LEFT_BOUND
1 NULL ALL 26 1
3 1 EARTH 19 8
8 3 TWO WHEELES 16 11
12 8 MOTORCYCLE 13 12

Вопрос: так в чем прикол?

Фактически мы реализовали стек, нумеруя слои данных. Вот поясняющая картинка:


Единственная вещь, которую я должен сделать, - это перенумеровать последовательно начиная с 1 справа налево все стеки, составленные каждым элементом данных. Теперь, чтобы получить вышеприведенный запрос, я только беру границы МОТОЦИКЛА (MOTORCYCLE) - слева 12, а справа 13 - и помещаю их в предложение WHERE, выбирая данные, правая граница которых превышает 12, а левая меньше 13.

Кстати говоря, рисунок станет более понятным, если повернуть его на 90 градусов против часовой стрелки.

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

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

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

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

Контакты

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