诺西LTE指标提取SQL语句20160801-南京廖华答案网

诺西LTE指标提取SQL语句20160801 下载本文

,sum(M8006C206+M8006C215+decode(M8006C54,0,0,ceil(M8006C45/M8006C54))) \掉线率分母(QCI=1)\

,sum(M8006C177) \异常释放次数(QCI=2)\

,sum(M8006C207+M8006C216+decode(M8006C54,0,0,ceil(M8006C46/M8006C54))) \掉线率分母(QCI=2)\

,AVG(M8008C17) \连接建立时长\,AVG(M8007C14) \建立时长\

,avg(M8011C50)/17280000 \上行PRB利用率\,avg(M8011C54)/69120000 \下行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

,EU0427,M8001C305,M8001C314,M8001C323,M8001C306,M8001C315,M8001C324,M8001C269,M8001C419,M8001C227

,V8006C1,V8006C2,V8006C3,V8006C4,V8006C5,V8006C6,V8006C7,V8006C8,V8006C9,V8006C10,V8006C11,V8006C12 ,EU0205,EU0507,EU0508,EU0509,EU0510,EU0515,EU0516 ,M8006C45,M8006C46,M8006C54,M8006C143,M8006C152,M8006C176,M8006C177,M8006C188,M8006C189,M8006C192,M8006C196,M8006C197,M8006C198,M8006C201,M8006C205 ,M8006C206,M8006C207,M8006C210,M8006C214,M8006C215,M8006C216,M8006C219,M8006C223

,M8007C14 ,M8008C17

,M8026C255,M8026C260,M8026C256,M8026C261 ,M8011C50,M8011C54 ,M8012C116,M8012C143

,M8013C5,M8013C17,M8013C18,M8013C19,M8013C21,M8013C26,M8013C31,M8013C34 ,M8016C34,M8016C33

FROM

( select

to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id

,AVG(nvl(PDCP_RET_DL_DEL_MEAN_QCI_2,0)) as EU0427 ,sum(nvl(PDCP_SDU_UL_QCI_1,0)) as M8001C305 ,sum(nvl(PDCP_SDU_DL_QCI_1,0)) as M8001C314

,sum(nvl(PDCP_SDU_DISC_DL_QCI_1,0)) as M8001C323 ,sum(nvl(PDCP_SDU_UL_QCI_2,0)) as M8001C306 ,sum(nvl(PDCP_SDU_DL_QCI_2,0)) as M8001C315

,sum(nvl(PDCP_SDU_DISC_DL_QCI_2,0)) as M8001C324

,avg(nvl(PDCP_RET_DL_DEL_MEAN_QCI_1,0)) as M8001C269 ,sum(nvl(UE_DRB_UL_DATA_QCI_1,0)) as M8001C419 ,sum(nvl(UE_DRB_DL_DATA_QCI_1,0)) as M8001C227

FROM NOKLTE_PS_LCELLD_lncel_hour PMRAW where

---to_char(period_start_time,'yyyymmddHH24') >= to_char(SYSDATE-1,'yyyymmddHH24') period_start_time between to_date(&1,'yyyymmddhh24') and to_date(&2,'yyyymmddhh24') -- and to_char(period_start_time,'yyyymmddHH24') <= to_char(SYSDATE-1,'yyyymmddHH24') --AND PERIOD_DURATION=15 group by

to_char(period_start_time,'yyyymmddhh24'),LNCEL_ID,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID )M8001 ,(

select

to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID ,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id

,SUM(nvl(ERAB_INI_SETUP_SUCC_QCI1,0)) V8006C1 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI1,0)) V8006C2 ,SUM(nvl(ERAB_INI_SETUP_ATT_QCI1,0)) V8006C3 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI1,0)) V8006C4 ,SUM(nvl(ERAB_INI_SETUP_SUCC_QCI2,0)) V8006C5 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI2,0)) V8006C6 ,SUM(nvl(ERAB_INI_SETUP_ATT_QCI2,0)) V8006C7 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI2,0)) V8006C8 ,SUM(nvl(ERAB_INI_SETUP_SUCC_QCI5,0)) V8006C9 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI5,0)) V8006C10 ,SUM(nvl(ERAB_INI_SETUP_ATT_QCI5,0)) V8006C11 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI5,0)) V8006C12

,sum(nvl(SUM_SIMUL_ERAB_QCI_1,0)) as M8006C45 ,sum(nvl(SUM_SIMUL_ERAB_QCI_2,0)) as M8006C46 ,sum(nvl(DENOM_SUM_SIMUL_ERAB,0)) as M8006C54

,sum(nvl(ENB_EPS_BEAR_REL_REQ_O_QCI1,0)) as M8006C143 ,sum(nvl(ENB_EPS_BEAR_REL_REQ_T_QCI1,0)) as M8006C152 ,sum(nvl(ERAB_REL_ENB_ACT_QCI1,0)) as M8006C176 ,sum(nvl(ERAB_REL_ENB_ACT_QCI2,0)) as M8006C177 ,sum(nvl(ERAB_INI_SETUP_ATT_QCI1,0)) as M8006C188 ,sum(nvl(ERAB_INI_SETUP_ATT_QCI2,0)) as M8006C189 ,sum(nvl(ERAB_INI_SETUP_ATT_QCI5,0)) as M8006C192 ,sum(nvl(ERAB_INI_SETUP_ATT_QCI9,0)) as M8006C196 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI1,0)) as M8006C197 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI2,0)) as M8006C198 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI5,0)) as M8006C201 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI9,0)) as M8006C205 ,sum(nvl(ERAB_INI_SETUP_SUCC_QCI1,0)) as M8006C206 ,sum(nvl(ERAB_INI_SETUP_SUCC_QCI2,0)) as M8006C207 ,sum(nvl(ERAB_INI_SETUP_SUCC_QCI5,0)) as M8006C210 ,sum(nvl(ERAB_INI_SETUP_SUCC_QCI9,0)) as M8006C214 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI1,0)) as M8006C215 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI2,0)) as M8006C216 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI5,0)) as M8006C219 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI9,0)) as M8006C223

,decode(SUM(ERAB_INI_SETUP_ATT_QCI1+ERAB_ADD_SETUP_ATT_QCI1),0,0 ,SUM(ERAB_REL_ENB_ACT_QCI1+ENB_EPS_BEAR_REL_REQ_O_QCI1+ENB_EPS_BEAR_REL_REQ_T_QCI1)/SUM(ERAB_INI_SETUP_ATT_QCI1+ERAB_ADD_SETUP_ATT_QCI1)) as EU0205 from

NOKLTE_PS_LEPSB_lncel_hour PMRAW where

period_start_time between to_date(&1,'yyyymmddhh24') and to_date(&2,'yyyymmddhh24') ---- to_char(period_start_time,'yyyymmddHH24') >= to_char(SYSDATE-1,'yyyymmddHH24') -- and to_char(period_start_time,'yyyymmddHH24') <= to_char(SYSDATE-1,'yyyymmddHH24') --AND PERIOD_DURATION=15 group by

to_char(period_start_time,'yyyymmddhh24'),LNCEL_ID,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID )M8006,(

select

to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,SUM(IP_TPUT_VOL_UL_QCI_1)/8/1000 as EU0507

,SUM(IP_TPUT_VOL_DL_QCI_1)/8/1000 as EU0508 ,SUM(IP_TPUT_VOL_UL_QCI_2)/8/1000 as EU0509 ,SUM(IP_TPUT_VOL_DL_QCI_2)/8/1000 as EU0510 ,SUM(IP_TPUT_VOL_UL_QCI_5)/8/1000 as EU0515 ,SUM(IP_TPUT_VOL_DL_QCI_5)/8/1000 as EU0516

,sum(nvl(PDCP_DATA_RATE_MEAN_UL_QCI_1,0)) as M8012C116 ,sum(nvl(PDCP_DATA_RATE_MEAN_DL_QCI_1,0)) as M8012C143 from

NOKLTE_PS_LCELLT_lncel_hour PMRAW where

period_start_time between to_date(&1,'yyyymmddhh24') and to_date(&2,'yyyymmddhh24') --- to_char(period_start_time,'yyyymmddHH24') >= to_char(SYSDATE-1,'yyyymmddHH24') -- and to_char(period_start_time,'yyyymmddHH24') <= to_char(SYSDATE-1,'yyyymmddHH24') --AND PERIOD_DURATION=15 group by

to_char(period_start_time,'yyyymmddhh24'),LNCEL_ID,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID )M8012 ,(

select

to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,sum(nvl(SIGN_CONN_ESTAB_COMP,0)) as M8013C5 ,sum(nvl(SIGN_CONN_ESTAB_ATT_MO_S,0)) as M8013C17 ,sum(nvl(SIGN_CONN_ESTAB_ATT_MT,0)) as M8013C18 ,sum(nvl(SIGN_CONN_ESTAB_ATT_MO_D,0)) as M8013C19 ,sum(nvl(SIGN_CONN_ESTAB_ATT_EMG,0)) as M8013C21 ,sum(nvl(SIGN_CONN_ESTAB_COMP_EMG,0)) as M8013C26 ,sum(nvl(SIGN_CONN_ESTAB_ATT_HIPRIO,0)) as M8013C31 ,sum(nvl(SIGN_CONN_ESTAB_ATT_DEL_TOL,0)) as M8013C34 ,lnbts.co_object_instance enb_id ,lncel.co_object_instance cell_id ,ip.ipno_mpia_8 bts_ip

,lnbts.co_sys_version bts_version ,Trim(lnbts.co_name) bts_name ,Trim(lncel.co_name) cel_name from

NOKLTE_PS_LUEST_lncel_hour PMRAW,ctp_common_objects lnbts,ctp_common_objects lncel,c_lte_ipno ip where

period_start_time between to_date(&1,'yyyymmddhh24') and to_date(&2,'yyyymmddhh24')

--- to_char(period_start_time,'yyyymmddHH24') >= to_char(SYSDATE-1,'yyyymmddHH24')

Word文档下载：诺西LTE指标提取SQL语句20160801.doc

搜索更多:诺西LTE指标提取SQL语句20160801