wldn_data_code/a_pm_4g_ct_share_day.sql

CREATE OR REPLACE FUNCTION app_screen.a_pm_4g_ct_share_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
(
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_ct_share_day_tmp00';
execute v_sql;	
v_sql := 'create temporary table a_pm_4g_ct_share_day_tmp00
(
eci varchar
,total_tra_mb numeric
)';
execute v_sql;
v_sql := 'insert into a_pm_4g_ct_share_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_ct_share_day_tmp01';
execute v_sql;	
v_sql := 'create temporary table a_pm_4g_ct_share_day_tmp01
(
eci varchar
,dl_prb_utilization numeric
)';
execute v_sql;
v_sql := 'insert into a_pm_4g_ct_share_day_tmp01
select 
a.eci,max(dl_prb_utilization)
from pm_parse.pm_4g_hour_'|| vi_date ||' a 
join a_pm_4g_ct_share_day_tmp00 b 
on a.eci = b.eci and a.total_tra_mb = b.total_tra_mb
where a.isp = ''电信''
group by a.eci';
execute v_sql;



v_sql := 'drop table if exists a_pm_4g_ct_share_day_tmp02';
execute v_sql;	
v_sql := 'create temporary table a_pm_4g_ct_share_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_ct_share_day_tmp02
select 
eci
,PDCP_SDU_VOL_UL_plmn2 + PDCP_SDU_VOL_DL_plmn2 total_tra_mb

,effectiveconnmean_plmn2 rrc_max

,erab_suc
,erab_req
,rrc_suc
,rrc_req

,erab_abnormal_release
,lte_drop

,x2_ho_out_suc
,s1_ho_out_suc
,x2_ho_out_req
,s1_ho_out_req

,dl_speed_mbps

,erab_suc_qci1
,erab_req_qci1

,erab_abnormal_qci1
,erab_normal_qci1

,ul_pdcp_package_drop
,ul_pdcp_package_total

,dl_pdcp_package_drop
,dl_pdcp_package_total 
from pm_parse.pm_4g_day_'|| vi_date ||'
where isp = ''电信'' and share = ''是''';
execute v_sql;

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

-- 插入目标数据
v_sql := 'insert into app_screen.a_pm_4g_ct_share_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_ct_share_day_tmp01 c
on a.eci = c.eci 
left join a_pm_4g_ct_share_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_ct_share_day a 
set poor_cell_num = b.poor_cell_num,poor_cell_ratio = b.poor_cell_ratio
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_ct_share_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_ct_share_day b 
where a.county = ''合计'' 
and a.sdate = '''|| v_date ||'''
and b.sdate = (select max(sdate) from app_screen.a_pm_4g_mr_ct_share_day)
and a.city = b.city';
execute v_sql;


return '0';
END;
$function$
;