-- 重复工单
with t1 as (select compl_area_local,busi_no from report_auto.he_d_mobile_comp hdmc where month_id = substring('20230420' from 1 for 6) and day_id <= substring('20230420' from 7 for 2)),
t2 as (select distinct * from t1),
t3 as (select compl_area_local, count(1) as total_num from t1 group by compl_area_local),
t4 as (select compl_area_local, count(1) as distinct_num from t2 group by compl_area_local),
t5 as (select t3.compl_area_local, t3.total_num, t3.total_num - t4.distinct_num as repeat_num, (t3.total_num - t4.distinct_num)/t3.total_num::numeric  as repeat_ratio from T3, t4 where t3.compl_area_local = t4.compl_area_local)
select '全省' as compl_area_local, sum(total_num) as total_num, sum(repeat_num) as repeat_num, sum(repeat_num) /sum(total_num)::numeric as repeat_ratio from t5
union select * from t5

-- 超时工单
with t1 as (select compl_area_local, is_timeout from report_auto.he_d_mobile_comp hdmc where month_id = substring('20230420' from 1 for 6) and day_id <= substring('20230420' from 7 for 2)),
t2 as (select '全省' as compl_area_local, count(1) as total_num from t1),
t3 as (select compl_area_local, count(1) as total_num from t1 group by compl_area_local),
t4 as (select * from t2 union select * from t3),
t5 as (select compl_area_local from t1 where is_timeout = '是'),
t7 as (select '全省' as compl_area_local, count(1) as timeout_num from t5),
t8 as (select compl_area_local, count(1) as timeout_num from t5 group by compl_area_local),
t9 as (select * from t7 union select * from t8)
select t4.compl_area_local, t4.total_num, t9.timeout_num, t9.timeout_num/t4.total_num::numeric as timeout_ratio from t4,t9 where t4.compl_area_local = t9.compl_area_local order by t9.timeout_num/t4.total_num::numeric desc

-- 超时时间
with t1 as (select compl_area_local, case when proce_time != '' then (extract('epoch' from to_timestamp(proce_time, 'YYYY-MM-DD HH24:MI:SS')) - extract('epoch' from to_timestamp(accept_time, 'YYYY-MM-DD HH24:MI:SS')))/3600 when is_online_complete = '是' then 0
else (extract('epoch' from to_timestamp(end_time, 'YYYY-MM-DD HH24:MI:SS')) - extract('epoch' from to_timestamp(accept_time, 'YYYY-MM-DD HH24:MI:SS')))/3600 end as duration
from report_auto.he_d_mobile_comp hdmc where month_id = substring('20230420' from 1 for 6) and day_id <= substring('20230420' from 7 for 2))
select compl_area_local, avg(duration) as avg_duration from t1 group by compl_area_local

-- 客户端统计
with t1 as (select businoareaname, complaint_satisfied_list::numeric, complaint_satisfied_count::numeric,
complaint_resolution_list::numeric, complaint_resolution_count::numeric,
complaint_response_list::numeric, complaint_response_count::numeric from report_auto.he_d_high_quality hdhq 
where acct_date = '2023-04-20'
and profes_dep = '网络质量'
and big_type_name = '移网网络体验'
and small_type_name = '--')
select '全省' as businoareaname, 
sum(complaint_satisfied_list) / sum(complaint_satisfied_count) as complaint_satisfied,
sum(complaint_resolution_list) / sum(complaint_resolution_count) as complaint_resolution,
sum(complaint_response_list) / sum(complaint_response_count) as complaint_response from t1 union
select businoareaname, 
complaint_satisfied_list / complaint_satisfied_count as complaint_satisfied, 
complaint_resolution_list / complaint_resolution_count as complaint_resolution, 
complaint_response_list / complaint_response_count as complaint_response from t1