【資料名稱】：new_質(zhì)差小區(qū)質(zhì)量匯總20121212

【資料作者】：2013

【資料日期】：20121212

【資料語(yǔ)言】：中文

【資料格式】：DOC

【資料目錄和簡(jiǎn)介】：

SELECT Qua.Sdate,
Qua.BSC,
Qua.CI,
PL.SEG,
Qua.dl0_5話音質(zhì)量,
Qua.ul0_5話音質(zhì)量,
PL.每線Erl峰值
,qq.PMAX1,
qq.PMAX2,
--qq.DMAX,
--qq.ALPHA,
--qq.GAMMA,
qq.PENA,
--qq.RLT,
--qq.HYS,
qq.RXP,
--qq.las,
qq.MS_PMAX1,
qq.MS_PMAX2

FROM (select a.SDATE SDATE,
a.ci CI,
a.bscBSC,

round(100 * decode(sum(dl0_7), 0, 0, sum(dl0_5) / sum(dl0_7)),
2) dl0_5話音質(zhì)量,

round(100 * decode(sum(ul0_7), 0, 0, sum(ul0_5) / sum(ul0_7)),
2) ul0_5話音質(zhì)量
from (select c.cell_id ci,
o.name bsc,
to_char(q.period_start_time, 'hh24') time,

to_char(q.period_start_time, 'yyyymmdd') SDATE,
sum(q.freq_dl_qual0 + q.freq_dl_qual1 +
q.freq_dl_qual2 + q.freq_dl_qual3 +
q.freq_dl_qual4 + q.freq_dl_qual5) dl0_5,
sum(q.freq_dl_qual0 + q.freq_dl_qual1 +
q.freq_dl_qual2 + q.freq_dl_qual3 +
q.freq_dl_qual4 + q.freq_dl_qual5 +
q.freq_dl_qual6 + q.freq_dl_qual7) dl0_7,
sum(q.freq_ul_qual0 + q.freq_ul_qual1 +
q.freq_ul_qual2 + q.freq_ul_qual3 +
q.freq_ul_qual4 + q.freq_ul_qual5) ul0_5,
sum(q.freq_ul_qual0 + q.freq_ul_qual1 +
q.freq_ul_qual2 + q.freq_ul_qual3 +
q.freq_ul_qual4 + q.freq_ul_qual5 +
q.freq_ul_qual6 + q.freq_ul_qual7) ul0_7
from p_nbsc_rx_qual q, c_bts c, objects o
where c.int_id = q.bts_int_id
and q.int_id = o.int_id
--andto_char(q.period_start_time,'yyyymmdd')='20120526'
AND q.PERIOD_START_TIME BETWEEN
TO_DATE(&SDATE, 'YYYYMMDD') AND
TO_DATE(&EDATE, 'YYYYMMDD')
AND TO_CHAR(q.PERIOD_START_TIME, 'YYYYMMDD') BETWEEN
&SDATE AND &EDATE
and to_char(q.period_start_time, 'hh24') in
(8, 9, 10, 18, 19,20 )
--(17)
--(8,9)
-- (8,9,10)
-- (18,19,20)
and c.conf_name = '<ACTUAL>'
and o.name like 'LYBSC%'
group by c.cell_id, O.name, q.period_start_time) a
--以小區(qū)號(hào)，BSC名字，時(shí)間的集合唯一進(jìn)行查詢

group by a.ci, a.bsc, a.SDATE) Qua,
(SELECT SDATE, BSC, CI, seg, SUM(ALL 每線話務(wù)量) 每線Erl峰值
FROM (SELECT TO_CHAR(AVAIL.PERIOD_START_TIME, 'YYYYMMDD') SDATE,
to_char(avail.period_start_time, 'hh24') stime,
OBSC.NAME BSC,
BTS.CELL_ID CI,
AVAIL.SEGMENT_ID SEG,
--ROUND(Sum(AVAIL.AVE_BUSY_TCH/AVAIL.RES_AV_DENOM14),2)Traffic,
--ROUND(Sum(AVAIL.AVE_BUSY_TCH/AVAIL.RES_AV_DENOM14),2)每線話務(wù)量,
ROUND(Sum( AVAIL.AVE_TCH_BUSY_HALF+AVAIL.AVE_TCH_BUSY_FULL),2) 每線話務(wù)量
--ROUND(Sum(AVAIL.AVE_AVAIL_TCH_SUM/AVAIL.AVE_AVAIL_TCH_DEN+(AVAIL.AVE_GPRS_CHANNELS_SUM/AVAIL.AVE_GPRS_CHANNELS_DEN)-(AVAIL.AVE_PERMANENT_GPRS_CH_SUM/AVAIL.AVE_PERMANENT_GPRS_CH_DEN)),2) AS 可用信道數(shù),
/*ROUND(DECODE(round(Sum(decode(AVAIL.AVE_AVAIL_TCH_DEN,
0,
0,
(AVAIL.AVE_AVAIL_TCH_SUM /
AVAIL.AVE_AVAIL_TCH_DEN)) +
decode(AVAIL.AVE_GPRS_CHANNELS_DEN,
0,
0,
(AVAIL.AVE_GPRS_CHANNELS_SUM /
AVAIL.AVE_GPRS_CHANNELS_DEN)) -
decode(AVAIL.AVE_PERMANENT_GPRS_CH_DEN,
0,
0,
(AVAIL.AVE_PERMANENT_GPRS_CH_SUM /
AVAIL.AVE_PERMANENT_GPRS_CH_DEN))),
0),
0,
0,
(round(sum(AVAIL.AVE_TCH_BUSY_HALF +
AVAIL.AVE_TCH_BUSY_FULL),
2) / --分子

round(Sum(decode(AVAIL.AVE_AVAIL_TCH_DEN,
0,
0,
(AVAIL.AVE_AVAIL_TCH_SUM /
AVAIL.AVE_AVAIL_TCH_DEN)) +
decode(AVAIL.AVE_GPRS_CHANNELS_DEN,
0,
0,
(AVAIL.AVE_GPRS_CHANNELS_SUM /
AVAIL.AVE_GPRS_CHANNELS_DEN)) -
decode(AVAIL.AVE_PERMANENT_GPRS_CH_DEN,
0,
0,
(AVAIL.AVE_PERMANENT_GPRS_CH_SUM /
AVAIL.AVE_PERMANENT_GPRS_CH_DEN))),
0))

) ,
4) 每線話務(wù)量2*/

FROM P_NBSC_RES_AVAIL AVAIL, OBJECTS OBSC, C_BTS BTS
WHERE AVAIL.INT_ID = OBSC.INT_ID
AND AVAIL.BTS_INT_ID = BTS.INT_ID
AND BTS.CONF_NAME = '<ACTUAL>'
AND AVAIL.PERIOD_START_TIME BETWEEN
TO_DATE(&SDATE, 'YYYYMMDD') AND
TO_DATE(&EDATE, 'YYYYMMDD')
AND TO_CHAR(AVAIL.PERIOD_START_TIME, 'YYYYMMDD') BETWEEN
&SDATE AND &EDATE
AND TO_CHAR(AVAIL.PERIOD_START_TIME, 'HH24') in
(8, 9, 10, 18, 19,20 )
--(17)
-- (8,9)
-- (8,9,10)
--(18,19,20)
AND OBSC.NAME LIKE 'LYBSC%'

GROUP BY TO_CHAR(AVAIL.PERIOD_START_TIME, 'YYYYMMDD'),
to_char(avail.period_start_time, 'hh24'),
OBSC.NAME,
BTS.CELL_ID,
AVAIL.SEGMENT_ID)
GROUP BY SDATE, BSC, CI, seg) PL
, ( select
bsc.name BSC,
c.LA_ID_LAC LAC,
c.CELL_ID CI,
c.last_modifiedlas,
--bts.NW_NAME NAME,
bts.OBJECT_INSTANCE SEG,
CON.BS_TX_PWR_MAX PMAX1,
con.BS_TX_PWR_MAX1X00 PMAX2,
--sum(con.BS_TX_PWR_MAX) PMAX1,
--sum(con.BS_TX_PWR_MAX1X00) PMAX2,
--con.LAST_MODIFIED LAST_MODIFIED,
C.MS_MAX_DIST_IN_CALL_SETUP DMAX,
CON.ALPHA,
CON.GAMMA,
CON.PC_CTRL_ENABLED PENA,
C.RADIO_LINK_TIMEOUT*4+4 RLT,
C.CELL_RESELECT_HYSTERESIS*2 HYS,
C.RX_LEV_ACCESS_MIN-110 RXP,
-C.MS_TX_PWR_MAX_GSM*2+43 MS_PMAX1,
-C.MS_TX_PWR_MAX_GSM1X00*2+30 MS_PMAX2
from
c_bts c,
c_power_control con,
objects trx,
objects bts,
objects bsc

where
con.int_id=trx.int_id and
trx.parent_int_id=bts.int_id and
bts.parent_int_id=bsc.int_id and
bts.int_id=c.int_id and
c.conf_name='<ACTUAL>' and
con.conf_name='<ACTUAL>' and
bsc.name LIKE 'LYBSC%' )QQ

WHERE QUA.Sdate = PL.Sdate
AND QUA.BSC = PL.BSC
And QUA.CI = PL.CI and pl.ci=qq.ci



And PL.每線Erl峰值 >= 3aND (Qua.dl0_5話音質(zhì)量< 95 or Qua.ul0_5話音質(zhì)量< 95)
--and PL.每線Erl峰值 >= 1.2 and PL.每線Erl峰值 < 3 and (Qua.dl0_5話音質(zhì)量< 95 or Qua.ul0_5話音質(zhì)量< 95)
--and PL.每線Erl峰值 >= 3and (Qua.dl0_5話音質(zhì)量< 96 or Qua.ul0_5話音質(zhì)量< 96)