【资料名称】：nsn-sql

【资料作者】：yuan

【资料日期】：2013-7-20

【资料语言】：中文

【资料格式】：DOC

【资料目录和简介】：

select
k.sdatesdate
,k.stimestime
,k.aera aera
,k.bscbsc
,k.cici
,k.band1band1
,k.band2band2
,k.band3band3
,k.band4band4
,k.band5band5
,k.bandbandrate
from
(SELECT
dbk.sdatesdate
,dbk.stimestime
,dbk.aera aera
,dbk.bscbsc
,dbk.cici
,dbk.band1band1
,dbk.band2band2
,dbk.band3band3
,dbk.band4band4
,dbk.band5band5
,decode(sum(dbk.band1+dbk.band2+dbk.band3+dbk.band4+dbk.band5),0,0,sum(dbk.band4+dbk.band5)/sum(dbk.band1+dbk.band2+dbk.band3+dbk.band4+dbk.band5))band
from
(SELECT
to_char(res.period_start_time,'yyyymmdd') sdate
, to_char(res.period_start_time,'hh24')stime
,bsc.nameBSC
,c_bts.cell_idCI
,substr(c_bts.segment_name,1,2)aera
, sum(res.AVE_IDLE_F_TCH_1)band1
, sum(res.AVE_IDLE_F_TCH_2)band2
, sum(res.AVE_IDLE_F_TCH_3)band3
, sum(res.AVE_IDLE_F_TCH_4)band4
, sum(res.AVE_IDLE_F_TCH_5)band5
from
objectsbsc
,objectsbts
,c_bts
,p_nbsc_res_availres
where
res.int_id=bsc.int_id
and res.bts_int_id=bts.int_id
and res.bts_int_id=c_bts.int_id
and res.period_start_time>=to_date(&starttime,'yyyymmddhh24')
and res.period_start_time<=to_date(&endtime,'yyyymmddhh24')
and to_char( res.period_start_time, 'hh24') in ('08','09','10','18','19','20')
and substr(c_bts.segment_name,1,2) in (&<name="县"hint="select one area from the list"default="'MW','MH','PT','LJ','CL','FQ'"ifempty="'MW','MH','PT','LJ','CL','FQ'"list=" ,'全部县','MW','马尾','MH','闽侯',PT,'平潭','LJ','连江','CL','长乐','FQ','福清'"description="yes">)
--and bsc.name like 'ZZBSC%'
--and substr(c_bts.segment_name,1,2) in (&<name="县"hint="select one area from the list"default="'MH','LJ'"ifempty="'MH','LJ'"list=" 'MH','闽侯','LJ','连江'"description="yes">)
group by
to_char(res.period_start_time,'yyyymmdd')
, to_char(res.period_start_time,'hh24')
,substr(c_bts.segment_name,1,2)
,bsc.name
,c_bts.cell_id)dbk
group by
dbk.sdate
,dbk.stime
,dbk.aera
,dbk.bsc
,dbk.ci
,dbk.band1
,dbk.band2
,dbk.band3
,dbk.band4
,dbk.band5 )k
where
band >0.2