Задача: записать в таблицу event_day общее время недоступности системы в формате: дата, время недоступности системы (в секундах).
Два элемента подключены параллельно и два последовательно. Система считается недоступной если есть аварии по элементу 1001 или 1002 или (1003 и 1004).
При отказе отдельного элемента в таблицу event записывается порядковый номер события, номер элемента по которому произошел отказ, время отказа. После починки строка обновляется и в поле end_date записывается дата/время возобновления работы.
Система не может быть "дважды недоступной" в один момент времени. Т.е. если в состоянии аварии больше чем один элемент, авария одна.
По одному элементу в один момент времени в таблице event_day может быть зафиксировано несколько аварий. Например, пользователь_1 видит что система не работает и создает аварию, в тоже время пользователь_2 создает свою аварию и т.д. Авария элемента считается с времени открытия самого первого по время закрытия последнего.
Система поддерживается 5 дней в неделю и только в "бизнес время" (для примера с 8-00 до 18-00). Т.е. если авария случается вне этого диапазона она не должна учитываться.
Таблица источник event:
| id | element_id | start_date | end_date |
| 1 | 1001 | 01.09.2011 5:00:00 | 01.09.2011 10:00:00 |
| 2 | 1001 | 01.09.2011 9:00:00 | 01.09.2011 11:00:00 |
| 3 | 1003 | 01.09.2011 12:00:00 | 01.09.2011 14:30:00 |
| 4 | 1003 | 01.09.2011 12:30:00 | 01.09.2011 16:00:00 |
| 5 | 1004 | 01.09.2011 13:00:00 | 01.09.2011 15:30:00 |
| 6 | 1002 | 01.09.2011 13:15:00 | 01.09.2011 13:30:00 |
| 7 | 1004 | 01.09.2011 16:30:00 | 01.09.2011 17:00:00 |
| 8 | 1002 | 01.09.2011 17:30:00 | 01.09.2011 19:00:00 |
| 9 | 1001 | 01.09.2011 17:30:00 |
Авария с id = 9 на момент расчета считается незакрытой.
Данные из таблицы event в виде схемы:
Нужно посчитать в секундах интервалы Т1 - Т4; Т7 - Т11 и Т15 - Т17.
Аварию элемента 1001 до наступления бизнес времени (точка Т1) учитывать не надо. В интервале Т2 - Т3 одновременно открыты 2 аварии, но считать нужно что система не доступна по одной иначе время задвоится.
В момент времени Т5 зафиксирована авария элемента 1003, но т.к. элементы 1003 и 1004 включены параллельно система остается в рабочем состоянии.
В Т6 авария по 1003 зафиксирована повторно. Система остается в рабочем состоянии.
В Т7 авария элемента 1004. Т.к. ранее открыта авария по 1003. С этого момента система считается недоступной.
В Т8 еще авария по элемента 1002. Которую считать не нужно т.к. система и так не доступна и ее учет задвоит время.
В Т9 авария элемента Т2 закрыта, но система остается недоступной из-за аварий элементов 1003 и 1004.
В Т10 закрывается одна из аварий элемента 1003. Например, администратор увидел что одна их несколько и закрыл дублирующую.
В Т11 закрывается авария по элементу 1004 система становится доступной. Хотя авария по 1003 еще не закрыта.
В Т12 закрывается авария по элементу 1003.
Т13-Т14 авария элемента 1004. Но т.к.элемент 1003 работает. Система доступна.
Т15-Т17 аварии по элементам 1001 и 1002. Нужно считать только одну что бы не задвоить время недоступности.
В точке Т17 заканчивается бизнес время. Дальше недоступность системы не учитывается.
На момент расчета, одна из аварий остается незакрытой (поле end_date таблицы event остается пустым).
DECLARE
v_date date := to_date('01.09.2011', 'dd.mm.yyyy'); -- день за который производится расчет
v_start number := 8; -- начало бизнес времени (с часа)
v_end number := 18; -- окончание бизнес времени (до часа)
v_day number := 5; -- сколько дней недели считать
v_1001 number :=0;
v_1002 number :=0;
v_1003 number :=0;
v_1004 number :=0;
v_alarm varchar(1);
cursor r_1 is
select
id
,element_id
,type
,dt start_date
,nvl(lead(DT) over(order by DT ), trunc(v_date) + 18/24) finish_date
from
(
select id, element_id, 'start_date' type,
case
when START_DATE < trunc(v_date) + v_start/24 then trunc(v_date) + v_start/24
when START_DATE > trunc(v_date) + v_end/24 then trunc(v_date) + v_end/24
else START_DATE end dt
from event
where START_DATE < trunc(v_date) + v_end/24
and nvl(FINISH_DATE, sysdate) >= trunc(v_date) + v_start/24
union all
select id, element_id, 'finish_date' type,
case
when nvl(FINISH_DATE, sysdate) < trunc(v_date) + v_start/24 then trunc(v_date) + v_start/24
when nvl(FINISH_DATE, sysdate) > trunc(v_date) + v_end/24 then trunc(v_date) + v_end/24
else FINISH_DATE end dt
from event
where START_DATE < trunc(v_date) + v_end/24
and nvl(FINISH_DATE, sysdate) >= trunc(v_date) + v_start/24
);
--Объявлены переменные. В курсоре выбраны все аварии которые были в этот день.
--Если время начала/окончания вне бизнес времени - приведены к нему.
--Все даты сгруппированы в один столбец с указанием номера аварии, элемента, признака что это дата начала или окончания аварии.
--При помощи функции LEAD весь день разбит на интервалы.
--Начало каждого интервала это открытие или закрытие аварий выстроенные в хронологическом порядке.
--Окончание интервала - время следующего события или окончание бизнес времени (если событие последнее в рассматриваемом дне).
BEGIN
IF to_char(v_date, 'D') <= v_day -- проверка что день с по пятницу
THEN
FOR t IN r_1
LOOP
if t.type = 'start_date' and t.element_id = 1001 then v_1001 := v_1001 +1;
elsif t.type = 'finish_date' and t.element_id = 1001 then v_1001 := v_1001 -1;
elsif t.type = 'start_date' and t.element_id = 1002 then v_1002 := v_1002 +1;
elsif t.type = 'finish_date' and t.element_id = 1002 then v_1002 := v_1002 -1;
elsif t.type = 'start_date' and t.element_id = 1003 then v_1002 := v_1003 +1;
elsif t.type = 'finish_date' and t.element_id = 1003 then v_1002 := v_1003 -1;
elsif t.type = 'start_date' and t.element_id = 1004 then v_1004 := v_1004 +1;
elsif t.type = 'finish_date' and t.element_id = 1004 then v_1004 := v_1004 -1;
end if;
if v_1003 * v_1004 > 0 then v_alarm := 'Y';
elsif v_1001 > 0 then v_alarm := 'Y';
elsif v_1002 > 0 then v_alarm := 'Y';
else v_alarm := 'N';
end if;
-- используется временная таблица с которой данные будут вставлятся в итоговую
insert into tmp_event te (TE.ID, TE.CI_ID, TE.TYPE, TE.START_DATE, TE.FINISH_DATE, TE.ALARM, TE.SEC)
values (t.id, t.element_id, t.type, t.start_date, t.finish_date, v_alarm,
(t.finish_date - t.start_date) * 86400 );
commit;
END LOOP;
END IF;
insert into event_day ed (ed.dt, ed.sec)
select v_date, nvl(sum(te.sec),0) from tmp_event te
where te.alarm = 'Y';
delete tmp_event;
commit;
END;Проверка что рассматриваемый день не выходной.Смотрим каждый интервал. Если в нем событие начинается соответствующую переменную увеличиваем на 1, если закрывается - уменьшаем. Таким образом определяем какие аварии в каком интервале значимы.
Для каждого интервала определяем была ли в нем авария. Если произведение переменных v_1003 и v_1004 больше нуля, то по обеим элементам были аварии, а если = 0 - проверяем переменные v_1001 и v_1002. соответствующие признаки записываем во временную таблицу. Также записываем длительность интервалов (в секундах)
Находим суммарную продолжительность всех интервалов где были аварии из временной таблицы в итоговую.
Удаляем все данные из временной таблицы.
Общее время недоступности составило 21600 секунд. Или 6 часов.
Интервал Т1-Т4 с 8-00 по 11-00 это 3 часа;
Интервал Т7-Т11 с 13-00 по 15-30 это 2,5 часа;
Интервал Т15-Т17 с 17-30 по 18-00 это 0,5 часа;
Итого 6 часов.