ŵÎ÷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. ¨C15·Ö×éÁ£¶ÈÈ«ÍøÖ¸±ê²éѯ

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