wldn_data_code/a_pm_4g_poor_cell_day.sql

CREATE OR REPLACE FUNCTION app_screen.a_pm_4g_poor_cell_day(vi_date character varying)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
    DECLARE
	    v_date timestamp;
		v_next_day timestamp;
		v_befor_day timestamp;
		v_befor1_day timestamp;
		v_befor2_day timestamp;
		v_befor3_day timestamp;
		v_befor4_day timestamp;
		v_befor5_day timestamp;
		
		v_befor_day_s varchar;
		v_befor1_day_s varchar;
		v_befor2_day_s varchar;
		v_befor3_day_s varchar;
		v_befor4_day_s varchar;
		v_befor5_day_s varchar;
		
		v_begin_time timestamp;
		v_sql text;
BEGIN
	v_date := vi_date::timestamp;
	v_next_day := vi_date::timestamp + '1 day'::interval;
	v_befor_day := vi_date::timestamp + '-1 day'::interval;
	v_befor1_day := vi_date::timestamp + '-2 day'::interval;
	v_befor2_day := vi_date::timestamp + '-3 day'::interval;
	v_befor3_day := vi_date::timestamp + '-4 day'::interval;
	v_befor4_day := vi_date::timestamp + '-5 day'::interval;
	v_befor5_day := vi_date::timestamp + '-6 day'::interval;
	
	v_befor_day_s := replace(date(vi_date::timestamp + '-1 day'::interval)::varchar,'-','');
	v_befor1_day_s := replace(date(vi_date::timestamp + '-2 day'::interval)::varchar,'-','');
	v_befor2_day_s := replace(date(vi_date::timestamp + '-3 day'::interval)::varchar,'-','');
	v_befor3_day_s := replace(date(vi_date::timestamp + '-4 day'::interval)::varchar,'-','');
	v_befor4_day_s := replace(date(vi_date::timestamp + '-5 day'::interval)::varchar,'-','');
	v_befor5_day_s := replace(date(vi_date::timestamp + '-6 day'::interval)::varchar,'-','');
	
	
	v_begin_time := clock_timestamp();

	
/* 
 * 
 * 
 *联通部分共建共享统计
 * 
 * 
 * 
*/

-- 取联通区县信息
v_sql := 'drop table if exists a_pm_4g_poor_cell_day_tmp01';
execute v_sql;	
v_sql := 'create temporary table a_pm_4g_poor_cell_day_tmp01
(
city varchar
,county varchar
,vendor varchar
,eci varchar
,cell_name varchar
)';
execute v_sql;
v_sql := 'insert into a_pm_4g_poor_cell_day_tmp01
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 := 'drop table if exists a_pm_4g_poor_cell_day_tmp02';
execute v_sql;	
v_sql := 'create temporary table a_pm_4g_poor_cell_day_tmp02
(
city varchar
,county varchar
,eci varchar
,conn_suc_poor integer
,lte_highdrop_poor integer
,ho_out_suc_poor integer
,volte_join_poor integer
,volte_highdrop_poor integer
,ul_pdcp_package_drop_poor integer
,dl_pdcp_package_drop_poor integer
)';
execute v_sql;
v_sql := 'insert into a_pm_4g_poor_cell_day_tmp02
(
city 
,county 
,eci 
,conn_suc_poor 
,lte_highdrop_poor 
,ho_out_suc_poor 
,volte_join_poor 
,volte_highdrop_poor 
,ul_pdcp_package_drop_poor 
,dl_pdcp_package_drop_poor
)
select 
a.city
,a.county
,a.eci
,count(distinct case when radio_conn_suc_r <= 0.9 and rrc_suc > 1000 then sdate end) conn_suc_poor
,count(distinct case when lte_drop_r >= 2 and lte_drop > 1000 then sdate end) lte_highdrop_poor
,count(distinct case when ho_out_suc_r <= 0.9 and (s1_ho_out_req+x2_ho_out_req) > 500 then sdate end) ho_out_suc_poor
,count(distinct case when erab_suc_r_qci1 < 0.98 or erab_suc_r_qci5 < 0.98 then sdate end) volte_join_poor
,count(distinct case when lte_drop_r_qci1 > 0.01 then sdate end) volte_highdrop_poor
,count(distinct case when ul_pdcp_package_drop_ratio_qci1 > 0.01 then sdate end) ul_pdcp_package_drop_poor
,count(distinct case when dl_pdcp_package_drop_ratio_qci1 > 0.01 then sdate end) dl_pdcp_package_drop_poor
from a_pm_4g_poor_cell_day_tmp01 a 
join pm_parse.pm_4g_day b 
on b.sdate >= '''|| v_date ||'''::timestamp - ''6 day''::interval and b.sdate < '''|| v_date ||'''::timestamp + ''1 day''::interval
and b.isp = ''联通'' and b.share = ''是''
and a.eci = b.eci 
group by a.city,a.county,a.eci';
execute v_sql;



v_sql := 'drop table if exists busy_info';
execute v_sql;	
v_sql := 'create temporary table busy_info
(
sdate timestamp
,eci varchar
,total_tra_mb numeric
)';
execute v_sql;
v_sql := 'insert into busy_info
select 
date(sdate),eci,max(total_tra_mb)
from pm_parse.pm_4g_hour b
where b.sdate >= '''|| v_date ||'''::timestamp - ''6 day''::interval and b.sdate < '''|| v_date ||'''::timestamp + ''1 day''::interval
and b.isp = ''联通'' and b.share = ''是''
group by date(sdate),eci';
execute v_sql;


v_sql := 'drop table if exists a_pm_4g_poor_cell_day_tmp03';
execute v_sql;	
v_sql := 'create temporary table a_pm_4g_poor_cell_day_tmp03
(
eci varchar
,cqi_poor integer
,highload_prb  integer
,low_speed_mbps integer
)';
execute v_sql;
v_sql := 'insert into a_pm_4g_poor_cell_day_tmp03
(
eci
,cqi_poor
,highload_prb
,low_speed_mbps
)
select 
eci
,case when sum(cqi_ge7)/7 > 0.2 then 1 else 0 end
,case when avg(dl_prb_utilization) > 0.65 then 1 else 0 end
,case when avg(dl_speed_mbps) < 5 then 1 else 0 end
from (
select 
t.sdate 
,t.eci
,1-t.cqi_ge7 cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| v_befor5_day_s ||' t 
join busy_info a 
on a.sdate = '''|| v_befor5_day ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''联通'' and t.share = ''是''
union all 
select 
t.sdate 
,t.eci
,1-t.cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| v_befor4_day_s ||' t 
join busy_info a 
on a.sdate = '''|| v_befor4_day ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''联通'' and t.share = ''是''
union all 
select 
t.sdate 
,t.eci
,1-t.cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| v_befor3_day_s ||' t 
join busy_info a 
on a.sdate = '''|| v_befor3_day ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''联通'' and t.share = ''是''
union all 
select 
t.sdate 
,t.eci
,1-t.cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| v_befor2_day_s ||' t 
join busy_info a 
on a.sdate = '''|| v_befor2_day ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''联通'' and t.share = ''是''
union all 
select 
t.sdate 
,t.eci
,1-t.cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| v_befor1_day_s ||' t 
join busy_info a 
on a.sdate = '''|| v_befor1_day ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''联通'' and t.share = ''是''
union all 
select 
t.sdate 
,t.eci
,1-t.cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| v_befor_day_s ||' t 
join busy_info a 
on a.sdate = '''|| v_befor_day ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''联通'' and t.share = ''是''
union all 
select 
t.sdate 
,t.eci
,1-t.cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| vi_date ||' t 
join busy_info a 
on a.sdate = '''|| v_date ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''联通'' and t.share = ''是''
) tb 
where tb.rn = 1
group by eci';
execute v_sql;


--删除当期数据
v_sql := 'delete from app_screen.a_pm_4g_poor_cell_day where sdate = '''|| v_date ||'''';
execute v_sql;	

-- 插入目标数据
v_sql := 'insert into app_screen.a_pm_4g_poor_cell_day
(
sdate
,city 
,county 
,operators 
,share_cell_num 
,conn_suc_poor 
,lte_highdrop_poor 
,cqi_poor 
,low_speed_mbps 
,ho_out_suc_poor 
,volte_join_poor 
,volte_highdrop_poor 
,ul_pdcp_package_drop_poor 
,dl_pdcp_package_drop_poor 
,highload_prb 
,poor_cell_num 
,poor_cell_ratio
)
select 
'''|| v_date ||''' sdate
,coalesce(a.city,''合计'')
,coalesce(a.county,''合计'')
,''联通'' operators
,count(distinct b.eci) share_cell_num
,sum(case when b.conn_suc_poor >= 3 then 1 else 0 end)
,sum(case when b.lte_highdrop_poor >= 3 then 1 else 0 end)
,sum(c.cqi_poor)
,sum(c.low_speed_mbps)
,sum(case when b.ho_out_suc_poor >= 3 then 1 else 0 end)
,sum(case when b.volte_join_poor >= 3 then 1 else 0 end)
,sum(case when b.volte_highdrop_poor >= 3 then 1 else 0 end)
,sum(case when b.ul_pdcp_package_drop_poor >= 3 then 1 else 0 end)
,sum(case when b.dl_pdcp_package_drop_poor >= 3 then 1 else 0 end)
,sum(c.highload_prb)
,count(distinct case when b.conn_suc_poor >= 3 or b.lte_highdrop_poor >= 3 or b.ho_out_suc_poor >= 3 or b.volte_join_poor >= 3
          or b.volte_highdrop_poor >= 3 or b.ul_pdcp_package_drop_poor >= 3 or b.dl_pdcp_package_drop_poor >= 3
          or c.cqi_poor = 1 or c.low_speed_mbps = 1 then b.eci end) poor_cell_num
,count(distinct case when b.conn_suc_poor >= 3 or b.lte_highdrop_poor >= 3 or b.ho_out_suc_poor >= 3 or b.volte_join_poor >= 3
          or b.volte_highdrop_poor >= 3 or b.ul_pdcp_package_drop_poor >= 3 or b.dl_pdcp_package_drop_poor >= 3
          or c.cqi_poor = 1 or c.low_speed_mbps = 1 then b.eci end)*1.0/count(distinct b.eci)*100 poor_cell_ratio
from a_pm_4g_poor_cell_day_tmp01 a
join a_pm_4g_poor_cell_day_tmp02 b 
on a.eci = b.eci
left join a_pm_4g_poor_cell_day_tmp03 c
on b.eci = c.eci
group by 
grouping sets((),(a.city),(a.city,a.county))';
execute v_sql;






/* 
 * 
 * 
 *电信部分共建共享统计
 * 
 * 
 * 
*/
-- 电信小区区县信息表
v_sql := 'drop table if exists a_pm_4g_poor_cell_day_tmp04';
execute v_sql;	
v_sql := 'create temporary table a_pm_4g_poor_cell_day_tmp04
(
city varchar
,county varchar
,vendor varchar
,enbid integer
,cellid integer
,eci varchar
,cell_name varchar
)';
execute v_sql;
v_sql := 'insert into a_pm_4g_poor_cell_day_tmp04
(
city 
,county 
,vendor
,enbid 
,cellid 
,eci 
,cell_name
)
select 
city 
,county 
,vendor
,enbid 
,cellid 
,''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_poor_cell_day_tmp05';
execute v_sql;	
v_sql := 'create temporary table a_pm_4g_poor_cell_day_tmp05
(
city varchar
,county varchar
,eci varchar
,conn_suc_poor integer
,lte_highdrop_poor integer
,ho_out_suc_poor integer
,volte_join_poor integer
,volte_highdrop_poor integer
,ul_pdcp_package_drop_poor integer
,dl_pdcp_package_drop_poor integer
)';
execute v_sql;
v_sql := 'insert into a_pm_4g_poor_cell_day_tmp05
(
city 
,county 
,eci 
,conn_suc_poor 
,lte_highdrop_poor 
,ho_out_suc_poor 
,volte_join_poor 
,volte_highdrop_poor 
,ul_pdcp_package_drop_poor 
,dl_pdcp_package_drop_poor  
)
select 
a.city
,a.county
,a.eci
,count(distinct case when radio_conn_suc_r <= 0.9 and rrc_suc > 1000 then sdate end) conn_suc_poor
,count(distinct case when lte_drop_r >= 2 and lte_drop > 1000 then sdate end) lte_highdrop_poor
,count(distinct case when ho_out_suc_r <= 0.9 and (s1_ho_out_req+x2_ho_out_req) > 500 then sdate end) ho_out_suc_poor
,count(distinct case when erab_suc_r_qci1 < 0.98 or erab_suc_r_qci5 < 0.98 then sdate end) volte_join_poor
,count(distinct case when lte_drop_r_qci1 > 0.01 then sdate end) volte_highdrop_poor
,count(distinct case when ul_pdcp_package_drop_ratio_qci1 > 0.01 then sdate end) ul_pdcp_package_drop_poor
,count(distinct case when dl_pdcp_package_drop_ratio_qci1 > 0.01 then sdate end) dl_pdcp_package_drop_poor
from a_pm_4g_poor_cell_day_tmp04 a 
join pm_parse.pm_4g_day b 
on b.sdate >= '''|| v_date ||'''::timestamp - ''6 day''::interval and b.sdate < '''|| v_date ||'''::timestamp + ''1 day''::interval
and b.isp = ''电信'' 
and a.eci = b.eci 
group by a.city,a.county,a.eci';
execute v_sql;



v_sql := 'drop table if exists busy_info_ct';
execute v_sql;	
v_sql := 'create temporary table busy_info_ct
(
sdate timestamp
,eci varchar
,total_tra_mb numeric
)';
execute v_sql;
v_sql := 'insert into busy_info_ct
select 
date(sdate),eci,max(total_tra_mb)
from pm_parse.pm_4g_hour b
where b.sdate >= '''|| v_date ||'''::timestamp - ''6 day''::interval and b.sdate < '''|| v_date ||'''::timestamp + ''1 day''::interval
and b.isp = ''电信''
group by date(sdate),eci';
execute v_sql;


v_sql := 'drop table if exists a_pm_4g_poor_cell_day_tmp06';
execute v_sql;	
v_sql := 'create temporary table a_pm_4g_poor_cell_day_tmp06
(
eci varchar
,cqi_poor integer
,highload_prb  integer
,low_speed_mbps integer
)';
execute v_sql;
v_sql := 'insert into a_pm_4g_poor_cell_day_tmp06
(
eci
,cqi_poor
,highload_prb
,low_speed_mbps
)
select 
eci
,case when sum(cqi_ge7)/7 > 0.2 then 1 else 0 end
,case when avg(dl_prb_utilization) > 0.65 then 1 else 0 end
,case when avg(dl_speed_mbps) < 5 then 1 else 0 end
from (
select 
t.sdate 
,t.eci
,1-t.cqi_ge7 cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| v_befor5_day_s ||' t 
join busy_info_ct a 
on a.sdate = '''|| v_befor5_day ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''电信''
union all 
select 
t.sdate 
,t.eci
,1-t.cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| v_befor4_day_s ||' t 
join busy_info_ct a 
on a.sdate = '''|| v_befor4_day ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''电信''
union all 
select 
t.sdate 
,t.eci
,1-t.cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| v_befor3_day_s ||' t 
join busy_info_ct a 
on a.sdate = '''|| v_befor3_day ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''电信''
union all 
select 
t.sdate 
,t.eci
,1-t.cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| v_befor2_day_s ||' t 
join busy_info_ct a 
on a.sdate = '''|| v_befor2_day ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''电信''
union all 
select 
t.sdate 
,t.eci
,1-t.cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| v_befor1_day_s ||' t 
join busy_info_ct a 
on a.sdate = '''|| v_befor1_day ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''电信''
union all 
select 
t.sdate 
,t.eci
,1-t.cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| v_befor_day_s ||' t 
join busy_info_ct a 
on a.sdate = '''|| v_befor_day ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''电信''
union all 
select 
t.sdate 
,t.eci
,1-t.cqi_ge7
,t.dl_prb_utilization
,dl_speed_mbps
,row_number() over (partition by a.sdate,a.eci order by t.sdate desc) rn
from pm_parse.pm_4g_hour_'|| vi_date ||' t 
join busy_info_ct a 
on a.sdate = '''|| v_date ||'''
and t.eci = a.eci
and t.total_tra_mb = a.total_tra_mb
and t.isp = ''电信''
) tb 
where tb.rn = 1
group by eci';
execute v_sql;

-- 插入目标数据
v_sql := 'insert into app_screen.a_pm_4g_poor_cell_day
(
sdate
,city 
,county 
,operators 
,share_cell_num 
,conn_suc_poor 
,lte_highdrop_poor 
,cqi_poor 
,low_speed_mbps 
,ho_out_suc_poor 
,volte_join_poor 
,volte_highdrop_poor 
,ul_pdcp_package_drop_poor 
,dl_pdcp_package_drop_poor 
,highload_prb 
,poor_cell_num 
,poor_cell_ratio
)
select 
'''|| v_date ||''' sdate
,coalesce(a.city,''合计'')
,coalesce(a.county,''合计'')
,''电信'' operators
,count(distinct b.eci) share_cell_num
,sum(case when b.conn_suc_poor >= 3 then 1 else 0 end)
,sum(case when b.lte_highdrop_poor >= 3 then 1 else 0 end)
,sum(c.cqi_poor)
,sum(c.low_speed_mbps)
,sum(case when b.ho_out_suc_poor >= 3 then 1 else 0 end)
,sum(case when b.volte_join_poor >= 3 then 1 else 0 end)
,sum(case when b.volte_highdrop_poor >= 3 then 1 else 0 end)
,sum(case when b.ul_pdcp_package_drop_poor >= 3 then 1 else 0 end)
,sum(case when b.dl_pdcp_package_drop_poor >= 3 then 1 else 0 end)
,sum(c.highload_prb)
,count(distinct case when b.conn_suc_poor >= 3 or b.lte_highdrop_poor >= 3 or b.ho_out_suc_poor >= 3 or b.volte_join_poor >= 3
          or b.volte_highdrop_poor >= 3 or b.ul_pdcp_package_drop_poor >= 3 or b.dl_pdcp_package_drop_poor >= 3
          or c.cqi_poor = 1 or c.low_speed_mbps = 1 then b.eci end) poor_cell_num
,count(distinct case when b.conn_suc_poor >= 3 or b.lte_highdrop_poor >= 3 or b.ho_out_suc_poor >= 3 or b.volte_join_poor >= 3
          or b.volte_highdrop_poor >= 3 or b.ul_pdcp_package_drop_poor >= 3 or b.dl_pdcp_package_drop_poor >= 3
          or c.cqi_poor = 1 or c.low_speed_mbps = 1 then b.eci end)*1.0/count(distinct b.eci)*100 poor_cell_ratio
from a_pm_4g_poor_cell_day_tmp04 a
join a_pm_4g_poor_cell_day_tmp05 b 
on a.eci = b.eci
left join a_pm_4g_poor_cell_day_tmp06 c
on b.eci = c.eci
group by 
grouping sets((),(a.city),(a.city,a.county))';
execute v_sql;



return '0';
END;
$function$
;