SQL在网优中的实用案例

1、 2way问题一步到位
/*create FUNCTION dbo.GetDistance ( @LonBegin REAL, @LatBegin REAL, @LonEnd REAL ,@LatEnd REAL )
RETURNS FLOAT AS BEGIN
DECLARE @Distance REAL
DECLARE @EARTH_RADIUS REAL
SET @EARTH_RADIUS = 6376736.6684
DECLARE @RadLatBegin REAL, @RadLatEnd REAL, @RadLatDiff REAL, @RadLonDiff REAL
SET @RadLatBegin = @LatBegin * PI() / 180.0
SET @RadLatEnd = @LatEnd * PI() / 180.0
SET @RadLatDiff = @RadLatBegin - @RadLatEnd
SET @RadLonDiff = @LonBegin * PI() / 180.0 - @LonEnd * PI() / 180.0
SET @Distance = 2 * ASIN(SQRT(POWER(Sin(@RadLatDiff / 2), 2) + COS(@RadLatBegin) * COS(@RadLatEnd) * POWER(SIN(@RadLonDiff/2),2))) SET @Distance = @Distance * @EARTH_RADIUS
RETURN @Distance
END
*/
create TABLE CDMA2WAY
(
SVR varchar(8000),
svrPN varchar(8000),
NL varchar(8000),
nlPN varchar(8000),
Dist varchar(8000),
切换次数 varchar(8000),
PSMM建议 varchar(8000)
)
insert into CDMA2WAY
select T.SVR as SVR,T.svrPN as svrPN,T.NL as NL,T.nlPN as nlPN,T.Dist as Dist,
dbo.PSMMPriAnalyse.PSMMCount as 切换次
数,dbo.PSMMPriAnalyse.Remark as PSMM建议
from(
select SVR,svrPN,NL,nlPN,dbo.GetDistance(svrLON,svrLAT,nlLON,nlLAT) as Dist
from(
select dbo.邻区.svr as SVR,工参1.经度 as svrLON ,工参1.纬度 as svrLAT,工参1.PN as svrPN,
dbo.邻区.nl as NL,工参2.经度as nlLON,工参2.纬度as nlLAT,工参2.PN as nlPN
from dbo.邻区,dbo.工参 as 工参1,dbo.工参 as 工参2
where
dbo.邻区.svr=工参1.索引
and
dbo.邻区.nl=工参2.索引
) as Tab
)as T,dbo.PSMMPriAnalyse
where T.SVR=dbo.PSMMPriAnalyse.CarrierName
and T.NL=dbo.PSMMPriAnalyse.NBLCarrierName
select T1.svrPN as A的PN,T1.SVR as A,T1.NL as B,T1.Dist as AB距离,T1.切换次数 as AB切换次数,T1.PSMM建议 as ABPSMM建议,
T2.NL as C,T2.Dist as BC距离,T2.切换次数as BC切换次数,T2.PSMM建议 as BCPSMM建议,
T3.SVR as D,T3.Dist as CD距离,T3.切换次数 as DC切换次数,T3.PSMM建议 as DCPSMM建议,T3.svrPN as D的PN
from
CDMA2WAY as T1,CDMA2WAY as T2,CDMA2WAY as T3
where
T1.NL=T2.SVR
and
T2.NL=T3.NL
and
T1.svrPN=T3.svrPN
and
T1.SVR<>T2.NL
and
T1.NL<>T3.SVR
and
T1.SVR<>T3.SVR;
2、查询单站的掉话记录
select * from dbo.CHR_allbscdr where
(
(cast(呼叫资源释放时激活集1小区标识as varchar)+'_'+cast(呼叫资源释放时激活集1扇区标识 as varchar))='1209_3'
or
(cast(呼叫资源释放时激活集2小区标识as varchar)+'_'+cast(呼叫资源释放时激活集2扇区标识 as varchar))='1209_3'
or
(cast(呼叫资源释放时激活集3小区标识as varchar)+'_'+cast(呼叫资源释放时激活集3扇区标识 as varchar))='1209_3'
)
and
呼叫释放原因值 in (3074,3076,3077)
and
最终的业务选项=3;
3、查询移动性
select count(1) as TotalCall from
(select * from dbo.CHR_BSC1 where 最终的业务选项=3
and
(cast( 初始激活集1小区标识 as varchar)+'_'+cast(初始激活集1扇区标识 as varchar))
not in
(
(cast(呼叫资源释放时激活集1小区标识as varchar)+'_'+cast(呼叫资源释放时激活集1扇区标识 as varchar)),
(cast(呼叫资源释放时激活集2小区标识as varchar)+'_'+cast(呼叫资源释放时激活集2扇区标识 as varchar)),
(cast(呼叫资源释放时激活集3小区标识as varchar)+'_'+cast(呼叫资源释放时激活集3扇区标识 as varchar))
)
and
(cast( 初始激活集2小区标识 as varchar)+'_'+cast(初始激活集2扇区标识 as varchar))
not in
(
(cast(呼叫资源释放时激活集1小区标识as varchar)+'_'+cast(呼叫资源释放时激活集1扇区标识 as varchar)),
(cast(呼叫资源释放时激活集2小区标识as varchar)+'_'+cast(呼叫资源释放时激活集2扇区标识 as varchar)),
(cast(呼叫资源释放时激活集3小区标识as varchar)+'_'+cast(呼叫资源释放时激活集3扇区标识 as varchar))
)
and
(cast( 初始激活集3小区标识 as varchar)+'_'+cast(初始激活集3扇区标识 as varchar))
not in
(
(cast(呼叫资源释放时激活集1小区标识as varchar)+'_'+cast(呼叫资源释放时激活集1扇区标识 as varchar)),
(cast(呼叫资源释放时激活集2小区标识as varchar)+'_'+cast(呼叫资源释放时激活集2扇区标识 as varchar)),
(cast(呼叫资源释放时激活集3小区标识as varchar)+'_'+cast(呼叫资源释放时激活集3扇区标识 as varchar))
)
) as a;
4、查询校园网基站的短消息
select datename(day,释放时的秒数) as 日,datename(hour,释放时的秒数) as 小时,呼叫标志,
count(呼叫标志) as 短消息数量
from dbo.CHR_周一周二1X
where
最终的业务选项 in(6,14)
and
(cast(呼叫资源释放时激活集1小区标识as varchar)+'_'+cast(呼叫资源释
放时激活集1扇区标识 as varchar))
in
('1493_2','1475_2','1484_1','1484_3','1672_3','1525_1','1525_3','1580_3','1493_1','1475 group by
datename(day,释放时的秒数),
datename(hour,释放时的秒数),呼叫标志
order by datename(day,释放时的秒数) asc,datename(hour,释放时的秒数)
asc
5、短消息时延统计分析
--各类时延平均值统计
USE Nastar_Genex_CDMA2K_18
SELECT AVG(cast(寻呼时长as float)) as 平均寻呼时长,AVG(cast(指配时长
as float))as 平均指配时长,AVG(cast(捕获PREAMBLE时长as float))as 平均
捕获PREAMBLE时长,avg(cast(协商时长as float))as 平均协商时
长,AVG(cast(呼叫持续时间 as float)) AS 平均呼叫持续时间
FROM (
SELECT 'BSC01' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC01
UNION ALL
SELECT 'BSC02' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC02
UNION ALL
SELECT 'BSC03' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC03
UNION ALL
SELECT 'BSC04' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC04
UNION ALL
SELECT 'BSC05' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC05
UNION ALL
SELECT 'BSC06' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC06
UNION ALL
SELECT 'BSC07' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC07
UNION ALL
SELECT 'BSC08' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC08
UNION ALL
SELECT 'BSC09' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC09
UNION ALL
SELECT 'BSC10' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC10
UNION ALL
SELECT 'BSC11' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC11
UNION ALL
SELECT 'BSC12' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC12
UNION ALL
SELECT 'BSC13' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC13
UNION ALL
SELECT 'BSC14' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC14
UNION ALL
SELECT 'BSC15' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC15
UNION ALL
SELECT 'BSC16' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC16
) AS a
WHERE [最终的业务选项] = 6
AND 接入时刻秒数BETWEEN '2009-4-17 00:00:00' AND '2009-4-19 00:00:00'
AND 呼叫标志 = 1
AND 呼叫状态 = 1
--MT短消息呼叫时延分布
USE Nastar_Genex_CDMA2K_18
select count(case when 平均时长<2000 then 1 end) as a_2,
count(case when 平均时长>=2000 and 平均时长<3000 then 1 end)as a_3,
count(case when 平均时长>=3000 and 平均时长<5000 then 1 end)as a_4,
count(case when 平均时长>=5000 and 平均时长<7000 then 1 end)as a_7,
count(case when 平均时长>=7000 and 平均时长<10000 then 1 end)as
a_10,
count(case when 平均时长>=10000 and 平均时长<15000 then 1 end)as a_15,
count(case when 平均时长>=15000 and 平均时长<20000 then 1 end)as a_20,
count(case when 平均时长>=20000 and 平均时长<30000 then 1 end)as a_30,
count(case when 平均时长>=30000 and 平均时长<40000 then 1 end)as a_40,
count(case when 平均时长>=40000 and 平均时长<50000 then 1 end)as a_50,
count(case when 平均时长>=50000 then 1 end)as a_51
from
(SELECT *,(cast(呼叫持续时间 as float)) as 平均时长
FROM (
SELECT 'BSC01' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC01
UNION ALL
SELECT 'BSC02' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC02
UNION ALL
SELECT 'BSC03' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC03
UNION ALL
SELECT 'BSC04' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC04
UNION ALL
SELECT 'BSC05' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC05
UNION ALL
SELECT 'BSC06' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC06
UNION ALL
SELECT 'BSC07' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC07
UNION ALL
SELECT 'BSC08' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC08
UNION ALL
SELECT 'BSC09' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC09
UNION ALL
SELECT 'BSC10' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC10
UNION ALL
SELECT 'BSC11' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC11
UNION ALL
SELECT 'BSC12' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC12
UNION ALL
SELECT 'BSC13' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC13
UNION ALL
SELECT 'BSC14' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC14
UNION ALL
SELECT 'BSC15' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC15
UNION ALL
SELECT 'BSC16' AS BscID,* FROM dbo.CHR_1xCDR_Detail_BSC16
) AS a
WHERE [最终的业务选项] = 6
AND 接入时刻秒数BETWEEN '2009-4-17 00:00:00' AND '2009-4-19 00:00:00'
AND 呼叫标志 = 1
AND 呼叫状态 = 1
)as c
6、按区间统计短消息寻呼时延
select 寻呼时长,count(*) as CounNUM from(
select 寻呼时长=
case when (寻呼时长<1000 or 寻呼时长=1000) then '0~1s'
when (寻呼时长>1000 and (寻呼时长<2000 or 寻呼时长=2000)) then '1~2s'
when (寻呼时长>2000 and (寻呼时长<4000 or 寻呼时长=4000)) then '2~4s'
when (寻呼时长>4000 and (寻呼时长<7000 or 寻呼时长=7000)) then '4~7s'
when (寻呼时长>7000 and (寻呼时长<9000 or 寻呼时长=9000)) then '7~9s'
when 寻呼时长>9000 then '大于9s'
else 'NA'
end
from dbo.CHR_BSC2
where
呼叫初始的业务选项 in (6,14) and
呼叫标志=1
) as AA
group by 寻呼时长;。