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

Войнов П.Е.

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

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

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

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

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

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

Замечание

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