诺西LTE指标提取SQL语句20160801

,max(nvl(UL_PRB_UTIL_TTI_MAX, 0)) M8011C23 --The maximum value of the UL Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI.

,avg(nvl(UL_PRB_UTIL_TTI_MEAN, 0)) M8011C24 --The mean value of the UL Physical Resource Block (PRB) use per TTI. The use is defined by the rate of used PRB per TTI.
The reported value is 10 times higher than the actual value (for example 5.4 is stored as 54).

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_1, 0)) M8011C25 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of <= 10% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_2, 0)) M8011C26 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 10% < PRBUTIL < = 20% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_3, 0)) M8011C27 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 20% < PRBUTIL < = 30% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_4, 0)) M8011C28 --The Physical Resource Block (PRB) utilization per TTI. The utilisation is defined by the rate of used PRB per TTI. Utilization in the range of 30% < PRBUTIL < = 40% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_5, 0)) M8011C29 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 40% < PRBUTIL < = 50% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_6, 0)) M8011C30 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 50% < PRBUTIL < = 60% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_7, 0)) M8011C31 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 60% < PRBUTIL < = 70% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_8, 0)) M8011C32 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 70% < PRBUTIL < = 80% is updated to this counter. ,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_9, 0)) M8011C33 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 80% < PRBUTIL < = 90% is updated to this counter. ,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_10, 0)) M8011C34 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 90% < PRBUTIL < = 100% is updated to this counter. ,min(nvl(DL_PRB_UTIL_TTI_MIN, 0)) M8011C35 --The minimum value of the DL Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. ,max(nvl(DL_PRB_UTIL_TTI_MAX, 0)) M8011C36 --The maximum value of the DL Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI.

,avg(nvl(DL_PRB_UTIL_TTI_MEAN, 0)) M8011C37 --The mean value of the DL Physical Resource Block (PRB) use per TTI. The use is defined by the rate of used PRB per TTI.
The reported value is 10 times higher than the actual value (for example 5.4 is stored as 54).

,sum(nvl(CCE_AVAIL_ACT_TTI, 0)) M8011C38 --Total number of CCEs available for active TTIs when at least one PDCCH is going to be scheduled.

,sum(nvl(AGG1_USED_PDCCH, 0)) M8011C39 --Total number of AGG1 used for PDCCH scheduling over the measurement period.

36

,sum(nvl(AGG2_USED_PDCCH, 0)) M8011C40 --Total number of AGG2 used for PDCCH scheduling over the measurement period.

,sum(nvl(AGG4_USED_PDCCH, 0)) M8011C41 --Total number of AGG4 used for PDCCH scheduling over the measurement period.

,sum(nvl(AGG8_USED_PDCCH, 0)) M8011C42 --Total number of AGG8 used for PDCCH scheduling over the measurement period.

,sum(nvl(AGG1_BLOCKED_PDCCH, 0)) M8011C43 --Total number of AGG1 blocked for PDCCH scheduling over the measurement period.

,sum(nvl(AGG2_BLOCKED_PDCCH, 0)) M8011C44 --Total number of AGG2 blocked for PDCCH scheduling over the measurement period.

,sum(nvl(AGG4_BLOCKED_PDCCH, 0)) M8011C45 --Total number of AGG4 blocked for PDCCH scheduling over the measurement period.

,sum(nvl(AGG8_BLOCKED_PDCCH, 0)) M8011C46 --The total number of AGG8 blocked for PDCCH scheduling over the measurement period.

,sum(nvl(PRB_USED_UL_TOTAL, 0)) M8011C47 --Total number of PRBs used for UL transmissions on PUSCH, PUCCH and PRACH over the measurement period is updated to this counter. A PRB covers the pair of resource blocks of two consecutive slots in a subframe.

,sum(nvl(PRB_USED_PUCCH, 0)) M8011C49 --Total number of PRBs used for PUCCH over the measurement period is updated to this counter. A PRB covers the pair of resource blocks of two consecutive slots in a subframe.

,avg(nvl(PRB_USED_PUSCH, 0)) M8011C50avg --Total number of PRBs used for UL transmissions on PUSCH over the measurement period is updated to this counter. A PRB covers the pair of resource blocks of two consecutive slots in a subframe.

,sum(nvl(PRB_USED_DL_TOTAL, 0)) M8011C51 --Total number of PRBs used for DL transmissions over the measurement period is updated to this counter. A PRB covers the pair of resource blocks of two consecutive slots in a subframe.

,avg(nvl(PRB_USED_PDSCH, 0)) M8011C54avg --Total number of PRBs used for PDSCH over the measurement period is updated to this counter. A PRB covers the pair of resource blocks of two consecutive slots in a subframe.

,avg(nvl(UE_PER_UL_TTI_AVG, 0)) M8011C55 --Average value of UEs served per TTI on uplink during a measurement period. Counter unit is 0.1 UE. For example, counter value 100 represents ten UEs. ,max(nvl(UE_PER_UL_TTI_MAX, 0)) M8011C56 --Maximum value of UEs served per TTI on uplink during a measurement period.

,avg(nvl(UE_PER_DL_TTI_AVG, 0)) M8011C57 --Average value of UEs served per TTI on downlink during a measurement period. Counter unit is 0.1 UE. For example, counter value 100 represents ten UEs. ,max(nvl(UE_PER_DL_TTI_MAX, 0)) M8011C58 --Maximum value of UEs served per TTI on downlink during a measurement period.

,sum(nvl(PDCCH_1_OFDM_SYMBOL, 0)) M8011C59 --This counter provides the number of subframes when only 1 OFDM symbol is allocated to the PDCCH (3GPP TS 36.211, 36.300).

,sum(nvl(PDCCH_2_OFDM_SYMBOLS, 0)) M8011C60 --This counter provides the number of subframes when 2 OFDM symbols are allocated to the PDCCH (3GPP TS 36.211, 36.300).

,sum(nvl(PDCCH_3_OFDM_SYMBOLS, 0)) M8011C61 --This counter provides the number of subframes when 3 OFDM symbols are allocated to the PDCCH (3GPP TS 36.211, 36.300).

,avg(nvl(TTI_BUNDLING_MODE_UE_AVG, 0)) M8011C62 --The measurement provides the average

37

number of UEs in TTI Bundling mode per sample.聽Each instance of a UE entering TTI Bundling Mode during the measurement interval is included in the average. Thus if, for example, a single UE is placed in TTI Bundling mode three times during the measurement interval, all of the three instances will be included in the average.
The reported value is 100 times higher than the actual value (for example 1.00 is stored as 100).

,sum(nvl(TTI_BUNDL_RETENTION_SHORT, 0)) M8011C64 --The measurement represents the number of UEs that are in TTI Bundling mode for only a short period of time, which is defined as less than t1 seconds, that is retention time in TTI Bundling mode < t1 sec.

,sum(nvl(TTI_BUNDL_RETENTION_MEDIUM, 0)) M8011C65 --The measurement represents the number of UEs that are in TTI Bundling mode for a medium period of time. This time period is defined by a lower bound of t1 seconds and an upper bound of t2 seconds, that is retention time in TTI Bundling mode: t1 <= x <= t2 sec. ,sum(nvl(TTI_BUNDL_RETENTION_LONG, 0)) M8011C66 --The measurement represents the number of UEs that are in TTI Bundling mode for a long period of time, which is defined as longer than t2 seconds, that is retention time in TTI Bundling mode > t2 sec.

,sum(nvl(CA_SCELL_CONFIG_ATT, 0)) M8011C67 --This counter provides the number of SCell configuration attempts.

,sum(nvl(CA_SCELL_CONFIG_SUCC, 0)) M8011C68 --This counter provides the number of successful SCell configuration attempts.

,sum(nvl(HIGH_CELL_LOAD_LB, 0)) M8011C69 --This counter indicates the occurrence that the cell is in the active Load Balancing state.
When the cell is in the active Load Balancing state, the Load Balancing algorithm tries to reduce the serving cell load by shifting cell traffic to neighbor cells (Load Balancing handover).
The maximum value of this counter depends on the length of the measurement period. If the measurement period is 15 minutes (that is 900 sec), the maximum value of this counter is 900 sec/1 sec = 900 (every 1 second the eNB checks whether the cell is in the active Load Balancing state).

from NOKLTE_PS_LCELLR_MNC1_RAW PMRAW where

period_start_time between to_date(&1,'yyyymmddHH24mi') and to_date(&2,'yyyymmddHH24mi') --- to_char(period_start_time,'yyyymmddHH24mi') >= to_char(SYSDATE-1,'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')||LNCEL_ID

) M8011 ,( select

38

to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID

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

,sum(nvl(PDCP_SDU_VOL_UL,0)) M8012C19 --The measurement gives an indication of the eUu interface traffic load by reporting the total received PDCP SDU-related traffic volume.

,sum(nvl(PDCP_SDU_VOL_DL,0)) M8012C20 --The measurement gives an indication of the eUu interface traffic load by reporting the total transmitted PDCP SDU-related traffic volume. ,sum(nvl(IP_TPUT_TIME_UL_QCI_1,0)) M8012C92 ,sum(nvl(IP_TPUT_TIME_UL_QCI_2,0)) M8012C94 ,sum(nvl(IP_TPUT_TIME_UL_QCI_3,0)) M8012C96 ,sum(nvl(IP_TPUT_TIME_UL_QCI_4,0)) M8012C98 ,sum(nvl(IP_TPUT_TIME_UL_QCI_5,0)) M8012C100 ,sum(nvl(IP_TPUT_TIME_UL_QCI_6,0)) M8012C102 ,sum(nvl(IP_TPUT_TIME_UL_QCI_7,0)) M8012C104 ,sum(nvl(IP_TPUT_TIME_UL_QCI_8,0)) M8012C106 ,sum(nvl(IP_TPUT_TIME_UL_QCI_9,0)) M8012C108 ,sum(nvl(IP_TPUT_TIME_DL_QCI_1,0)) M8012C118 ,sum(nvl(IP_TPUT_TIME_DL_QCI_2,0)) M8012C120 ,sum(nvl(IP_TPUT_TIME_DL_QCI_3,0)) M8012C122 ,sum(nvl(IP_TPUT_TIME_DL_QCI_4,0)) M8012C124 ,sum(nvl(IP_TPUT_TIME_DL_QCI_5,0)) M8012C126 ,sum(nvl(IP_TPUT_TIME_DL_QCI_6,0)) M8012C128 ,sum(nvl(IP_TPUT_TIME_DL_QCI_7,0)) M8012C130 ,sum(nvl(IP_TPUT_TIME_DL_QCI_8,0)) M8012C132 ,sum(nvl(IP_TPUT_TIME_DL_QCI_9,0)) M8012C134 from

NOKLTE_PS_LCELLT_MNC1_RAW PMRAW where

period_start_time between to_date(&1,'yyyymmddHH24mi') and to_date(&2,'yyyymmddHH24mi') --- to_char(period_start_time,'yyyymmddHH24mi') >= to_char(SYSDATE-1,'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')||LNCEL_ID )M8012,( select

to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID

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

,sum(nvl(SIGN_CONN_ESTAB_COMP,0)) M8013C5 --The number of Signaling Connection Establishment completions with the UE target to be in the ECM-CONNECTED state.

,sum(nvl(SIGN_EST_F_RRCCOMPL_MISSING,0)) M8013C6 --The number of Signaling Connection Establishment failures due to a missing RRC CONNECTION SETUP COMPLETE message. The UE has not

39