发布时间 : 星期四 文章诺西LTE指标提取SQL语句20160801更新完毕开始阅读
-- and to_char(period_start_time,'yyyymmddHH24') <= to_char(SYSDATE-1,'yyyymmddHH24') AND PMRAW.LNCEL_ID=lncel.co_gid and PMRAW.lnbts_id=lnbts.co_gid
and ip.ipno_bts_id=lnbts.co_object_instance --AND PERIOD_DURATION=15 group by
to_char(period_start_time,'yyyymmddhh24'),MRBTS_ID,LNBTS_ID,LNCEL_ID,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID
,lnbts.co_object_instance,lncel.co_object_instance,ip.ipno_mpia_8,lnbts.co_sys_version,Trim(lnbts.co_name),Trim(lncel.co_name) ) M8013 ,( select
to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID
,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,sum(nvl(PDCP_SDU_LOSS_UL_QCI_1_FNA,0)) as M8026C255 ,sum(nvl(PDCP_SDU_LOSS_DL_QCI_1_FNA,0)) as M8026C260 ,sum(nvl(PDCP_SDU_LOSS_UL_QCI_2_FNA,0)) as M8026C256 ,sum(nvl(PDCP_SDU_LOSS_DL_QCI_2_FNA,0)) as M8026C261
from
NOKLTE_PS_LQOS_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 )M8026 ,(
select
to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID
,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,sum(nvl(ISYS_HO_GERAN_SRVCC_SUCC,0)) as M8016C34 ,sum(nvl(ISYS_HO_GERAN_SRVCC_ATT,0)) as M8016C33
from
NOKLTE_PS_LISHO_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')
24
-- 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 )M8016 ,(
select
to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID
,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,sum(nvl(PRB_USED_PUSCH,0)) as M8011C50 ,sum(nvl(PRB_USED_PDSCH,0)) as M8011C54 from
NOKLTE_PS_LCELLR_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 )M8011 ,(
select
to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID ,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,avg(nvl(ERAB_SETUP_TIME_MEAN,0)) as M8007C14
from
NOKLTE_PS_LRDB_MNC1_RAW 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 )M8007 ,(
25
select
to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID
,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,avg(nvl(RRC_CON_STP_TIM_MEAN,0)) as M8008C17
from
NOKLTE_PS_LRRC_MNC1_RAW 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 )M8008
WHERE M8013.cel_key_id=m8001.cel_key_id(+) AND M8013.cel_key_id=m8006.cel_key_id(+) AND M8013.cel_key_id=m8007.cel_key_id(+) AND M8013.cel_key_id=m8008.cel_key_id(+) AND M8013.cel_key_id=m8011.cel_key_id(+) AND M8013.cel_key_id=m8012.cel_key_id(+) AND M8013.cel_key_id=m8016.cel_key_id(+) AND M8013.cel_key_id=m8026.cel_key_id(+) )
WHERE enb_id=618679 --enb_id between 620416 and 620608) --(bts.co_object_instance between '618496' and '619375')or(bts.co_object_instance between '620416' and '620608')
GROUP BY enb_cell,enb_id,cell_id,bts_ip,bts_version,bts_name,cel_name,sdatetime
5. –15分组粒度全网指标查询
SELECT
enb_cell ,sdatetime ,enb_id ,cell_id
26
,bts_ip
,bts_version ,bts_name ,cel_name
,sum(M8013C17+M8013C18+M8013C19+M8013C20+M8013C21+M8013C31+M8013C34) RRC连接建立请求次数
,sum(M8013C5) RRC连接建立成功次数 ,sum(M8006C0) ERAB建立请求数 ,sum(M8006C1) ERAB建立成功数 ,sum(M8013C15）无线掉线次数1 ,sum(M8013C16) 无线掉线次数2 ,sum(M8006C12) ERAB掉线次数1 ,sum(M8006C13) ERAB掉线次数2 ,sum(M8006C14) ERAB掉线次数3
,Round(Decode(sum(M8013C17+M8013C18+M8013C19+M8013C20+ M8013C21),0,0, 100*sum(M8013C5)/sum(M8013C17+M8013C18+M8013C19+M8013C20+ M8013C21)),2) RRC连接建立成功率 --集团考核指标,和nokia官方公式一致,LTE_5218a ,Round(Decode(sum(M8006C0),0,0,100*sum(M8006C1)/sum(M8006C0)),2) ERAB建立成功率
,Round(100*decode( sum(M8006C6 + M8006C7 + M8006C8 + M8006C9 + M8006C15 + M8006C10 +
M8006C12 + M8006C14 + M8006C13),0,null, sum(M8006C12 + M8006C14 + M8006C13) / sum(M8006C6 + M8006C7 + M8006C8 + M8006C9 + M8006C15 + M8006C10 + M8006C12 + M8006C14 + M8006C13)),2) ERAB掉线率 ,Round(Decode(sum(M8013C17+M8013C18+M8013C19+M8013C20+M8013C21)*sum(M8006C0),0,0,
100*(sum(M8013C5)/sum(M8013C17+M8013C18+M8013C19+M8013C20+M8013C21))*(sum(M8006C1)/sum(M8006C0))),2) 无线接通率 --集团考核指标,nokia官方没有这个公式,为中国区自己匹配,nokia最近似的标准公式是LTE_5060e：RRC成功率*initial_Erab成功率
,Round(Decode(sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170),0,0,
100*sum(M8013C15+M8013C16)/sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170)),2) 无线掉线率1 --集团考核指标,常用公式,nokia内部没有这个公式,分母不包括:last(roundup(M8001C318/M8001C319)),因为不合理
,Round(decode(sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170+M8001C223),0,0,
100*sum(M8013C15+M8013C16)/sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170+M8001C223)),2) 无线掉线率2 --集团考核指标,修正后的报集团公式,nokia官方没有这个公式,分母用M8001C223替代了last(roundup(M8001C318/M8001C319))
,Round(decode(sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170+M8001C223),0,0, 100*sum(M8006C176+M8006C177+M8006C178+M8006C179+M8006C180+M8013C16)
/sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170+M8001C223)),2) 无线掉线率3 ,Round(Decode(sum(M8009C6+M8014C6+M8014C18),0,0,
100*sum(M8009C7+M8014C7+M8014C19)/sum(M8009C6+M8014C6+M8014C18)),2) 切换成功率1 --集团考核指标,修正后的正确公式,修正了集团公式的分母,inter eNB S1用M8014C18替换M8014C14,nokia官方没有这个公式
27