【資料名稱】：SQL腳本-BSC_Performanc

【資料作者】：未知

【資料日期】：未知

【資料語言】：中文

【資料格式】：其它

【資料目錄和簡介】：

NSN-SQL腳本-BSC_Performanc
供新手學習

SELECT bsC.name bsc,
--bcf.object_instance bcf_id,
--bts.object_instance bts_id,
-- C.LA_ID_LAC,
-- C.CELL_ID,
--bts.name bts_name,
to_char(t.period_start_time,'mmddhh24') PERIOD_DATE,


/* round(100* ( 1 - decode(sum(S.setup_succ+S.call_setup_failure),0,0,sum(S.call_setup_failure)/sum(S.setup_succ+S.call_setup_failure))),2) CSSR, */


/* round(100*decode(sum(R.res_av_denom14),0,0,sum(R.ave_busy_tch)/sum(R.res_av_denom14))/decode(sum(R.ave_avail_tch_den),0,0,ROUND(sum(R.ave_avail_tch_sum)/sum(R.ave_avail_tch_den),2)),2) TCH_usage,*/

/* round(100*(decode(sum(R.res_av_denom14),0,0,sum(R.ave_busy_tch)/sum(R.res_av_denom14))+decode(sum(R.ave_GPRS_channels_den),0,0,round(sum(R.ave_GPRS_channels_sum)/sum(R.ave_GPRS_channels_den),2)))/(decode(sum(R.ave_avail_tch_den),0,0,ROUND(sum(R.ave_avail_tch_sum)/sum(R.ave_avail_tch_den),2))+decode(sum(R.ave_avail_tch_den),0,0,ROUND(sum(R.ave_avail_tch_sum)/sum(R.ave_avail_tch_den),2))),2) chan_usage,*/


/*round((avg(decode(R.RES_AV_DENOM2,0,0,R.AVE_AVAIL_FULL_TCH/R.RES_AV_DENOM2))+avg(R.AVE_TCH_AVAIL_HALF/2)),2) TCH_total,

round(avg(R.AVE_TCH_AVAIL_HALF),2)TCH_HR,

decode(sum(R.ave_avail_tch_den),0,0,ROUND(sum(R.ave_avail_tch_sum)/sum(R.ave_avail_tch_den),2)) TCH_num,

round((decode(sum(R.ave_avail_tch_den),0,0,sum(R.ave_avail_tch_sum)/sum(R.ave_avail_tch_den))+decode(sum(R.ave_GPRS_channels_den),0,0,sum(R.ave_GPRS_channels_sum)/sum(R.ave_GPRS_channels_den)))-decode(sum(R.ave_GPRS_channels_den),0,0,round(sum(R.ave_GPRS_channels_sum)/sum(R.ave_GPRS_channels_den),2))-avg(R.AVE_TCH_BUSY_HALF)/2-avg(R.AVE_TCH_BUSY_FULL),2) Idle_TCH_NUM,

round(avg(R.AVE_NON_AVAIL_TCH),2) NON_AVAIL_TCH,

decode(sum(R.ave_GPRS_channels_den),0,0,round(sum(R.ave_GPRS_channels_sum)/sum(R.ave_GPRS_channels_den),2)) average_GPRS_TS,

sum( R.PEAK_GPRS_CHANNELS) peak_GPRS_TS,*/

round(avg(R.AVE_NON_AVAIL_TSL/R.RES_AV_DENOM1/8),0) TRX_BLK,
decode(SUM(T.TCH_CALL_REQ),0,0,round(100*SUM(T.QUE_ALL_ASS_REQ_FAIL +T.QUE_NALL_ASS_REQ_FAIL)/SUM(T.TCH_CALL_REQ),2)) TCH_BLK_HO_EXCLUDED,--TCH_擁塞不含切換
round(decode(sum(t.tch_call_req),0,0,greatest((sum(t.tch_call_req)-sum(t.tch_norm_seiz)-sum(h.msc_o_sdcch_tch+ h.bsc_o_sdcch_tch+h.cell_sdcch_tch)),0)/sum(t.tch_call_req)*100),2) TCH_BLK_HO_INCLUDED,--TCH_擁塞含奇幻



round(decode(sum(R.ave_busy_tch/R.res_av_denom14),0,0,100*SUM(R.AVE_TCH_BUSY_HALF)/sum(R.ave_busy_tch/R.res_av_denom14)),2)HR_traffic_percentage,--半速率比例

round(SUM(R.AVE_TCH_BUSY_HALF),2) tch_traffic_half,


round(SUM(R.AVE_TCH_BUSY_FULL),2) tch_traffic_full,

round(sum(R.ave_busy_tch/R.res_av_denom14),2) as TCH_TRAFFIC,--話務量,

round(decode(sum(T.tch_call_req-T.a_if_crc_mismatch_call_setup-H.msc_o_sdcch_tch_at-H.cell_sdcch_tch_at-H.bsc_o_sdcch_tch_at),0,0,sum(T.ms_tch_succ_seiz_assign_cmplt)/sum(T.tch_call_req-T.a_if_crc_mismatch_call_setup-H.msc_o_sdcch_tch_at-H.cell_sdcch_tch_at-H.bsc_o_sdcch_tch_at))*100,2) as jietonglv, --接通率,

decode((sum(decode(R.res_av_denom2,0,0,R.ave_avail_full_TCH/R.res_av_denom2))+sum(decode(R.res_av_denom1,0,0,R.ave_non_avail_tsl/R.res_av_denom1))),0,0,round(Sum(decode(R.res_av_denom2,0,0,100*R.ave_avail_full_TCH/R.res_av_denom2))/(sum(decode(R.res_av_denom2,0,0,R.ave_avail_full_TCH/R.res_av_denom2))+sum(decode(R.res_av_denom1,0,0,R.ave_non_avail_tsl/R.res_av_denom1))),2)) as TCH_avail_percentage, --利用率,

sum(R.CLEAR_REQUEST_BY_BSC_ON_TCH) as c2071, --掉話次數,
sum(T.ms_tch_succ_seiz_assign_cmplt+H.bsc_i_tch_tch+H.bsc_i_sdcch_tch+H.msc_i_tch_tch+H.msc_i_sdcch_tch+H.cell_tch_tch+H.cell_sdcch_tch) as CALLS,

sum(T.tch_radio_fail + T.tch_rf_old_ho + T.tch_tr_fail +
T.tch_tr_fail_old + T.tch_abis_fail_call + T.tch_abis_fail_old +
T.tch_a_if_fail_call + T.tch_a_if_fail_old + T.tch_lapd_fail +
T.tch_bts_fail + T.tch_user_act + T.tch_bcsu_reset +
T.tch_netw_act + T.tch_act_fail_call) TCH_Drops,--TCH掉話次數，3J


請下載附件查看