report_auto/doc/开发文档/数据库文档/一些语句.sql

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