【資料名稱】：質(zhì)差小區(qū)指標提取SQL

【資料作者】：2012

【資料日期】：2012

【資料語言】：中文

【資料格式】：DOC

【資料目錄和簡介】：

select *
from
(
select a1.sdate,
a1.bscname bsca,
a1.cell_id cellid,
a1.seg seg,
a1.segname segname,
a1.ul5 ul,
a1.dl5 dl,
a2.tcherl 話務(wù)量
from (select to_char(a.period_start_time, 'yyyymmdd') Sdate,
bsc.name bscname,
a.segment_id seg,
c.segment_name segname,
c.cell_id cell_id,
decode(sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
0,
100,
round(100 * sum(a.freq_ul_qual0) /
sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
2)) ul0,
decode(sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
0,
100,
round(100 * sum(a.freq_ul_qual1) /
sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
2)) ul1,
decode(sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
0,
100,
round(100 * sum(a.freq_ul_qual2) /
sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
2)) ul2,
decode(sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
0,
100,
round(100 * sum(a.freq_ul_qual3) /
sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
2)) ul3,
decode(sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
0,
100,
round(100 * sum(a.freq_ul_qual4) /
sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
2)) ul4,
decode(sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
0,
100,
round(100 *
sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5) /
sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
2)) ul5,
decode(sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
0,
100,
round(100 * sum(a.freq_ul_qual6) /
sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
2)) ul6,
decode(sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
0,
100,
round(100 * sum(a.freq_ul_qual7) /
sum(a.freq_ul_qual0 + a.freq_ul_qual1 +
a.freq_ul_qual2 + a.freq_ul_qual3 +
a.freq_ul_qual4 + a.freq_ul_qual5 +
a.freq_ul_qual6 + a.freq_ul_qual7),
2)) ul7,
decode(sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
0,
100,
round(100 * sum(a.freq_dl_qual0) /
sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
2)) dl0,
decode(sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
0,
100,
round(100 * sum(a.freq_dl_qual1) /
sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
2)) dl1,
decode(sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
0,
100,
round(100 * sum(a.freq_dl_qual2) /
sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
2)) dl2,
decode(sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
0,
100,
round(100 * sum(a.freq_dl_qual3) /
sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
2)) dl3,
decode(sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
0,
100,
round(100 * sum(a.freq_dl_qual4) /
sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
2)) dl4,
decode(sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
0,
100,
round(100 *
sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5) /
sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
2)) dl5,
decode(sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
0,
100,
round(100 * sum(a.freq_dl_qual6) /
sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
2)) dl6,
decode(sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
0,
100,
round(100 * sum(a.freq_dl_qual7) /
sum(a.freq_dl_qual0 + a.freq_dl_qual1 +
a.freq_dl_qual2 + a.freq_dl_qual3 +
a.freq_dl_qual4 + a.freq_dl_qual5 +
a.freq_dl_qual6 + a.freq_dl_qual7),
2)) dl7

from p_nbsc_rx_qual a, c_bts c, objects bsc
where
to_char(a.period_start_time,'yyyymmdd') between &Sdate and &enddate
and to_char(a.period_start_time,'hh24') in('08','09','10','18','19','20')
and
-- c.cell_id='&cellid' and
a.int_id = bsc.int_id
and c.int_id = a.bts_int_id
and c.conf_name = '<ACTUAL>'
and bsc.name like '**BSC%'

group by to_char(a.period_start_time, 'yyyymmdd'),
--,to_char(a.period_start_time,'hh24')
bsc.name,
a.segment_id,
c.segment_name,
c.cell_id
)a1,

(select bsc2.name bscm,
c2.cell_id cellid,
to_char(ava.period_start_time, 'yyyymmdd'),
round(sum(ave_busy_tch / res_av_denom14), 2) tcherl
from p_nbsc_res_avail ava, c_bts c2, objects bsc2
where to_char(ava.period_start_time,'yyyymmdd') between &Sdate and &enddate
and to_char(ava.period_start_time,'hh24') in('08','09','10','18','19','20')
and
-- c2.cell_id='&cellid' and
c2.int_id = ava.bts_int_id
and c2.conf_name = '<ACTUAL>'
and ava.int_id = bsc2.int_id
and bsc2.name like '**BSC%'
group by to_char(ava.period_start_time, 'yyyymmdd'),
bsc2.name,
c2.cell_id) a2
where a1.cell_id = a2.cellid
) a3
where
話務(wù)量>=3 and (ul<95 or dl<95)