8.2. テーブルビュー作成スクリプト サンプルコード¶
項目
8.2.1. imld_log_optime ビュー¶
create or replace view imld_log_optime as
select
bday,
COALESCE(
round(sum(task1) / 60),
0
) as task1,
COALESCE(
round(sum(task2) / 60),
0
) as task2,
COALESCE(
round(sum(task3) / 60),
0
) as task3,
count(*),
round(
COALESCE(round(sum(task1) / 60), 0) + COALESCE(round(sum(task2) / 60), 0) + COALESCE(round(sum(task3) / 60), 0)
) as total
from
(
select
TO_CHAR(i.execution_time, 'YYYY/MM/DD') as bday,
(
select
(
case
when t.optime - (i2.duration / 1000) < 0 then 0
else t.optime - (i2.duration / 1000)
end
)
from
imld_log i2
where
i.execution_id = i2.execution_id
and i.execution_no = i2.execution_no
and i2.execute_id = 'bizrobo0021'
) as task1,
(
select
(
case
when t.optime - (i2.duration / 1000) < 0 then 0
else t.optime - (i2.duration / 1000)
end
)
from
imld_log i2
where
i.execution_id = i2.execution_id
and i.execution_no = i2.execution_no
and i2.execute_id = 'im_winactorCallAgent1'
) as task2,
(
select
(
case
when t.optime - (i2.duration / 1000) < 0 then 0
else t.optime - (i2.duration / 1000)
end
)
from
imld_log i2
where
i.execution_id = i2.execution_id
and i.execution_no = i2.execution_no
and i2.execute_id = 'uipath0012'
) as task3
from
imld_log i,
imrpa_optime_task t
where
i.execute_id = t.execute_id
and i.execution_time >= current_date - 30
) as A
group by
bday
order by
bday
8.2.2. imld_log_optime_accum ビュー¶
create or replace view imld_log_optime_accum as
select
i.bday,
i.task1,
i.task2,
i.task3,
i.total,
sum(
r.total
) as accum
from
imld_log_optime as i,
imld_log_optime as r
where
i.bday >= r.bday
group by
i.bday,
i.task1,
i.task2,
i.task3,
i.total
order by
i.bday
8.2.3. imld_log_monitoring_task ビュー¶
create or replace view imld_log_monitoring_task as
select
COALESCE(
category_name,
'マスタ登録なし'
) as category_name,
COALESCE(
task_name,
'マスタ登録なし'
) as task_name,
COALESCE(
status,
'稼働なし'
) as status,
COALESCE(
rtype,
'稼働なし'
) as rtype
from
(
select
m.flow_id,
m.execute_id,
m.event_type,
m.duration,
m.error_message,
CASE
WHEN m.event_type = 'END_TASK'
and m.duration > 100000 THEN '遅い'
WHEN m.event_type = 'END_TASK' THEN '正常'
WHEN m.event_type = 'BEGIN_TASK' THEN '実行中'
WHEN m.event_type = 'ERROR_TASK' THEN 'エラー'
ELSE '稼働なし'
END as status,
CASE
WHEN m.task_type = 'im_winactorCallAgent' THEN 'WA'
WHEN m.task_type = 'bizrobo' THEN 'B!'
WHEN m.task_type = 'uipath' THEN 'UP'
ELSE '稼働なし'
END as rtype
from
(
select
i.flow_id,
i.execute_id,
max(i.execution_time) as execution_time
from
imld_log i,
imrpa_monitoring_task mon
where
i.execute_id = mon.execute_id
and i.execution_time >= current_date - 5
group by
i.flow_id,
i.execute_id
) as s,
imld_log as m
where
s.flow_id = m.flow_id
and s.execute_id = m.execute_id
and s.execution_time = m.execution_time
) z
FULL OUTER JOIN
imrpa_monitoring_task mon
on z.execute_id = mon.execute_id
8.2.4. imld_log_op_cnt ビュー¶
create or replace view imld_log_op_cnt as
select
bday,
count(
task1
) as task1,
count(
task2
) as task2,
count(
task3
) as task3,
count(
etask1
) as etask1,
count(
etask2
) as etask2,
count(
etask3
) as etask3
from
(
select
TO_CHAR(i.execution_time, 'YYYY/MM/DD') as bday,
(
select
i2.execution_id
from
imld_log i2
where
i.execution_id = i2.execution_id
and i.execution_no = i2.execution_no
and i2.execute_id = 'bizrobo0021'
and i2.event_type = 'END_TASK'
) as task1,
(
select
i2.execution_id
from
imld_log i2
where
i.execution_id = i2.execution_id
and i.execution_no = i2.execution_no
and i2.execute_id = 'im_winactorCallAgent1'
and i2.event_type = 'END_TASK'
) as task2,
(
select
i2.execution_id
from
imld_log i2
where
i.execution_id = i2.execution_id
and i.execution_no = i2.execution_no
and i2.execute_id = 'uipath0012'
and i2.event_type = 'END_TASK'
) as task3,
(
select
i2.execution_id
from
imld_log i2
where
i.execution_id = i2.execution_id
and i.execution_no = i2.execution_no
and i2.execute_id = 'bizrobo0021'
and i2.event_type = 'ERROR_TASK'
) as etask1,
(
select
i2.execution_id
from
imld_log i2
where
i.execution_id = i2.execution_id
and i.execution_no = i2.execution_no
and i2.execute_id = 'im_winactorCallAgent1'
and i2.event_type = 'ERROR_TASK'
) as etask2,
(
select
i2.execution_id
from
imld_log i2
where
i.execution_id = i2.execution_id
and i.execution_no = i2.execution_no
and i2.execute_id = 'uipath0012'
and i2.event_type = 'ERROR_TASK'
) as etask3
from
imld_log i,
imrpa_monitoring_task as mon
where
i.execute_id = mon.execute_id
and i.execution_time >= current_date - 30
) as A
group by
bday
order by
bday
8.2.5. imld_log_mon_err ビュー¶
create or replace view imld_log_mon_err as
select
TO_CHAR(
i.execution_time,
'YYYY/MM/DD HH:mm:ss'
) as ltime,
mon.category_name,
mon.task_name,
i.error_message
from
imld_log as i,
imrpa_monitoring_task as mon
where
i.execute_id = mon.execute_id
and i.execution_time >= current_date - 30
and i.error_message <> ''