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


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


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

Автор
Serge I

Статистика

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

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


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

http://www.sql-ex.ru

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

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

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

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

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

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

§ Сдана в типографию книга, посвященная анализу упражнений первого этапа на сайте. Название уже другое, надеюсь, что содержание не изменилось :-). Уже можно сделать заказ: SQL. Задачи и решения.

§ Как и прогнозировалось, burakov58 занял третью позицию в рейтинге (задач 138, время 17.395). Изменить тройку лидеров теперь сможет только человек, решивший 138 задачу. Судя по частоте посещений сайта, 138-ю задачу начал решать dimzv. Так что у нас может смениться лидер.

§ В сотне появились новые участники:
lepton (95, .937)
ds (95, 9.755)

§ Сохранил шансы на попадание в десятку:
Testo (115, 2.568)
OlegE (112, 3.006)

§ Продолжили свое восхождение к вершине:
Phohack (133, 92.795)
xax (123, 77.275)
Eugeniе (107, 29.545)
gps (104, 138.860)
arm (100, 13.913)
Gosha (96, 8.029)
GoshaS_29 (96, 15.653)

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

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

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

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

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

No Person Number of
Sel_ex		 Last_Sel Number of
DML_ex		 Scores Days Days_2 LastSolved LastVisit
1 Кувалкин К.С. (Cyrilus) 138 138 20 320 484 5.460 23 Mar 2006 31 Mar 2006
2 Kamaev V.M. (Heromantor) 138 138 20 320 128 9.066 14 Mar 2006 25 Mar 2006
3 Бураков С.Г. (burakov58) 138 138 20 320 419 17.395 24 Mar 2006 25 Mar 2006
4 Тарасов Д.Б. (Gavrila) 138 138 20 320 208 19.424 22 Mar 2006 31 Mar 2006
5 Голубин Р.С. (Roman S. Golubin) 138 138 20 320 210 20.835 16 Mar 2006 31 Mar 2006
6 Валуев Д.И. (Fiolent) 137 138 20 318 925 47.930 17 Mar 2006 31 Mar 2006
7 Войнов П.Е. (pаparome) 137 137 20 316 203 1.769 15 Mar 2006 31 Mar 2006
8 Зверев Д.Л. (dimzv) 137 137 20 316 869 3.278 22 Mar 2006 31 Mar 2006
9 Юлдашев М.Р. (Snowbear) 137 137 20 316 239 3.448 14 Mar 2006 31 Mar 2006
10 Абашин П.И. (Dizil) 137 137 20 316 203 3.776 15 Mar 2006 31 Mar 2006
11 Самохвалов В. (ValdemarES) 137 137 20 316 117 7.788 14 Mar 2006 28 Mar 2006
12 Крижевич С.А. (yaff) 137 137 20 316 259 14.801 16 Mar 2006 22 Mar 2006
13 Иванов А.Н. (Goapsy) 137 137 20 316 128 16.076 18 Mar 2006 25 Mar 2006
14 Зырин В.Е. (Vezyr) 137 137 20 316 63 20.618 17 Mar 2006 28 Mar 2006
15 Страшников А.С. (EffEct) 137 137 20 316 305 59.972 16 Mar 2006 31 Mar 2006
16 Матвеева Ю.Б. (Julia_M) 137 137 20 316 137 68.931 24 Mar 2006 30 Mar 2006
17 Духин А. (Shark) 135 137 20 311 249 2.785 17 Mar 2006 21 Mar 2006
18 Носков Н.В. (niko2) 134 137 20 309 260 8.265 23 Mar 2006 23 Mar 2006
19 Леденев С.А. (Shurgenz) 134 137 20 309 573 11.574 14 Mar 2006 22 Mar 2006
20 Держальцев В.А. (MadVet) 134 137 20 309 334 18.469 16 Mar 2006 16 Mar 2006

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 solodovnikov V. (kapuchinoff) 65 65 119 32 151 206 30 Mar 2006
2 >Petrova G.V. (Galina V) 59 59 108 19 127 384 31 Mar 2006
3 Мурашкин (lepton) 45 95 97 15 112 95 31 Mar 2006
4 >Maskovich (Strider5) 43 43 71 32 103 620 31 Mar 2006
5 Гриценко Ю.А. (Pal) 28 54 59 23 82 434 30 Mar 2006
6 Корниенко А. (1917) 41 41 71 0 71 1121 27 Mar 2006
7 >Patel R.J. (Rups) 39 39 65 3 68 1174 31 Mar 2006
8 >Diep (pd206) 39 39 68 0 68 1175 31 Mar 2006
9 Yakimenko V. (janush) 15 71 32 31 63 178 31 Mar 2006
10 >Chumakov D.V. (Dennis) 31 47 63 0 63 923 31 Mar 2006
11 >Усачев (maar) 25 40 54 7 61 989 31 Mar 2006
12 >Шишикина (Inna) 24 44 47 9 56 932 31 Mar 2006
13 Ларченко Р.И. (дба) 30 30 55 0 55 1507 29 Mar 2006
14 Ng J. (Jackey) 28 28 49 0 49 1676 31 Mar 2006
15 >Slobodcicov A.N. (Testo) 18 115 47 0 47 50 31 Mar 2006
16 Manukyan A.S. (Arsen1987) 25 25 42 5 47 1744 31 Mar 2006
17 >Ivanov (vanio) 26 56 46 0 46 688 31 Mar 2006
18 >Kim S. (kimberly) 27 27 46 0 46 1769 31 Mar 2006
19 >Kolotnina A. (Virna) 25 25 40 0 40 1937 31 Mar 2006
20 Dorge C. (Coennemans) 24 24 39 0 39 1995 30 Mar 2006
21 Урусов А.С. (Xardas) 22 50 37 0 37 871 28 Mar 2006
22 kolanych K.K. (Kolanych) 24 24 37 0 37 2038 29 Mar 2006
23 >Волков В. (DizeL) 23 23 34 3 37 2090 31 Mar 2006
24 >Ильин А.С. (Sasha_Century) 17 41 36 0 36 1198 31 Mar 2006
25 Саканцева Н.С. (nsnt) 14 30 36 0 36 1510 29 Mar 2006
26 >krishnakumar (hemskk) 23 23 36 0 36 2169 31 Mar 2006
27 >wwer (qwerty) 15 15 17 17 34 2230 31 Mar 2006
28 Kornelis H. (Hugo Kornelis) 20 20 27 7 34 2231 30 Mar 2006
29 >Островский Е. (ЕО) 14 78 33 0 33 156 31 Mar 2006
30 Галкина Н. (natalya) 14 39 31 0 31 1126 30 Mar 2006
31 >Hakikat T. (TLV) 19 22 28 2 30 2361 31 Mar 2006

Изучаем SQL

Группировка отрезков времени

Войнов П.Е.

Данная работа является переработкой статьи Ицик Бен-Ган, расположенной по адресу http://www.osp.ru/win2000/sql/master/25mstr09.htm, в применении к учебной базе "Аэрофлот".

Здесь будет применен только один из вариантов решения, рассмотренных в вышеуказанной статье.

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

Для работы нам понадобится следующая информация: идентификатор компании, дата-время взлета и посадки самолета.

Примечание: для краткости и удобства восприятия, я буду создавать представления, которые будут использоваться по ходу решения задачи. Стоит заметить, что впоследствии легко можно будет "развернуть" получившееся решение в один "select".
Итак.

Представление vw_trip

CREATE VIEW dbo.vw_trip
as
SELECT DISTINCT id_comp,
    dt_out = date + time_out,
    dt_in = date + time_in + CASE WHEN time_out > time_in THEN 1 ELSE 0 END
FROM trip t JOIN
    pass_in_trip pt ON t.trip_no = pt.trip_no

Основная трудность решения заключается в том, что нам не известно, сколько рейсов могут организовывать непрерывный интервал времени, в течение которого в воздухе находился хотя бы один самолет компании (в дальнейшем "интервал").

Для нахождения начала "интервала" воспользуемся таким фактом: начало "интервала" совпадает со стартом самолета, если в этот момент ни какой другой самолет компании не находится в воздухе. Или, другими словами, начало "интервала" совпадает со стартом самолета, если не существует других рейсов компании, которые уже взлетели, но еще не приземлились.

Представление vw_dt_start

CREATE VIEW dbo.vw_dt_start
AS
SELECT DISTINCT id_comp, dt_out
FROM vw_trip t
WHERE NOT EXISTS(SELECT 1
        FROM vw_trip
        WHERE id_comp = t.id_comp AND
                dt_out < t.dt_out AND
                dt_in > = t.dt_out
                )

Пояснения:
dt_out < t.dt_out - строгое неравенство, дабы в проверку не попал сам рассматриваемый рейс;
dt_in > = t.dt_out - нестрогое неравенство; проверяем также ситуацию, когда один самолет взлетает, а другой в это время садится ("интервал" не разрывается);
DISTINCT - убираем дублирование записей на случай одновременного взлета двух или более самолетов.

Время окончания "интервала" находится аналогичным образом.

Представление vw_dt_end

CREATE VIEW dbo.vw_dt_end
AS
SELECT DISTINCT id_comp, dt_in
FROM vw_trip t
WHERE NOT EXISTS(SELECT 1
        FROM vw_trip
        WHERE id_comp = t.id_comp AND
                dt_out <= t.dt_in AND
                dt_in > t.dt_in
                )

Теперь нам осталось соединить времена начала и окончания "интервалов". Так как интервалы не пересекаются, то можно утверждать, что время окончания "интервала" - это минимальное время из всех vw_dt_end.dt_in, превосходящих рассматриваемое время начала "интервала".

Представление vw_result

CREATE VIEW dbo.vw_result
AS
SELECT name_comp = (SELECT [name]
                FROM company
                WHERE id_comp = vw_dt_start.id_comp
                ), vw_dt_start.dt_out, dt_in = MIN(vw_dt_end.dt_in)
FROM vw_dt_start JOIN
        vw_dt_end on vw_dt_start.id_comp = vw_dt_end.id_comp AND
                vw_dt_start.dt_out < vw_dt_end.dt_in
GROUP BY vw_dt_start.id_comp, vw_dt_start.dt_out

Читателю предлагается самостоятельно решить эту задачу без использования представлений (т.е. построить решение в виде единственного оператора SELECT).

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

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

Контакты

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