Книги и статьи по SQL Rambler's Top100 Switch language to: English 22 июля 2019 г. 17:04:18


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

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

Войнов П.Е.

Данная работа является переработкой статьи Ицик Бен-Ган , расположенной по адресу 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).

На главную страницу

Print  Версия для печати


Использование любых материалов данного сайта возможно только
при условии обязательного размещения прямой ссылки на сайт
http://www.sqlbooks.ru
на каждой странице, где размещены используемые материалы.

 Начало   Статьи    Книги 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.