【資料名稱】：無(wú)線接入性腳本

【資料作者】：XXXXX

【資料日期】：20120610

【資料語(yǔ)言】：中文

【資料格式】：DOC

【資料目錄和簡(jiǎn)介】：

/* seg cell kpi info */
select to_char(tra.period_start_time,'yyyymmddhh24')"sdate",
--to_char(tra.period_start_time,'hh24')stime,
o_bsc.namebsc_name,
c_bts.la_id_laclac,
c_bts.cell_idcell_id,
tra.segment_id seg_id,
c_bts.segment_nameseg_name,
round(sum(ave_avail_tch_sum/ave_avail_tch_den),0)"工作信道數(shù)(不含GPRS)",
round(sum(ave_avail_tch_sum/ave_avail_tch_den)+sum(ave_non_avail_tch),0)"配置信道數(shù)(不含GPRS)",
round(100*decode(sum(ave_avail_TCH_sum/ave_avail_TCH_den+ave_GPRS_channels_sum/ave_GPRS_channels_den+ave_non_avail_TCH),0,0,
(sum(ave_avail_TCH_sum/ave_avail_TCH_den)+sum(ave_GPRS_channels_sum/ave_GPRS_channels_den))/
(sum(ave_avail_TCH_sum/ave_avail_TCH_den+ave_GPRS_channels_sum/ave_GPRS_channels_den+ave_non_avail_TCH))),2)"TCH可用率",
round(sum(AVE_AVAIL_FULL_TCH/RES_AV_DENOM2)+sum(AVE_GPRS_CHANNELS_SUM/
AVE_GPRS_CHANNELS_DEN)-sum(AVE_PERMANENT_GPRS_CH_SUM/ AVE_PERMANENT_GPRS_CH_DEN),0)"全速率信道可用數(shù)",
round(sum(AVE_TCH_AVAIL_HALF),0) "半速率信道可用數(shù)",
round(sum(ava.ave_avail_full_tch/ava.res_av_denom2),0)"tch信道數(shù)",
round(sum(ave_sdcch_sub/res_av_denom3),0) "SD信道數(shù)",
round(sum(ave_busy_tch/res_av_denom14),2) "TCH話務(wù)量",
round(sum(ava.ave_tch_busy_full),2) "全速率erl",
round(sum(ava.ave_tch_busy_half),2) "半速率erl",
round(sum(ava.ave_busy_sdcch/ava.res_av_denom15),2)"SD話務(wù)量",
round(decode(sum(ava.ave_avail_full_tch/ava.res_av_denom2)+sum(ava.ave_gprs_channels_sum/ava.ave_gprs_channels_den)-
sum(ava.ave_permanent_gprs_ch_sum/ava.ave_permanent_gprs_ch_den)+sum(ava.ave_tch_avail_half),0,0,
sum(ava.ave_tch_busy_full+ava.ave_tch_busy_half)/(sum(ava.ave_avail_full_tch/ava.res_av_denom2)+sum(ava.ave_gprs_channels_sum/ava.ave_gprs_channels_den)-
sum(ava.ave_permanent_gprs_ch_sum/ava.ave_permanent_gprs_ch_den)+sum(ava.ave_tch_avail_half))),4)"每線話務(wù)量",
sum(tra.tch_call_req-tra.a_if_crc_mismatch_call_setup-ho.msc_o_sdcch_tch-ho.bsc_o_sdcch_tch-ho.cell_sdcch_tch_at)"TCH試呼次數(shù)(不含切換)",
sum(tra.que_all_ass_req_fail+ tra.que_nall_ass_req_fail-tra.a_if_crc_mismatch_call_setup+ tra.removal_from_que_due_to_dr-
ho.msc_o_sdcch_tch- ho.bsc_o_sdcch_tch - ho.cell_sdcch_tch)"TCH擁塞次數(shù)(不含切換)",
round(100*decode(sum(tra.tch_call_req-tra.a_if_crc_mismatch_call_setup
-ho.msc_o_sdcch_tch-ho.bsc_o_sdcch_tch-ho.cell_sdcch_tch_at),0,0,
sum(tra.que_all_ass_req_fail
+ tra.que_nall_ass_req_fail-tra.a_if_crc_mismatch_call_setup+ tra.removal_from_que_due_to_dr
-ho.msc_o_sdcch_tch
- ho.bsc_o_sdcch_tch
- ho.cell_sdcch_tch)/sum(tra.tch_call_req-tra.a_if_crc_mismatch_call_setup
-ho.msc_o_sdcch_tch-ho.bsc_o_sdcch_tch-ho.cell_sdcch_tch_at)),2) "TCH擁塞率(不含切換)",
sum(tra.tch_request
-tra.a_if_crc_mismatch_call_setup+ho.msc_i_tch_tch_at
+ho.msc_i_sdcch_tch_at+ho.bsc_i_tch_tch_at+ho.bsc_i_sdcch_tch_at+ho.cell_tch_tch_at+ho.cell_sdcch_tch_at)"TCH試呼次數(shù)(含切換)",

sum(tra.tch_req_rej_lack-tra.tch_rej_due_req_ch_a_if_crc)"TCH擁塞次數(shù)(含切換)",
round(100*decode(sum(tra.tch_request
-tra.a_if_crc_mismatch_call_setup+ho.msc_i_tch_tch_at
+ho.msc_i_sdcch_tch_at+ho.bsc_i_tch_tch_at
+ho.bsc_i_sdcch_tch_at+ho.cell_tch_tch_at+ho.cell_sdcch_tch_at),0,0,
sum(tra.tch_req_rej_lack-tra.tch_rej_due_req_ch_a_if_crc)/sum(tra.tch_request
-tra.a_if_crc_mismatch_call_setup+ho.msc_i_tch_tch_at
+ho.msc_i_sdcch_tch_at+ho.bsc_i_tch_tch_at+ho.bsc_i_sdcch_tch_at+ho.cell_tch_tch_at+ho.cell_sdcch_tch_at)),2) "TCH擁塞率(含切換)",
round(decode(sum(tra.tch_call_req-tra.a_if_crc_mismatch_call_setup-ho.msc_o_sdcch_tch_at-ho.bsc_o_sdcch_tch_at-ho.cell_sdcch_tch_at),
0,0,sum(tra.ms_tch_succ_seiz_assign_cmplt)/sum(tra.tch_call_req-tra.a_if_crc_mismatch_call_setup-ho.msc_o_sdcch_tch_at-ho.bsc_o_sdcch_tch_at-ho.cell_sdcch_tch_at)),2)"TCH接入性",
sum(tra.tch_call_req)-sum(tra.a_if_crc_mismatch_call_setup)-sum(ho.msc_o_sdcch_tch_at)-sum(ho.bsc_o_sdcch_tch_at)
-sum(ho.cell_sdcch_tch_at)-sum(tra.ms_tch_succ_seiz_assign_cmplt)"TCH接入失敗次數(shù)",
sum(tra.ms_tch_succ_seiz_assign_cmplt)"TCH接入性分子",
sum(tra.tch_call_req-tra.a_if_crc_mismatch_call_setup-ho.msc_o_sdcch_tch_at-ho.bsc_o_sdcch_tch_at-ho.cell_sdcch_tch_at)"TCH接入性分母",
round(decode(sum(serv.sdcch_req),0,0,sum(serv.served_sdcch_req)/sum(serv.sdcch_req)),2) "SD接入性",
sum(serv.sdcch_req)-sum(serv.served_sdcch_req) "SD接入失敗次數(shù)",
sum(serv.served_sdcch_req) "SD接入性分子",
sum(serv.sdcch_req)"SD接入性分母",
sum(tra.sdcch_busy_att) "sd原始擁塞次數(shù)",
sum(tra.sdcch_busy_att-tra.tch_seiz_due_sdcch_con) "sd擁塞次數(shù)",
sum(SDCCH_SEIZ_ATT) as SD試呼數(shù),
round(100*decode(sum(tra.sdcch_seiz_att),0,0,sum(tra.sdcch_busy_att)/sum(tra.sdcch_seiz_att)),2) "SD原始擁塞率",
round(100*decode(sum(tra.sdcch_seiz_att),0,0,
sum(tra.sdcch_busy_att-tra.tch_seiz_due_sdcch_con)/sum(tra.sdcch_seiz_att)),2)"SD擁塞率",
sum(tra.tch_request) "TCH總請(qǐng)求次數(shù)",
sum(tra.tch_norm_seiz)-sum(tra.tch_succ_seiz_for_dir_acc) + sum(ho.msc_i_sdcch_tch+ho.bsc_i_sdcch_tch)+
sum(ho.cell_sdcch_tch)+ sum(tra.tch_seiz_due_sdcch_con)"試呼次數(shù)",
---sum(tch_norm_seiz+tch_ho_seiz) as TCH占用數(shù),
sum(tra.ms_tch_succ_seiz_assign_cmplt+ho.msc_i_tch_tch+ho.msc_i_sdcch_tch+ho.bsc_i_tch_tch+ho.bsc_i_sdcch_tch+ho.cell_tch_tch+ho.cell_sdcch_tch) "TCH占用次數(shù)(含切換)",
sum(tra.ms_tch_succ_seiz_assign_cmplt)"TCH占用次數(shù)(不含切換)",
sum(ava.clear_request_by_bsc_on_tch) "drop2071掉話次數(shù)",
-- sum(ava.spare002072)"drop2072次數(shù)",
round(100*decode(sum(tra.ms_tch_succ_seiz_assign_cmplt+ho.msc_i_tch_tch+ho.msc_i_sdcch_tch+ho.bsc_i_tch_tch+ho.bsc_i_sdcch_tch+ho.cell_tch_tch+ho.cell_sdcch_tch),0,0,
sum(ava.clear_request_by_bsc_on_tch)/sum(tra.ms_tch_succ_seiz_assign_cmplt+ho.msc_i_tch_tch+
ho.msc_i_sdcch_tch+ho.bsc_i_tch_tch+ho.bsc_i_sdcch_tch+ho.cell_tch_tch+ho.cell_sdcch_tch)),2) "drop2071(含切換)",
round(100*decode(sum(tra.ms_tch_succ_seiz_assign_cmplt),0,0,sum(ava.clear_request_by_bsc_on_tch)/sum(tra.ms_tch_succ_seiz_assign_cmplt)),2) "drop2071(不含切換)",
sum(tra.tch_radio_fail+tra.tch_rf_old_ho+tra.tch_abis_fail_call+tra.tch_abis_fail_old+tra.tch_a_if_fail_call+
tra.tch_a_if_fail_old+tra.tch_tr_fail+tra.tch_tr_fail_old+tra.tch_lapd_fail+tra.tch_bts_fail+tra.tch_user_act+
tra.tch_bcsu_reset+tra.tch_netw_act+tra.tch_act_fail_call)"TCH掉話數(shù)",
100*round(decode((sum(tra.tch_norm_seiz) /*(normal calls)*/
+sum(msc_i_sdcch_tch+bsc_i_sdcch_tch+cell_sdcch_tch) /* ;(DR calls)*/
-sum(tra.tch_succ_seiz_for_dir_acc) /*;ref.2*/
+ sum(tra.tch_seiz_due_sdcch_con)/*calls started as FACCH call setup*/
- sum(serv.tch_re_est_assign)),0,0,
(sum(tch_radio_fail+tch_rf_old_ho+tch_abis_fail_call+
tch_abis_fail_old+tch_a_if_fail_call+tch_a_if_fail_old+
tch_tr_fail+tch_tr_fail_old+tch_lapd_fail+
tch_bts_fail+tch_user_act+tch_bcsu_reset+tch_netw_act+
tch_act_fail_call)
-sum(serv.tch_re_est_assign))/*(call re-establishments)*/ /(sum(tra.tch_norm_seiz) /*(normal calls)*/
+sum(msc_i_sdcch_tch+bsc_i_sdcch_tch+cell_sdcch_tch) /* ;(DR calls)*/
-sum(tra.tch_succ_seiz_for_dir_acc) /*;ref.2*/
+sum(tra.tch_seiz_due_sdcch_con)/*calls started as FACCH call setup*/
-sum(serv.tch_re_est_assign))),2)/*(call re-establishments)*/"掉話率dcr_3j",
sum(ho.msc_i_tch_tch_at+ho.msc_i_sdcch_tch_at+ho.msc_i_sdcch_at+
ho.msc_o_tch_tch_at+ho.msc_o_sdcch_tch_at+ho.msc_o_sdcch_at+
ho.bsc_i_tch_tch_at+ho.bsc_i_sdcch_tch_at+ho.bsc_i_sdcch_at+
ho.bsc_o_tch_tch_at+ho.bsc_o_sdcch_tch_at+ho.bsc_o_sdcch_at+
ho.cell_tch_tch_at+ho.cell_sdcch_tch_at+ho.cell_sdcch_at)"全部切換請(qǐng)求數(shù)",
round(100*decode(sum(msc_i_tch_tch_at+msc_i_sdcch_tch_at+msc_i_sdcch_at+
msc_o_tch_tch_at+msc_o_sdcch_tch_at+msc_o_sdcch_at+
bsc_o_tch_tch_at+bsc_o_sdcch_tch_at+bsc_o_sdcch_at+
bsc_i_tch_tch_at+bsc_i_sdcch_tch_at+bsc_i_sdcch_at+
cell_tch_tch_at+cell_sdcch_tch_at+cell_sdcch_at),0,0,
1-sum(msc_i_succ_ho+msc_o_succ_ho+bsc_i_succ_ho+bsc_o_succ_ho+cell_succ_ho)/
sum(msc_i_tch_tch_at+msc_i_sdcch_tch_at+msc_i_sdcch_at+
msc_o_tch_tch_at+msc_o_sdcch_tch_at+msc_o_sdcch_at+
bsc_o_tch_tch_at+bsc_o_sdcch_tch_at+bsc_o_sdcch_at+
bsc_i_tch_tch_at+bsc_i_sdcch_tch_at+bsc_i_sdcch_at+
cell_tch_tch_at+cell_sdcch_tch_at+cell_sdcch_at)),2)"切換失敗率",
sum(ho.msc_o_tch_tch_at + ho.msc_i_tch_tch_at +
ho.bsc_o_tch_tch_at + ho.bsc_i_tch_tch_at + ho.cell_tch_tch_at)"TCH切換請(qǐng)求數(shù)",
sum(ho.msc_o_tch_tch + ho.msc_i_tch_tch + ho.bsc_o_tch_tch + ho.bsc_i_tch_tch + ho.cell_tch_tch)"TCH切換成功次數(shù)",
sum(msc_i_tch_tch_at+bsc_i_tch_tch_at) "tch切入請(qǐng)求數(shù)",
sum(msc_i_tch_tch+bsc_i_tch_tch) "tch切入成功數(shù)",
round(sum(decode(ava.res_av_denom4,0,0,ava.ave_idle_f_tch_1/ava.res_av_denom4)),2)"干擾帶1",
round(sum(decode(ava.res_av_denom5,0,0,ava.ave_idle_f_tch_2/ava.res_av_denom5)),2)"干擾帶2",
round(sum(decode(ava.res_av_denom6,0,0,ava.ave_idle_f_tch_3/ava.res_av_denom6)),2)"干擾帶3",
round(sum(decode(ava.res_av_denom7,0,0,ava.ave_idle_f_tch_4/ava.res_av_denom7)),2)"干擾帶4",
round(sum(decode(ava.res_av_denom8,0,0,ava.ave_idle_f_tch_5/ava.res_av_denom8)),2)"干擾帶5",
round(decode(sum(ava.ave_idle_f_tch_1/ava.res_av_denom4
+ava.ave_idle_f_tch_2/ava.res_av_denom5+ava.ave_idle_f_tch_3/ava.res_av_denom6
+ava.ave_idle_f_tch_4/ava.res_av_denom7+ava.ave_idle_f_tch_5/ava.res_av_denom8),0,0,
1-(sum(ava.ave_idle_f_tch_1/ava.res_av_denom4))/sum(ava.ave_idle_f_tch_1/ava.res_av_denom4
+ava.ave_idle_f_tch_2/ava.res_av_denom5+ava.ave_idle_f_tch_3/ava.res_av_denom6
+ava.ave_idle_f_tch_4/ava.res_av_denom7+ava.ave_idle_f_tch_5/ava.res_av_denom8)),2)out_band1,
sum(tra.tch_radio_fail)tch_radio_fail,
sum(tra.tch_rf_old_ho)tch_rf_old_ho,
sum(tra.tch_abis_fail_call)tch_abis_fail_call,
sum(tra.tch_abis_fail_old) tch_abis_fail_old,
sum(tra.tch_a_if_fail_call)tch_a_if_fail_call,
sum(tra.tch_a_if_fail_old) tch_a_if_fail_old,
sum(tra.tch_tr_fail) tch_tr_fail,
sum(tra.tch_tr_fail_old)tch_tr_fail_old,
sum(tra.tch_lapd_fail)tch_lapd_fail,
sum(tra.tch_bts_fail)tch_bts_fail,
sum(tra.tch_user_act)tch_user_act,
sum(tra.tch_bcsu_reset) tch_bcsu_reset,
sum(tra.tch_netw_act)tch_netw_act,
sum(tra.tch_act_fail_call) tch_act_fail_call,
sum(tra.sdcch_radio_fail)sdcch_radio_fail,
sum(tra.sdcch_rf_old_ho)sdcch_rf_old_ho,
sum(tra.sdcch_user_act) sdcch_user_act,
sum(tra.sdcch_bcsu_reset)sdcch_bcsu_reset,
sum(tra.sdcch_netw_act) sdcch_netw_act,
sum(tra.sdcch_abis_fail_call) sdcch_abis_fail_call,
sum(tra.sdcch_abis_fail_old)sdcch_abis_fail_old,
sum(tra.sdcch_bts_fail) sdcch_bts_fail,
sum(tra.sdcch_lapd_fail)sdcch_lapd_fail,
sum(tra.sdcch_a_if_fail_call) sdcch_a_if_fail_call,
sum(tra.sdcch_a_if_fail_old)sdcch_a_if_fail_old,
sum(tra.sdcch_assign)sdcch_assign,
sum(tra.sdcch_ho_seiz)sdcch_ho_seiz,
sum(tra.sdcch_lu_attempt)sdcch_lu_attempt,
sum(tra.sdcch_lu_attempt_fail)sdcch_lu_attempt_fail,
sum(tra.sdcch_moc_seiz_att)sdcch_moc_seiz_att,
sum(tra.sdcch_mtc_seiz_att)sdcch_mtc_seiz_att,
sum(tra.gprs_ter_upgrd_req)gprs_ter_upgrd_req,
sum(tra.gprs_ter_ug_rej_due_csw_tr) gprs_ter_ug_rej_due_csw_tr,
sum(tra.gprs_ter_ug_rej_due_lack_psw)gprs_ter_ug_rej_due_lack_psw,
sum(tra.gprs_ter_ug_rej_due_lack_pcu)gprs_ter_ug_rej_due_lack_pcu,
sum(tra.gprs_ter_ug_due_dec_csw_tr) gprs_ter_ug_due_dec_csw_tr,
sum(tra.gprs_ter_ug_req_from_csw)gprs_ter_ug_req_from_csw,
sum(tra.gprs_ter_ug_from_csw_partial)gprs_ter_ug_from_csw_partial,
sum(tra.gprs_ter_ug_from_csw_failed)gprs_ter_ug_from_csw_failed,
sum(tra.gprs_ter_downgrade_req)gprs_ter_downgrade_req,
sum(tra.gprs_ter_dg_rej_due_streaming) gprs_ter_dg_rej_due_streaming,
sum(tra.gprs_ter_dg_req_when_eqos_on)gprs_ter_dg_req_when_eqos_on,
sum(tra.gprs_ter_dg_due_inc_in_csw_tr) gprs_ter_dg_due_inc_in_csw_tr,
sum(serv.t3101_expired) t3101_expired
from p_nbsc_traffic tra,p_nbsc_res_avail ava,p_nbsc_ho ho,p_nbsc_service serv,c_bts, objects o_bsc
where tra.period_start_time=ava.period_start_time and tra.period_start_time=ho.period_start_time
and tra.period_start_time=serv.period_start_time
and tra.int_id=ava.int_id and tra.int_id=ho.int_id and tra.int_id=serv.int_id
and tra.bts_int_id=ava.bts_int_id and tra.bts_int_id=ho.bts_int_id
and tra.bts_int_id=serv.bts_int_id
and tra.bts_int_id=c_bts.int_id and tra.int_id=o_bsc.int_id
and c_bts.conf_name='<ACTUAL>' and o_bsc.name <> 'BSC 0'
and tra.period_start_time >= to_date(&Stime,'yyyymmddhh24')
and tra.period_start_time <= to_date(&Etime,'yyyymmddhh24')
--and to_char(tra.period_start_time,'yyyymmdd')>='20110613'
--and to_char(tra.period_start_time,'yyyymmdd')<='20110613'
--and to_char(tra.period_start_time,'hh24')in (8,9,10,18,19,20)
--and c_bts.cell_id in &ci
--and c_bts.cell_id in (19526,16034)
and o_bsc.name like 'NC%'
--and o_bsc.name in ('NCBSC52G')
group by tra.period_start_time,o_bsc.name,c_bts.la_id_lac,c_bts.cell_id,c_bts.segment_name,tra.segment_id
having(
(sum(tra.tch_call_req)-sum(tra.a_if_crc_mismatch_call_setup)-sum(ho.msc_o_sdcch_tch_at)-sum(ho.bsc_o_sdcch_tch_at)
-sum(ho.cell_sdcch_tch_at)-sum(tra.ms_tch_succ_seiz_assign_cmplt) >100 )


or
( sum(serv.sdcch_req)-sum(serv.served_sdcch_req) >500 )
)