【资料名称】：日常监控_语音

【资料作者】：yy

【资料日期】：2013-5-7

【资料语言】：英文

【资料格式】：TXT

【资料目录和简介】：

nsn sql 脚本
select
r1.pdate
,r1.BSC
,r1.SEG
,r1.CI
,r4.area
,r3.Traffic
,r3.erl_line
,r2.DCN
,r2.DCR
,r1.SD_BLK_Nub
,r1.SD_BLK
,r1.TCH_BLK_Nub
,r1.TCH_BLK
,r2.新无线接通率
,r3.outband1
,r4.上行质量
,r4.下行质量



from
(select
to_char(tra.period_start_time,'yyyymmddhh24') pdate
,to_char(tra.period_start_time,'yyyymmddhh24')||c.cell_id ptfile
,o_bsc.name BSC
,c.segment_id SEG
,c.cell_id CI
,sum(tra.sdcch_busy_att) SD_BLK_Nub
,round(100*decode(sum(tra.sdcch_seiz_att),0,0,sum(tra.sdcch_busy_att)/sum(tra.sdcch_seiz_att)),2) SD_BLK
,sum(tra.tch_call_req-tra.tch_norm_seiz-ho.msc_o_sdcch_tch+ho.msc_controlled_in_ho+ho.ho_unsucc_a_int_circ_type) TCH_BLK_Nub
,round(100*decode(sum(tra.tch_call_req-tra.tch_rej_due_req_ch_a_if_crc-ho.bsc_i_unsucc_a_int_circ_type+ho.msc_controlled_in_ho+ho.ho_unsucc_a_int_circ_type),
0,0,sum(tra.tch_call_req-tra.tch_norm_seiz-ho.msc_o_sdcch_tch+ho.msc_controlled_in_ho+ho.ho_unsucc_a_int_circ_type)/
sum(tra.tch_call_req-tra.tch_rej_due_req_ch_a_if_crc-ho.bsc_i_unsucc_a_int_circ_type+ho.msc_controlled_in_ho+ho.ho_unsucc_a_int_circ_type)),2) TCH_BLK

from
p_nbsc_traffic tra
,p_nbsc_ho ho
,c_bts c
,objects o_bsc

where
tra.bts_int_id=ho.bts_int_id
and tra.bts_int_id=c.int_id
and tra.int_id=o_bsc.int_id
and o_bsc.object_class=3
and tra.period_start_time=ho.period_start_time
and o_bsc.name like 'SQBSC%'
and to_char(tra.period_start_time,'yyyymmddhh24') between &Start and &End
and c.cell_id in &CI

group by
to_char(tra.period_start_time,'yyyymmddhh24')
,o_bsc.name
,c.segment_id
,c.cell_id

) r1,

(select
to_char(tra.period_start_time,'yyyymmddhh24') S_date
,to_char(tra.period_start_time,'yyyymmddhh24')||c_bts.cell_id ptfile
,c_bts.cell_id CI
,o_bsc.name BSC
,c_bts.segment_id SEG
,concat(round(100*decode(sum(ser.sdcch_req),0,0,sum(ser.served_sdcch_req)/sum(ser.sdcch_req))*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),'%') as 新无线接通率
,concat(round(100*decode(sum(ser.sdcch_req),0,0,sum(ser.served_sdcch_req)/sum(ser.sdcch_req)),2),'%') SDCCH接通率
,concat(round(100*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(ser.served_sdcch_req) SDCCH占用次数
,sum(ser.sdcch_req) SDCCH试呼次数
,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(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)- sum(ser.tch_re_est_assign),2) DCN --3j掉话
,concat(decode(sum(tra.tch_norm_seiz) + sum(ho.msc_i_sdcch_tch+ ho.bsc_i_sdcch_tch+ ho.cell_sdcch_tch) - sum(tra.tch_succ_seiz_for_dir_acc)+ sum(tra.tch_seiz_due_sdcch_con)- sum(ser.tch_re_est_assign),0,0,round(100*(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)- sum(ser.tch_re_est_assign))/(sum(tra.tch_norm_seiz) + sum(ho.msc_i_sdcch_tch+ ho.bsc_i_sdcch_tch+ ho.cell_sdcch_tch) - sum(tra.tch_succ_seiz_for_dir_acc)+ sum(tra.tch_seiz_due_sdcch_con)- sum(ser.tch_re_est_assign)),2)),'%') DCR--掉话率3j


from
c_bts
,objects o_bts
,objects o_bsc
,p_nbsc_traffic tra
,p_nbsc_service ser
,p_nbsc_ho ho

where
tra.bts_int_id=c_bts.int_id
and tra.bts_int_id=o_bts.int_id
and tra.int_id=o_bsc.int_id
and tra.period_start_time=ser.period_start_time
and tra.int_id=ser.int_id
and tra.bts_int_id=ser.bts_int_id
and tra.period_start_time=ho.period_start_time
and tra.int_id=ho.int_id
and tra.bts_int_id=ho.bts_int_id
and c_bts.conf_name = '<ACTUAL>'
and o_bsc.name like 'SQBSC%'
and c_bts.cell_id in &CI
and to_char(tra.period_start_time,'yyyymmddhh24') between &Start and &End

group by
to_char(tra.period_start_time,'yyyymmddhh24')
,c_bts.cell_id
,o_bsc.name
,c_bts.segment_id

order by 1

) r2,

(select
to_char(res.period_start_time,'yyyymmddhh24') S_date
,to_char(res.period_start_time,'yyyymmddhh24')||c_bts.cell_id ptfile
,c_bts.cell_id CI
,o_bsc.name BSC
,round(sum(res.ave_busy_tch/res.res_av_denom14),2) Traffic
,round(decode(sum(res.ave_avail_tch_sum/res.ave_avail_tch_den),0,0,sum(res.ave_busy_tch/res.res_av_denom14)/sum(res.ave_avail_tch_sum/res.ave_avail_tch_den)),2) erl_line
,concat(decode(sum(res.AVE_IDLE_F_TCH_1/res.res_av_denom4)+sum(res.AVE_IDLE_F_TCH_2/res.res_av_denom5)+sum(res.AVE_IDLE_F_TCH_3/res.res_av_denom6)+sum(res.AVE_IDLE_F_TCH_4/res.res_av_denom7)+sum(res.AVE_IDLE_F_TCH_5/res.res_av_denom8),0,0,round(100*(1-sum(res.AVE_IDLE_F_TCH_1/res.res_av_denom4)/(sum(res.AVE_IDLE_F_TCH_1/res.res_av_denom4)+sum(res.AVE_IDLE_F_TCH_2/res.res_av_denom5)+sum(res.AVE_IDLE_F_TCH_3/res.res_av_denom6)+sum(res.AVE_IDLE_F_TCH_4/res.res_av_denom7)+sum(res.AVE_IDLE_F_TCH_5/res.res_av_denom8))),2)),'%') outband1

from

c_bts
,objects o_bts
,objects o_bsc
,p_nbsc_res_avail res

where

res.bts_int_id=o_bts.int_id
and res.bts_int_id=c_bts.int_id
and res.int_id=o_bsc.int_id
and c_bts.conf_name='<ACTUAL>'
and o_bsc.name like 'SQBSC%'
and to_char(res.period_start_time,'yyyymmddhh24') between &Start and &End
and c_bts.cell_id in &CI

group by
to_char(res.period_start_time,'yyyymmddhh24')
,c_bts.cell_id
,o_bsc.name

order by 1

) r3,
(SELECT
to_char(rx.period_start_time,'yyyymmddhh24') sdate
,to_char(rx.period_start_time,'yyyymmddhh24')||c_bts.cell_id ptfile
,bsc.nameBSC
,c_bts.segment_id SEG
,c_bts.cell_idCI
,substr(c_bts.segment_name,1,2)area
,concat(round(decode(sum(rx.freq_ul_qual0+rx.freq_ul_qual1+rx.freq_ul_qual2+rx.freq_ul_qual3+rx.freq_ul_qual4+rx.freq_ul_qual5+rx.freq_ul_qual6+rx.freq_ul_qual7),0,0,100*sum(rx.freq_ul_qual0+rx.freq_ul_qual1+rx.freq_ul_qual2+rx.freq_ul_qual3+rx.freq_ul_qual4+rx.freq_ul_qual5)/sum(rx.freq_ul_qual0+rx.freq_ul_qual1+rx.freq_ul_qual2+rx.freq_ul_qual3+rx.freq_ul_qual4+rx.freq_ul_qual5+rx.freq_ul_qual6+rx.freq_ul_qual7)),2),'%') 上行质量
,concat(round(decode(sum(rx.freq_dl_qual0+rx.freq_dl_qual1+rx.freq_dl_qual2+rx.freq_dl_qual3+rx.freq_dl_qual4+rx.freq_dl_qual5+rx.freq_dl_qual6+rx.freq_dl_qual7),0,0,100*sum(rx.freq_dl_qual0+rx.freq_dl_qual1+rx.freq_dl_qual2+rx.freq_dl_qual3+rx.freq_dl_qual4+rx.freq_dl_qual5)/sum(rx.freq_dl_qual0+rx.freq_dl_qual1+rx.freq_dl_qual2+rx.freq_dl_qual3+rx.freq_dl_qual4+rx.freq_dl_qual5+rx.freq_dl_qual6+rx.freq_dl_qual7)),2),'%') 下行质量
,round(-110+sum(pow.ave_dl_sig_str)/sum(pow.power_denom3),2) DL_sig_str
,round(-110+sum(pow.ave_ul_sig_str)/sum(pow.power_denom4),2) UL_sig_str
from
objectsbsc
,objectsbcf
,objectsbts
,c_bts
,p_nbsc_rx_qualrx
,p_nbsc_power pow


where
rx.int_id=bsc.int_id
and rx.bts_int_id=bts.int_id
and rx.bts_int_id=c_bts.int_id
and c_bts.int_id=bts.int_id
and bts.parent_int_id=bcf.int_id
and bcf.parent_int_id=bsc.int_id
and c_bts.conf_name = '<ACTUAL>'
and bsc.name like 'SQBSC%'
--p表关联
and rx.period_start_time=pow.period_start_time
and rx.int_id=pow.int_id
and to_char(rx.period_start_time,'yyyymmddhh24') between &Start and &End
and c_bts.cell_id in &CI

group by
to_char(rx.period_start_time,'yyyymmddhh24')
,substr(c_bts.segment_name,1,2)
,bsc.name
,c_bts.segment_id
,c_bts.cell_id
order by 1

) r4


where
r1.ptfile=r2.ptfile
and r1.ptfile=r3.ptfile
and r1.ptfile=r4.ptfile

group by
r1.pdate
,r1.BSC
,r1.SEG
,r1.CI
,r3.Traffic
,r3.erl_line
,r1.SD_BLK_Nub
,r1.SD_BLK
,r1.TCH_BLK_Nub
,r1.TCH_BLK
,r2.新无线接通率
,r3.outband1
,r4.area
,r4.上行质量
,r4.下行质量
,r2.DCN
,r2.DCR