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

Данная работа является переработкой статьи Ицик Бен-Ган в применении к учебной базе “Аэрофлот”.

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

Рассмотрим следующую задачу: 

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

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

Примечание: для краткости и удобства восприятия, я буду создавать представления, которые будут использоваться по ходу решения задачи. Стоит заметить, что впоследствии легко можно будет “развернуть” получившееся решение в один " 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).