-- 重复工单
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