Как просуммировать время простоя?

Question

[Сергей]

Есть у меня таблица с полями:
Id - уникальный идентификатор с автоинкрементом
Interface - номер интерфейса
Status - состояние интерфейса. Поле типа "Enum", значения только 1 или 2.
Date - дата
Time -время события
Сначала происходит событие 2, т.е. интерфейс упал. Через какое то время статус становится 1 - интерфейс поднялся. Таких событий за день или даже за час может быть несколько. Мне нужно выбрать номера интерфейсов и по каждому посчитать суммарное время простоя. Т.е. сначала выбрать пары, где событие стало 1, вычесть время события, произошедшего ранее (со статусом 2), вычислить разницу и просуммировать эти дельты с выборкой по каждому каналу в отдельности.

Answer 1

[d-stream]

Да... если еще нет гарантии "непропуска" события - то совсем беда-беда...

Как один из вариантов - добавить колонку-хэлпер в виде "расстояния" (например в секундах) до предшествующего - тут придется чуть ли не циклом заполнять старое, ну а новое - видимо будет удобно триггером.

Ну а потом - для каждого включения (status=2) суммируем те самые "расстояния"

Comments

[Сергей]

Мне не нужен триггер. Мне нужно задачу решить разово прямо сейчас, потом база будет переделана

[d-stream]

sergey_privacy: значит делать часть до триггера и потом выводить, а триггер не делать (подразумевая, что в процессе данные не будут приходить)

Answer 2

[Draconian]

Нужно иметь две выборки - статусы запусков и статусы остановок по одному из интерфейсов.
Если мы отсечем первый запуск, количество пусков-остановок у нас будет одинаковое (если интерфейс в данный момент работает), при правильной сортировке можно две эти выборки соединить друг с другом по порядковому номеру строки.
MySql под рукой нет, а в Oracle удаление первой строки довольно довольно страшно выглядит (писал на коленке):

with q as (
    select 1 as id, 1 as interface, 1 as status, to_date('01.01.2017','dd.mm.yyyy') as dd, to_date('01.01.2017 00:00','dd.mm.yyyy HH24:MI:SS') as time from dual
        UNION ALL
    select 2 as id, 1 as interface, 2 as status, to_date('01.01.2017','dd.mm.yyyy') as dd, to_date('01.01.2017 18:20','dd.mm.yyyy HH24:MI:SS') as time from dual
        UNION ALL
    select 3 as id, 1 as interface, 1 as status, to_date('01.01.2017','dd.mm.yyyy') as dd, to_date('01.01.2017 18:30','dd.mm.yyyy HH24:MI:SS') as time from dual
        UNION ALL
    select 4 as id, 1 as interface, 2 as status, to_date('01.01.2017','dd.mm.yyyy') as dd, to_date('01.01.2017 19:20','dd.mm.yyyy HH24:MI:SS') as time from dual
        UNION ALL
    select 5 as id, 1 as interface, 1 as status, to_date('01.01.2017','dd.mm.yyyy') as dd, to_date('01.01.2017 21:00','dd.mm.yyyy HH24:MI:SS') as time from dual
)

SELECT SUM(ROUND ( (end_date - begin_date) * 24, 2)) as hours
FROM (
        SELECT 
               q2.id,
               q2.status,
               q2.time begin_date,
               q.time end_date
        FROM (SELECT ROWNUM r, q.*
                     FROM (SELECT ROWNUM rr, q.*
                               FROM q
                               WHERE q.interface = 1 AND q.status = 1
                               ORDER BY time ASC) q
                    WHERE rr > 1) q
            JOIN
                ( SELECT ROWNUM rr, q.*
                   FROM q
                   WHERE q.interface = 1 AND q.status = 2
                   ORDER BY time ASC ) q2 ON q.dd = q2.dd AND q.interface = q2.interface AND q.r = q2.rr
)

На этих тестовых данных всё верно.