wldn_data_code/a_pm_4g_cuct_all_day.sql

CREATE OR REPLACE FUNCTION app_screen.a_pm_4g_cuct_all_day(vi_date character varying)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
    DECLARE
	    v_date timestamp;
		v_next_day timestamp;
		v_begin_time timestamp;
		v_sql text;
BEGIN
    
	v_date := vi_date::timestamp;
	v_next_day := vi_date::timestamp + '1 day'::interval;
	v_begin_time := clock_timestamp();
	
--插入联通工参
v_sql := 'drop table if exists a_pm_info';
execute v_sql;	
v_sql := 'create temporary table a_pm_info
(
city varchar
,county varchar
,vendor varchar
,eci varchar
,cell_name varchar
)';
execute v_sql;
v_sql := 'insert into a_pm_info
select 
a.city
,a.county
,b.vendor 
,b.eci
,b.cellname
from app_screen.county a 
left join pm_parse.per_cfg_cell b 
on a.city = replace(b.city,''市'','''') and a.county = b.quxian';
execute v_sql;

--插入电信工参
v_sql := 'insert into a_pm_info
(
city 
,county 
,vendor 
,eci 
,cell_name
)
select 
city 
,county 
,vendor 
,''127.''||enbid||''.''||cellid 
,cell_name
from pm_parse.pm_ct_cell_info_4g';
execute v_sql;

-- 取自忙时
v_sql := 'drop table if exists a_pm_4g_cuct_all_day_tmp00';
execute v_sql;	
v_sql := 'create temporary table a_pm_4g_cuct_all_day_tmp00
(
eci varchar
,total_tra_mb numeric
)';
execute v_sql;
v_sql := 'insert into a_pm_4g_cuct_all_day_tmp00
select 
eci,max(total_tra_mb)
from pm_parse.pm_4g_hour_'|| vi_date ||'
group by eci';
execute v_sql;

v_sql := 'drop table if exists a_pm_4g_cuct_all_day_tmp01';
execute v_sql;	
v_sql := 'create temporary table a_pm_4g_cuct_all_day_tmp01
(
eci varchar
,dl_prb_utilization numeric
)';
execute v_sql;
v_sql := 'insert into a_pm_4g_cuct_all_day_tmp01
select 
coalesce(c.end_eci,a.eci),max(dl_prb_utilization)
from pm_parse.pm_4g_hour_'|| vi_date ||' a 
join a_pm_4g_cuct_all_day_tmp00 b 
on a.eci = b.eci and a.total_tra_mb = b.total_tra_mb

left join app_screen.nokia_eci_conf c --添加诺基亚eci映射关系表
on a.eci = c.eci

group by coalesce(c.end_eci,a.eci)';
execute v_sql;


v_sql := 'drop table if exists a_pm_4g_cuct_all_day_tmp02';
execute v_sql;	
v_sql := 'create temporary table a_pm_4g_cuct_all_day_tmp02
(
eci varchar
,total_tra_mb numeric
,rrc_max numeric

,erab_suc numeric
,erab_req numeric
,rrc_suc numeric
,rrc_req numeric

,erab_abnormal_release numeric
,lte_drop numeric

,x2_ho_out_suc numeric
,s1_ho_out_suc numeric
,x2_ho_out_req numeric
,s1_ho_out_req numeric

,dl_speed_mbps numeric

,erab_suc_qci1 numeric
,erab_req_qci1 numeric

,erab_abnormal_qci1 numeric
,erab_normal_qci1 numeric

,ul_pdcp_package_drop numeric
,ul_pdcp_package_total numeric

,dl_pdcp_package_drop numeric
,dl_pdcp_package_total numeric
)';
execute v_sql;
v_sql := 'insert into a_pm_4g_cuct_all_day_tmp02
select 
coalesce(c.end_eci,a.eci)
,sum(total_tra_mb)
,sum(rrc_max)

,sum(erab_suc)
,sum(erab_req)
,sum(rrc_suc)
,sum(rrc_req)

,sum(erab_abnormal_release)
,sum(lte_drop)

,sum(x2_ho_out_suc)
,sum(s1_ho_out_suc)
,sum(x2_ho_out_req)
,sum(s1_ho_out_req)

,sum(dl_speed_mbps)

,sum(erab_suc_qci1)
,sum(erab_req_qci1)

,sum(erab_abnormal_qci1)
,sum(erab_normal_qci1)

,sum(ul_pdcp_package_drop)
,sum(ul_pdcp_package_total)

,sum(dl_pdcp_package_drop)
,sum(dl_pdcp_package_total)
from pm_parse.pm_4g_day_'|| vi_date ||' a 
left join app_screen.nokia_eci_conf c --添加诺基亚eci映射关系表
on a.eci = c.eci
group by coalesce(c.end_eci,a.eci)';
execute v_sql;

-- 删除当前时间数据
v_sql := 'delete from app_screen.a_pm_4g_cuct_all_day where sdate = '''|| v_date ||'''';
execute v_sql;	

-- 插入目标数据
v_sql := 'insert into app_screen.a_pm_4g_cuct_all_day
(
sdate
,city
,county
,cell_number
,dl_prb_utilization
,busy_avg_dl_prb_utilization
,total_tra_mb
,rrc_max
,radio_conn_suc_r
,lte_drop_r
,ho_out_suc_r
,dl_speed_mbps_avg_d
,erab_suc_r_qci1
,lte_drop_r_qci1
,ul_pdcp_package_drop_ratio_qci1
,dl_pdcp_package_drop_ratio_qci1
)	 
select
'''|| v_date ||''' 
,COALESCE(a.city,''合计'') city
,COALESCE(a.county,''合计'') county
,count(distinct d.eci) cell_number
,avg(c.dl_prb_utilization)*100 dl_prb_utilization
,case when count(distinct d.eci) = 0 then 0 else sum(case when c.dl_prb_utilization*100 > 65 then 1 else 0 end)*1.0/count(distinct d.eci)*100 end busy_avg_dl_prb_utilization
,sum(d.total_tra_mb) total_tra_mb
,round(avg(d.rrc_max),0) rrc_max

,round((case when sum(d.erab_req)=0 then 0 else (sum(d.erab_suc)/sum(d.erab_req)) end) *100,2) radio_conn_suc_r
,round((case when sum(d.lte_drop) = 0 then 0 else sum(d.erab_abnormal_release)/sum(d.lte_drop) end) *100,2) lte_drop_r
,round((case when sum(d.x2_ho_out_req+d.s1_ho_out_req) = 0 then 0 else sum(d.x2_ho_out_suc+d.s1_ho_out_suc)/sum(d.x2_ho_out_req+d.s1_ho_out_req) end) *100,2) ho_out_suc_r
,round(avg(d.dl_speed_mbps),2) dl_speed_mbps_avg_d
,round((case when sum(d.erab_req_qci1) = 0 then 0 else sum(d.erab_suc_qci1)/sum(d.erab_req_qci1) end) *100,2) erab_suc_r_qci1
,round((case when sum(d.erab_abnormal_qci1+d.erab_normal_qci1) = 0 then 0 else sum(d.erab_abnormal_qci1)/sum(d.erab_abnormal_qci1+d.erab_normal_qci1) end) *100,2) lte_drop_r_qci1
,round((case when sum(d.ul_pdcp_package_total) = 0 then 0 else sum(d.ul_pdcp_package_drop)/sum(d.ul_pdcp_package_total) end)*100,2) ul_pdcp_package_drop_ratio_qci1
,round((case when sum(d.dl_pdcp_package_total) = 0 then 0 else sum(d.dl_pdcp_package_drop)/sum(d.dl_pdcp_package_total) end)*100,2) dl_pdcp_package_drop_ratio_qci1
from a_pm_info a
left join a_pm_4g_cuct_all_day_tmp01 c
on a.eci = c.eci 
left join a_pm_4g_cuct_all_day_tmp02 d
on a.eci = d.eci
group by 
grouping sets((),(a.city),(a.city,a.county))';
execute v_sql;


-- 更新质差小区数量和占比
v_sql := 'update app_screen.a_pm_4g_cuct_all_day a 
set poor_cell_num = COALESCE(b.poor_cell_num,0),poor_cell_ratio = COALESCE(b.poor_cell_ratio,0)
from(
select
city 
,county
,poor_cell_num 
,poor_cell_ratio 
from app_screen.a_pm_4g_poor_cell_day
where sdate = '''|| v_date ||'''
and operators = ''联通''
) b
where a.sdate = '''|| v_date ||'''
and a.city = b.city
and a.county = b.county';
execute v_sql;

--更新mr数据
v_sql := 'update app_screen.a_pm_4g_cuct_all_day a
set poor_samples_110 = b.poor_samples_110,rsrp_samples = b.rsrp_samples,cove_rage = b.cove_rage
from app_screen.a_pm_4g_mr_cuct_all_day b 
where a.county = ''合计'' 
and a.sdate = '''|| v_date ||'''
and b.sdate = (select max(sdate) from app_screen.a_pm_4g_mr_cuct_all_day)
and a.city = b.city';
execute v_sql;


return '0';
END;
$function$
;