Как получить сумму соседних строк, Общая сумма которых больше X
У меня есть база данных следующего формата, которая включает в себя рабочее состояние машины:
Event_Start,Event_End,Длительность,Статус
23.06.2018 07:00:00 AM,23.06.2018 07:01:00 AM,60,1
23.06.2018 07:01:00 AM,23.06.2018 07:10:00 AM,540,2
23.06.2018 07:10:00 AM,23.06.2018 07:20:00 AM,600,1
23.06.2018 07:20:00 AM,23.06.2018 07:25:00 AM,300,2
23.06.2018 07:25:00 AM,23.06.2018 07:27:00 AM,120,1
23.06.2018 07:27:00 AM,23.06.2018 07:28:00 AM,60,1
23.06.2018 07:28:00 AM,23.06.2018 07:31:00 AM,180,3
23.06.2018 07:31:00 AM,23.06.2018 08:00:00 AM,1740,2
То, что я пытаюсь сделать, - это найти блоки времени, где машина не работает в течение 5 или минут (продолжительность 300 или более). Машина работает только тогда, когда статус установлен на 2. Эти блоки времени могут состоять из одной строки или нескольких соседних строк. Ключ к этому - только суммирование соседних строк, которые не являются статусом 2. Например:
Первая строка в данных не имеет статуса 2 и рассчитана на 60 секунд. Следующая строка-статус 2, поэтому мне нужно прекратить суммирование. Поскольку 60 секунд-это ниже моего порога в 300 секунд, я не хочу сообщать об этом.
3-я строка содержит запись, где машина не находится в состоянии 2. 600 секунд-это больше моего порога в 300 секунд, поэтому мне нужно сообщить об этом. Мне также нужно искать больше соседних строк, но так как 4-я строка имеет статус 2, мне нужно прекратить суммирование и просто сообщить о 600 секундах.
5-я строка содержит запись, где машина не находится в состоянии 2. Эти 120 секунд находятся ниже моего 300-секундного порога, но мне все еще нужно искать больше соседних строк. 6-я строка также не является статусом 2, поэтому я могу добавить 60 секунд к 120 секундам. 7-я строка также не является статусом 2, поэтому я могу добавить 180 секунд, чтобы добавить это к другим 2 записям. Наконец, 8-я строка-это статус 2, поэтому мне нужно прекратить суммирование. Если я суммирую 3 строки (60 + 120 + 180) = 360. Это больше, чем мой 300-секундный порог, поэтому мне нужно сообщить об этом.
Когда это будет сделано, я должен иметь 2 строки, сообщенные в моих результатах. 2-я строка моих результатов должна включать время начала 5-й строки и время окончания 7-й строки. Наконец, продолжительность должна включать в себя сумму секунд от 7:25 до 7:31.
Event_Start,Event_End,Длительность
23.06.2018 07:10:00 AM,23.06.2018 07:20:00 AM,600
23.06.2018 07:25:00 AM,23.06.2018 07:31:00 AM,360
Что я уже пробовал:
Я начал со следующего SQL, но он дает только отдельные строки, превышающие 300 секунд. Я немного читал об использовании команды LEAD для просмотра следующей строки, но я не знаю, как я могу сделать это, когда она будет продолжать суммироваться до следующей строки, которая не является статусом 2.
select event_start, event_end, duration from mazak_oee_log where event_start >= '06/23/2018' and event_end <= '06/24/2018' and status <> 2 and duration >= 300