#2
林月儿2020-03-03 17:06
|
程序代码:
create or replace procedure job is
begin
select
lcelav.period_start_time 时间,
LNCEL.LNCEL_CELL_NAME 小区,
sum(LIANBHO.SUCC_INTRA_ENB_HO+LIENBHO.SUCC_INTER_ENB_HO+LIENBHO.INTER_ENB_S1_HO_SUCC) 系统内切换成功次数,
sum(LIANBHO.ATT_INTRA_ENB_HO+LIENBHO.ATT_INTER_ENB_HO+LIENBHO.INTER_ENB_S1_HO_ATT) 系统内切换请求次数,
sum(LIANBHO.ATT_INTRA_ENB_HO) eNodeB内切换请求次数,
sum(LIANBHO.SUCC_INTRA_ENB_HO)eNodeB内切换成功次数,
round(decode(sum(LIANBHO.ATT_INTRA_ENB_HO),0,NULL,(sum(LIANBHO.SUCC_INTRA_ENB_HO)/sum(LIANBHO.ATT_INTRA_ENB_HO)))*100,2) eNodeB内切换成功率,
sum(LIENBHO.ATT_INTER_ENB_HO) X2接口切换请求次数,
sum(LIENBHO.SUCC_INTER_ENB_HO)X2接口切换成功次数
From
NOKLTE_PS_LUEQ_lncel_day lueq,
noklte_ps_lcelav_lncel_day lcelav,
noklte_ps_luest_lncel_day luest,
ctp_common_objects lncel_info,
ctp_common_objects lnbts_info,
ctp_common_objects fdd,
c_lte_lncel lncel,
c_lte_lncel_fdd lnfdd
Where
lcelav.lncel_id = LUEQ.lncel_id
and lcelav.lncel_id = luest.lncel_id
and lcelav.lncel_id = lepsb.lncel_id
and lcelav.lncel_id = lcellt.lncel_id
and lcelav.lncel_id = lcellr.lncel_id
and lcelav.period_start_time = LUEQ.period_start_time
and lcelav.period_start_time = luest.period_start_time
and lcelav.period_start_time = lepsb.period_start_time
and lcelav.lncel_id = lncel_info.co_gid
and lcelav.lnbts_id = lnbts_info.co_gid
and lcelav.lncel_id = lncel.obj_gid
and lnfdd.obj_gid=fdd.co_gid
and lncel.obj_gid=lncel_info.co_gid
and fdd.co_parent_gid=lncel_info.co_gid
and lncel.conf_id = 1
and lnfdd.conf_id=1
and lcelav.period_start_time >= to_date(&start_time, 'yyyy-mm-dd')
and lcelav.period_start_time < to_date(&end_time, 'yyyy-mm-dd')
AND LCELLT.PDCP_SDU_VOL_UL+LCELLT.PDCP_SDU_VOL_DL=0
Group By
lcelav.period_start_time,
LNCEL.LNCEL_CELL_NAME;
end;
begin
select
lcelav.period_start_time 时间,
LNCEL.LNCEL_CELL_NAME 小区,
sum(LIANBHO.SUCC_INTRA_ENB_HO+LIENBHO.SUCC_INTER_ENB_HO+LIENBHO.INTER_ENB_S1_HO_SUCC) 系统内切换成功次数,
sum(LIANBHO.ATT_INTRA_ENB_HO+LIENBHO.ATT_INTER_ENB_HO+LIENBHO.INTER_ENB_S1_HO_ATT) 系统内切换请求次数,
sum(LIANBHO.ATT_INTRA_ENB_HO) eNodeB内切换请求次数,
sum(LIANBHO.SUCC_INTRA_ENB_HO)eNodeB内切换成功次数,
round(decode(sum(LIANBHO.ATT_INTRA_ENB_HO),0,NULL,(sum(LIANBHO.SUCC_INTRA_ENB_HO)/sum(LIANBHO.ATT_INTRA_ENB_HO)))*100,2) eNodeB内切换成功率,
sum(LIENBHO.ATT_INTER_ENB_HO) X2接口切换请求次数,
sum(LIENBHO.SUCC_INTER_ENB_HO)X2接口切换成功次数
From
NOKLTE_PS_LUEQ_lncel_day lueq,
noklte_ps_lcelav_lncel_day lcelav,
noklte_ps_luest_lncel_day luest,
ctp_common_objects lncel_info,
ctp_common_objects lnbts_info,
ctp_common_objects fdd,
c_lte_lncel lncel,
c_lte_lncel_fdd lnfdd
Where
lcelav.lncel_id = LUEQ.lncel_id
and lcelav.lncel_id = luest.lncel_id
and lcelav.lncel_id = lepsb.lncel_id
and lcelav.lncel_id = lcellt.lncel_id
and lcelav.lncel_id = lcellr.lncel_id
and lcelav.period_start_time = LUEQ.period_start_time
and lcelav.period_start_time = luest.period_start_time
and lcelav.period_start_time = lepsb.period_start_time
and lcelav.lncel_id = lncel_info.co_gid
and lcelav.lnbts_id = lnbts_info.co_gid
and lcelav.lncel_id = lncel.obj_gid
and lnfdd.obj_gid=fdd.co_gid
and lncel.obj_gid=lncel_info.co_gid
and fdd.co_parent_gid=lncel_info.co_gid
and lncel.conf_id = 1
and lnfdd.conf_id=1
and lcelav.period_start_time >= to_date(&start_time, 'yyyy-mm-dd')
and lcelav.period_start_time < to_date(&end_time, 'yyyy-mm-dd')
AND LCELLT.PDCP_SDU_VOL_UL+LCELLT.PDCP_SDU_VOL_DL=0
Group By
lcelav.period_start_time,
LNCEL.LNCEL_CELL_NAME;
end;
[local]1[/local]