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