📄 collect.sql
字号:
t9.atotal_n_abandoned_20s,
t9.n_answered_timeout,
t9.noc_answered_tot
from (
select time_key,object_name, sum(itotal_n_entered) as itotal_n_entered
from(
select time_key,
sum(n_entered) as itotal_n_entered,
object_id
from R_ROUTE_POIN_day
where object_id in ( select object_id
from O_ROUTE_POIN_day
where substr(object_name,1,5)='60090')
and time_key >= begindate and time_key <= enddate
group by time_key,object_id ) as t7
left join
(
select object_id,case when substr(object_name,1,5) ='60090' then '10105558' else '95558' end as object_name
from O_ROUTE_POIN_day
where substr(object_name,1,5)= '60090'
) as t12
on t7.object_id =t12.object_id
group by time_key,object_name
) as t8
left join
(
select time_key,
sum(n_entered) as atotal_n_entered,
sum(n_answered) as atotal_n_answered,
sum(t_answered) as atotal_t_answered,
sum(n_abandoned) as atotal_n_abandoned,
sum(t_abandoned) as atotal_t_abandoned,
sum(abandoned_in_5s) as atotal_n_abandoned_5s,
sum(t_abandoned_5s) as atotal_t_abandoned_5s,
sum(answered_in_20s) as atotal_n_answered_20s,
sum(abandoned_in_20s) as atotal_n_abandoned_20s,
sum(n_answered_timeout) as n_answered_timeout,
sum(noc_answered_tot) as noc_answered_tot
from R_RP_BK_1001_day
where object_id in ( select object_id
from O_RP_BK_1001_day
where substr(object_name,1,5) in( '62001' ))
group by time_key
) as t9
on t8.time_key=t9.time_key
) as t10
left join
(
select time_key,
sum(n_enteredn) as n_entered_62006,
sum(n_abandoned) as n_abandoned_62006,
sum(t_abandoned) as t_abandoned_62006,
sum(n_distributed) as n_distributed_62006,
sum(t_distributed) as t_distributed_62006
from R_QU_BK_1002_day
where object_id in ( select object_id
from O_QU_BK_1002_day
where substr(object_name,1,5) in( '62006' ))
group by time_key
) as t11
on t10.time_key=t11.time_key
order by 1;
end p2;
P3: BEGIN
declare cur95 cursor with hold for select * from SESSION.TB_RPT_MID_11;
declare cur10 cursor with hold for select * from SESSION.TB_RPT_MID_02;
declare continue handler for not found
begin
set v_notfound = 1;
end;
OPEN cur10;
OPEN cur95;
fetch cur10 into
time_key10 ,
object_name10 ,
n_entered10 ,
an_entered10 ,
n_answered10 ,
t_answered10 ,
n_abandoned10 ,
t_abandoned10 ,
n_abandoned_5s10 ,
t_abandoned_5s10 ,
n_answered_20s10 ,
n_abandoned_20s10 ,
n_answered_timeout10 ,
noc_answered_tot10 ,
n_entered_62006_10 ,
n_abandoned_62006_10 ,
t_abandoned_62006_10 ,
n_distributed_62006_10,
t_distributed_62006_10 ;
fetch cur95 into
time_key95 ,
object_name95 ,
n_entered95 ,
an_entered95 ,
n_answered95 ,
t_answered95 ,
n_abandoned95 ,
t_abandoned95 ,
n_abandoned_5s95 ,
t_abandoned_5s95 ,
n_answered_20s95 ,
n_abandoned_20s95 ,
n_answered_timeout95 ,
noc_answered_tot95 ,
n_entered_62006_95 ,
n_abandoned_62006_95 ,
t_abandoned_62006_95 ,
n_distributed_62006_95 ,
t_distributed_62006_95 ;
set v_notfound=0;
while v_notfound=0 Do
if time_key95=time_key10 then
insert into SESSION.TB_RPT_MID_01 values
(
time_key95 ,
object_name95 ,
n_entered95 ,
an_entered95 -an_entered10 ,
n_answered95 -n_answered10 ,
t_answered95 -t_answered10 ,
n_abandoned95-n_abandoned10 ,
t_abandoned95-t_answered10 ,
n_abandoned_5s95-n_abandoned_5s10 ,
t_abandoned_5s95-t_abandoned_5s10 ,
n_answered_20s95-n_answered_20s10,
n_abandoned_20s95 -n_abandoned_20s10 ,
n_answered_timeout95-n_answered_timeout10 ,
noc_answered_tot95 -noc_answered_tot10 ,
n_entered_62006_95 -n_entered_62006_10 ,
n_abandoned_62006_95-n_abandoned_62006_10 ,
t_abandoned_62006_95-t_abandoned_62006_10 ,
n_distributed_62006_95-n_distributed_62006_10 ,
t_distributed_62006_95-t_distributed_62006_10
);
end if;
fetch cur10 into
time_key10 ,
object_name10 ,
n_entered10 ,
an_entered10 ,
n_answered10 ,
t_answered10 ,
n_abandoned10 ,
t_abandoned10 ,
n_abandoned_5s10 ,
t_abandoned_5s10 ,
n_answered_20s10 ,
n_abandoned_20s10 ,
n_answered_timeout10 ,
noc_answered_tot10 ,
n_entered_62006_10 ,
n_abandoned_62006_10 ,
t_abandoned_62006_10 ,
n_distributed_62006_10,
t_distributed_62006_10;
fetch cur95 into
time_key95 ,
object_name95 ,
n_entered95 ,
an_entered95 ,
n_answered95 ,
t_answered95 ,
n_abandoned95 ,
t_abandoned95 ,
n_abandoned_5s95 ,
t_abandoned_5s95 ,
n_answered_20s95 ,
n_abandoned_20s95 ,
n_answered_timeout95 ,
noc_answered_tot95 ,
n_entered_62006_95 ,
n_abandoned_62006_95 ,
t_abandoned_62006_95 ,
n_distributed_62006_95 ,
t_distributed_62006_95 ;
end while;
close cur10;
close cur95;
--delete from SESSION.TB_RPT_MID_01;
end p3;
p5:begin
DECLARE GLOBAL TEMPORARY TABLE SESSION.TB_RPT_MID_RS
(
time_key varchar(30),
object_name varchar(8),
result3 integer,
result4 integer,
result5 integer,
result6 integer,
result7 float,
result8 float,
result9 integer,
result10 float,
result11 float,
result12 float,
result13 integer
)
NOT LOGGED
WITH REPLACE
;
insert into SESSION.TB_RPT_MID_RS
select * from
(
select COALESCE(substr(time_key,1,4)||'-'||substr(time_key,5,2)||'-'||substr(time_key,7,2),'总计'),
max(object_name),
sum(itotal_n_entered),
sum(atotal_n_entered) - sum(atotal_n_abandoned_5s),
sum(atotal_n_abandoned) + sum(n_abandoned_62006) - sum(atotal_n_abandoned_5s),
sum(atotal_n_abandoned_5s),
round(case when sum(atotal_n_entered - atotal_n_abandoned_5s)=0 then 0 else real(sum(atotal_n_abandoned + n_abandoned_62006 - atotal_n_abandoned_5s))/sum(atotal_n_entered - atotal_n_abandoned_5s) end , 4),
round(case when sum(atotal_n_abandoned + n_abandoned_62006 - atotal_n_abandoned_5s)=0 then 0 else real(sum(atotal_t_abandoned+(n_entered_62006-noc_answered_tot)*60+t_abandoned_62006+ (case when n_distributed_62006=0 then 0 else (n_distributed_62006-noc_answered_tot)*t_distributed_62006/n_distributed_62006 end) ))/sum(atotal_n_abandoned + n_abandoned_62006 - atotal_n_abandoned_5s) end , 2),
sum(atotal_n_entered) - (sum(atotal_n_abandoned) + sum(n_abandoned_62006)),
round(case when sum(atotal_n_answered)=0 then 0 else real(sum(atotal_t_answered+noc_answered_tot*60+ (case when n_distributed_62006=0 then 0 else noc_answered_tot*t_distributed_62006/n_distributed_62006 end) ))/sum(atotal_n_answered) end , 2),
round(case when sum(atotal_n_entered - atotal_n_abandoned_5s)=0 then 0 else real(sum(atotal_n_entered - atotal_n_abandoned - n_abandoned_62006))/sum(atotal_n_entered - atotal_n_abandoned_5s) end , 4),
round(case when sum(atotal_n_entered - atotal_n_abandoned_5s)=0 then 0 else real(sum(atotal_n_answered_20s + atotal_n_abandoned_20s - atotal_n_abandoned_5s))/sum(atotal_n_entered - atotal_n_abandoned_5s) end , 4),
sum(n_entered_62006)
from SESSION.TB_RPT_MID_01
where time_key >= begindate
and time_key <= enddate
and (char(dayofweek(cast(substr(time_key,1,4)||'-'||substr(time_key,5,2)||'-'||substr(time_key,7,2) as date)))=week or week = 'ALL')
group by substr(time_key,1,4)||'-'||substr(time_key,5,2)||'-'||substr(time_key,7,2)
with cube
union
select COALESCE(substr(time_key,1,4)||'-'||substr(time_key,5,2)||'-'||substr(time_key,7,2),'总计'),
max(object_name),
sum(itotal_n_entered),
sum(atotal_n_entered) - sum(atotal_n_abandoned_5s),
sum(atotal_n_abandoned) + sum(n_abandoned_62006) - sum(atotal_n_abandoned_5s),
sum(atotal_n_abandoned_5s),
round(case when sum(atotal_n_entered - atotal_n_abandoned_5s)=0 then 0 else real(sum(atotal_n_abandoned + n_abandoned_62006 - atotal_n_abandoned_5s))/sum(atotal_n_entered - atotal_n_abandoned_5s) end , 4),
round(case when sum(atotal_n_abandoned + n_abandoned_62006 - atotal_n_abandoned_5s)=0 then 0 else real(sum(atotal_t_abandoned+(n_entered_62006-noc_answered_tot)*60+t_abandoned_62006+ (case when n_distributed_62006=0 then 0 else (n_distributed_62006-noc_answered_tot)*t_distributed_62006/n_distributed_62006 end) ))/sum(atotal_n_abandoned + n_abandoned_62006 - atotal_n_abandoned_5s) end , 2),
sum(atotal_n_entered) - (sum(atotal_n_abandoned) + sum(n_abandoned_62006)),
round(case when sum(atotal_n_answered)=0 then 0 else real(sum(atotal_t_answered+noc_answered_tot*60+ (case when n_distributed_62006=0 then 0 else noc_answered_tot*t_distributed_62006/n_distributed_62006 end) ))/sum(atotal_n_answered) end , 2),
round(case when sum(atotal_n_entered - atotal_n_abandoned_5s)=0 then 0 else real(sum(atotal_n_entered - atotal_n_abandoned - n_abandoned_62006))/sum(atotal_n_entered - atotal_n_abandoned_5s) end , 4),
round(case when sum(atotal_n_entered - atotal_n_abandoned_5s)=0 then 0 else real(sum(atotal_n_answered_20s + atotal_n_abandoned_20s - atotal_n_abandoned_5s))/sum(atotal_n_entered - atotal_n_abandoned_5s) end , 4),
sum(n_entered_62006)
from SESSION.TB_RPT_MID_02
where time_key >= begindate
and time_key <= enddate
and (char(dayofweek(cast(substr(time_key,1,4)||'-'||substr(time_key,5,2)||'-'||substr(time_key,7,2) as date)))=week or week = 'ALL')
group by substr(time_key,1,4)||'-'||substr(time_key,5,2)||'-'||substr(time_key,7,2)
with cube
order by 1
) as m;
end p5;
p6:begin
declare c1 cursor with return to client for
select * from SESSION.TB_RPT_MID_RS
order by 1,2;
open c1;
end p6;
END P1;⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -