发布时间 : 星期四 文章诺西LTE指标提取SQL语句20160801更新完毕开始阅读
,Round(Decode(sum(M8009C6+M8014C0+M8014C14),0,0,
100*sum(M8009C7+M8014C7+M8014C19)/sum(M8009C6+M8014C0+M8014C14)),2) 切换成功率2_集团公式 --集团考核指标,报给集团的公式,该公式被亿阳网管使用。该公式存在两个错误,X2中使用了错误的分母:M8014C0,该值为切换准备次数,S1中使用了错误的分母M8014C14,nokia官方没有这个公式
,Round(avg(M8001C199),1) RRC连接平均数 ,max(M8001C200) RRC连接最大数
,Round(Decode(sum(M8001C217),0,0,sum(M8011C50/(15*60*1000*1/(1+4)))/sum(M8001C217)*100),2) 上行业务PRB占用率
,Round(Decode(sum(M8001C216),0,0,max(M8011C54-438800)/(15*60*1000*4/(1+4))/sum(M8001C216)*100),2) 下行业务PRB占用率
FROM (
SELECT M8013.sdatetime
-- ,M8020.MRBTS_ID -- ,M8020.LNBTS_ID -- ,M8020.LNCEL_ID
,enb_id || '_' || cell_id enb_cell ,enb_id ,cell_id ,bts_ip
,bts_version ,bts_name ,cel_name
,m8001C199,M8001C153,M8001C154,M8001C200,M8001C223,M8001C254,M8001C259,M8001C318,M8001C319,M8001C269,M8001C217,M8001C216,M8001C147,M8001C150,M8001C148,M8001C151 ,M8006C0,M8006C1,M8006C6,M8006C7,M8006C8,M8006C9,M8006C10,M8006C12,M8006C13,M8006C14,M8006C15,M8006C17,M8006C18,M8006C26,M8006C35,M8006C36,M8006C44,M8006C89,M8006C98
,M8006C45,M8006C46,M8006C47,M8006C48,M8006C49,M8006C50,M8006C51,M8006C52,M8006C53,M8006C54 ,M8006C107,M8006C116,M8006C125,M8006C134,M8006C143,M8006C152,M8006C161,M8006C162,M8006C163,M8006C164,M8006C165,M8006C166,M8006C167,M8006C168,M8006C169,M8006C170,M8006C171,M8006C172,M8006C173,M8006C176,M8006C177,M8006C178,M8006C179,M8006C180 ,M8008C0,m8008c1,m8008c2,M8008C4,M8008C5 ,M8009C2,M8009C6,M8009C7
,M8011C50,M8011C54,M8011C37,M8011C24,M8011C50avg,M8011C54avg,M8011C47,M8011C51
,M8011C38,M8011C39,M8011C40,M8011C41,M8011C42
,M8012C19,M8012C20,M8012C92,M8012C94,M8012C96,M8012C98,M8012C100,M8012C102,M8012C104,M8012C106,M8012C108,M8012C118,M8012C120,M8012C122,M8012C124,M8012C126,M8012C128
,M8012C130,M8012C132,M8012C134
,M8013C5,M8013C9,M8013C10,M8013C11,M8013C12,M8013C13,M8013C15,M8013C16,M801
28
3C17,M8013C18,M8013C19,M8013C20,M8013C21,M8013C31,M8013C34
,M8014C0,M8014C6,M8014C7,M8014C8,M8014C14,M8014C18,M8014C19,M8014C20 ,M8015C2,M8015C9,M8015C8,M8015C5,M8015C6,M8015C7
,M8016C11,M8016C14,M8016C21,M8016C23,M8016C25,M8016C26,M8016C27,M8016C29,M8016C30,M8020C3,M8020C6,M8020C4 FROM ( select
to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID
,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID cel_key_id ,sum(PDCP_SDU_UL) M8001C153 ,sum(PDCP_SDU_DL) M8001C154
,avg(nvl(RRC_CONN_UE_AVG,0)) M8001C199 --The average number of UEs in RRC_CONNECTED state over the measurement period. The average value is the arithmetical average of samples taken from the number of UEs in RRC_CONNECTED state.
,max(nvl(RRC_CONN_UE_MAX,0)) M8001C200 --The highest value for number of UEs in RRC_CONNECTED state over the measurement period.
,avg(nvl(CELL_LOAD_ACT_UE_AVG,0)) M8001C223 --The average number of active UE per cell during measurement period. A UE is termed active if at least a single non-GBR DRB has been successfully configured for it.
,sum(PDCP_SDU_LOSS_UL) M8001C254 ---Number of missing UL PDCP packets of a data bearer that are not delivered to higher layers.
,sum(PDCP_SDU_LOSS_DL) M8001C259 ---Number of DL PDCP SDUs that could not be successfully transmitted.
,sum(SUM_RRC_CONN_UE) M8001C318 ,sum(DENOM_RRC_CONN_UE) M8001C319 ,avg(nvl(PDCP_RET_DL_DEL_MEAN_QCI_1,0)) M8001C269 ,avg(nvl(MEAN_PRB_AVAIL_PUSCH,0)) M8001C217 ,avg(nvl(MEAN_PRB_AVAIL_PDSCH,0)) M8001C216 ,avg(nvl(DL_UE_DATA_BUFF_AVG,0)) M8001C147 ,avg(nvl(UL_UE_DATA_BUFF_AVG,0)) M8001C150 ,max(nvl(DL_UE_DATA_BUFF_MAX,0)) M8001C148 ,max(nvl(UL_UE_DATA_BUFF_MAX,0)) M8001C151
FROM NOKLTE_PS_LCELLD_MNC1_RAW PMRAW where
---to_char(period_start_time,'yyyymmddHH24mi') >= to_char(SYSDATE-1,'yyyymmddHH24mi') period_start_time between to_date(&1,'yyyymmddHH24mi') and to_date(&2,'yyyymmddHH24mi') -- and to_char(period_start_time,'yyyymmddHH24mi') <= to_char(SYSDATE-1,'yyyymmddHH24mi') --AND PERIOD_DURATION=15 group by
to_char(period_start_time,'yyyymmddHH24mi'),LNCEL_ID,to_char(period_start_time,'yyyymmddHH24mi')||LNC
29
EL_ID )M8001 ,(
select
to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID
,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID cel_key_id
,sum(nvl(EPS_BEARER_SETUP_ATTEMPTS,0)) M8006C0 --The number of EPS bearer setup attempts. Each bearer of the E-RAB to Be Setup List IE is counted.
,sum(nvl(EPS_BEARER_SETUP_COMPLETIONS,0)) M8006C1 --The number of EPS bearer setup completions. Each bearer of the E-RAB Setup List IE is counted.
,sum(nvl(EPS_BEARER_SETUP_FAIL_RNL,0)) M8006C2 --The number of EPS bearer setup failures due to Radio Network Layer. Each bearer of the E-RAB Failed to Setup List IE is counted. ,sum(nvl(EPS_BEARER_SETUP_FAIL_TRPORT,0)) M8006C3 --The number of EPS bearer setup failures due to Transport Layer. Each bearer of the E-RAB Failed to Setup List IE is counted. ,sum(nvl(EPS_BEARER_SETUP_FAIL_RESOUR,0)) M8006C4 --The number of EPS bearer setup failures due to Resource reasons. Each bearer of the E-RAB Failed to Setup List IE has to be counted. ,sum(nvl(EPS_BEARER_SETUP_FAIL_OTH,0)) M8006C5 --The number of EPS bearer setup failures due to Other reasons. Each bearer of the E-RAB Failed to Setup List IE is counted. ,sum(nvl(EPC_EPS_BEARER_REL_REQ_NORM,0)) M8006C6 --The number of released Data Radio Bearers due to normal release per call. Each bearer of the E-RAB To Be Released List IE has to be counted. In case of a UE context release request, all established EPS Bearers are counted. ,sum(nvl(EPC_EPS_BEARER_REL_REQ_DETACH,0)) M8006C7 --The number of EPC-initiated EPS Bearer Release requests due to the Detach procedure by the UE or MME (NAS cause). Each bearer of the E-RAB To Be Released List IE has to be counted. In case of a UE context release request, all established EPS Bearers are counted.
,sum(nvl(EPC_EPS_BEARER_REL_REQ_RNL,0)) M8006C8 --The number of EPC-initiated EPS Bearer Release requests due to the Radio Network Layer cause. Each bearer of the E-RAB to be Released List IE is counted.
,sum(nvl(EPC_EPS_BEARER_REL_REQ_OTH,0)) M8006C9 --The number of released Data-Radio Bearers due to Other Reasons. Each bearer of the E-RAB To Be Released List IE has to be counted. In case of a UE context release request, all established EPS Bearer are counted. ,sum(nvl(ENB_EPS_BEARER_REL_REQ_NORM,0)) M8006C10 --The number of eNB-initiated EPS Bearer Release requests due to the UE inactivity. In case of the UE context release request, all the established EPS Bearers are counted.
,sum(nvl(ENB_EPS_BEARER_REL_REQ_RNL,0)) M8006C12 --The number of E-RABs requested to be released in case a Radio Link Failure is detected by eNB. ,sum(nvl(ENB_EPS_BEARER_REL_REQ_OTH,0)) M8006C13 --The number of eNB-initiated EPS Bearer Release requests due to Other causes . In case of a UE context release request, all the established EPS Bearers are counted.
,sum(nvl(ENB_EPS_BEARER_REL_REQ_TNL,0)) M8006C14 --The number of eNB-initiated EPS Bearer Release requests due to Transport Layer Cause
,sum(nvl(ENB_EPSBEAR_REL_REQ_RNL_REDIR,0)) M8006C15 --The number of eNB-initiated EPS Bearer Release requests due Redirect (release due to RNL E-UTRAN generated reason or RNL Inter-RAT Redirection). ,sum(nvl(EPS_BEARER_SETUP_FAIL_HO,0)) M8006C16 --The number of EPS bearer setup failures due to Handover Pending reason. Each bearer of the E-RAB Failed to Setup List IE is counted.
30
,sum(nvl(EPS_BEARER_STP_ATT_INI_QCI_1,0)) M8006C17 --The number of initial EPS bearer setup attempts per QCI1. Each bearer of the E-RAB to Be Setup List IE is counted.
,sum(nvl(EPS_BEAR_STP_ATT_INI_NON_GBR,0)) M8006C18 --The number of initial EPS bearer setup attempts per non-GBR. Each bearer of the E-RAB to Be Setup List IE is counted.
,sum(nvl(EPS_BEARER_STP_ATT_ADD_QCI_1,0)) M8006C26 --The number of additional EPS bearer setup attempts per QCI1. Each bearer of the E-RAB to Be Setup List IE is counted.
,sum(nvl(EPS_BEARER_STP_COM_INI_QCI1,0)) M8006C35 --The number of initial EPS bearer setup completions per QCI1. Each bearer of the E-RAB Setup List IE is counted.
,sum(nvl(EPS_BEAR_STP_COM_INI_NON_GBR,0)) M8006C36 --The number of initial EPS bearer setup completions per non-GBR. Each bearer of the E-RAB Setup List IE is counted.
,sum(nvl(EPS_BEAR_SET_COM_ADDIT_QCI1,0)) M8006C44 --The number of additional EPS bearer setup completions for QCI1. Each bearer of the E-RAB Setup List IE is counted.
,sum(nvl(EPC_EPS_BEAR_REL_REQ_N_QCI1,0)) M8006C89 --The number of EPC-initiated EPS Bearer Release requests for QCI1 due to normal release by UE. Each bearer of the E-RAB to be Released List IE is counted.
,sum(nvl(EPC_EPS_BEAR_REL_REQ_D_QCI1,0)) M8006C98 --The number of EPC-initiated EPS Bearer Release requests for QCI1 due to the Detach procedure by the UE or the MME. Each bearer of the E-RAB to be Released List IE is counted.
,sum(nvl(EPC_EPS_BEAR_REL_REQ_R_QCI1,0)) M8006C107 --The number of EPC-initiated EPS Bearer Release requests for QCI1 due to the Radio Network Layer cause. Each bearer of the E-RAB to be Released List IE is counted.
,sum(nvl(EPC_EPS_BEAR_REL_REQ_O_QCI1,0)) M8006C116 --The number of EPC-initiated EPS Bearer Release requests for QCI1 due to Other causes. Each bearer of the E-RAB to be Released List IE is counted. ,sum(nvl(ENB_EPS_BEAR_REL_REQ_N_QCI1,0)) M8006C125 --The number of eNB-initiated EPS Bearer Release requests for QCI1 due to the Normal release. In case of a UE context release request, all the established EPS Bearers are counted.
,sum(nvl(ENB_EPS_BEAR_REL_REQ_R_QCI1,0)) M8006C134 --The number of eNB-initiated EPS Bearer Release requests for QCI1 due to Radio Network Layer cause. In case of a UE context release request, all the established EPS Bearers are counted. ,sum(nvl(ENB_EPS_BEAR_REL_REQ_O_QCI1,0)) M8006C143 --The number of eNB-initiated EPS Bearer Release requests for QCI1 due to Other causes . In case of a UE context release request, all the established EPS Bearers are counted. ,sum(nvl(ENB_EPS_BEAR_REL_REQ_T_QCI1,0)) M8006C152 --The number of eNB-initiated EPS Bearer Release requests for QCI1 due Transport Layer Cause - Transport Resource UnavailableCause. ,sum(nvl(ENB_EPS_BEAR_REL_REQ_RD_QCI1,0)) M8006C161 --The number of eNB-initiated EPS Bearer Release requests for QCI1 due Redirect (release due to RNL E-UTRAN generated reason or RNL Inter-RAT Redirection )
,sum(nvl(EPS_BEARER_STP_ATT_INI_QCI_2,0)) M8006C162 --This measurement provides the number of initial EPS bearer setup attempts for GBR DRBs of QCI2 characteristics.
,sum(nvl(EPS_BEARER_STP_ATT_INI_QCI_3,0)) M8006C163 --This measurement provides the number of initial EPS bearer setup attempts for GBR DRBs of QCI3 characteristics.
,sum(nvl(EPS_BEARER_STP_ATT_INI_QCI_4,0)) M8006C164 --This measurement provides the number of initial EPS bearer setup attempts for GBR DRBs of QCI4 characteristics.
,sum(nvl(EPS_BEARER_STP_ATT_ADD_QCI_2,0)) M8006C165 --This measurement provides the number of additional EPS bearer setup attempts for GBR DRBs of QCI2 characteristics.
,sum(nvl(EPS_BEARER_STP_ATT_ADD_QCI_3,0)) M8006C166 --This measurement provides the number of
31