sql数学公式的问题

qq_38940580 2018-01-20 11:58:17

有如下数据，在表当量表_1中：
观测站编号观测站名称行驶方向观测日期小时分钟当量
G25L372130208 丰润收费站上行 2016/8/3 15 30 71
G25L372130208 丰润收费站上行 2016/8/3 16 0 103
G25L372130208 丰润收费站上行 2016/8/3 16 55 99.5
G25L372130208 丰润收费站上行 2016/8/3 17 25 109.5
G25L372130208 丰润收费站上行 2016/8/3 18 30 70
G25L372130208 丰润收费站上行 2016/8/3 18 50 84.5
G25L372130208 丰润收费站上行 2016/8/3 19 20 65
G25L372130208 丰润收费站上行 2016/8/3 19 55 79.5
G25L372130208 丰润收费站上行 2016/8/3 20 25 92
G25L372130208 丰润收费站上行 2016/8/3 21 50 50.5
G25L372130208 丰润收费站上行 2016/8/3 22 20 37.5
G25L372130208 丰润收费站上行 2016/8/3 22 55 44.5
G25L372130208 丰润收费站上行 2016/8/3 23 25 34
G25L372130208 丰润收费站上行 2016/8/3 23 45 25
G25L372130208 丰润收费站上行 2016/8/4 0 15 20.5
G25L372130208 丰润收费站上行 2016/8/4 1 5 27.5
G25L372130208 丰润收费站上行 2016/8/4 1 40 29
G25L372130208 丰润收费站上行 2016/8/4 2 10 49.5
G25L372130208 丰润收费站上行 2016/8/4 2 45 48
G25L372130208 丰润收费站上行 2016/8/4 3 35 49
G25L372130208 丰润收费站上行 2016/8/4 4 5 69
G25L372130208 丰润收费站上行 2016/8/4 4 40 44.5
G25L372130208 丰润收费站上行 2016/8/4 5 10 70.5
G25L372130208 丰润收费站上行 2016/8/4 6 0 90
G25L372130208 丰润收费站上行 2016/8/4 6 35 85
G25L372130208 丰润收费站上行 2016/8/4 7 5 86
G25L372130208 丰润收费站上行 2016/8/4 7 40 138.5
G25L372130208 丰润收费站上行 2016/8/4 8 30 109
G25L372130208 丰润收费站上行 2016/8/4 9 0 101
G25L372130208 丰润收费站上行 2016/8/4 9 35 87
G25L372130208 丰润收费站上行 2016/8/4 10 5 58
G25L372130208 丰润收费站上行 2016/8/4 11 30 59
G25L372130208 丰润收费站上行 2016/8/4 12 0 48
G25L372130208 丰润收费站上行 2016/8/4 12 35 81
G25L372130208 丰润收费站上行 2016/8/4 22 45 21.5
G25L372130208 丰润收费站上行 2016/8/4 23 15 39
G25L372130208 丰润收费站上行 2016/8/4 23 50 65.5
G25L372130208 丰润收费站上行 2016/8/5 0 5 23
G25L372130208 丰润收费站上行 2016/8/5 0 40 29.5
G25L372130208 丰润收费站上行 2016/8/5 1 10 18
G25L372130208 丰润收费站上行 2016/8/5 1 45 17.5
G25L372130208 丰润收费站上行 2016/8/5 2 35 27.5
G25L372130208 丰润收费站上行 2016/8/5 3 5 10
G25L372130208 丰润收费站上行 2016/8/5 3 40 35
G25L372130208 丰润收费站上行 2016/8/5 4 10 16
G25L372130208 丰润收费站上行 2016/8/5 5 0 30
G25L372130208 丰润收费站上行 2016/8/5 5 35 52.5
G25L372130208 丰润收费站上行 2016/8/5 6 5 75
G25L372130208 丰润收费站上行 2016/8/5 6 40 77.5
G25L372130208 丰润收费站上行 2016/8/5 7 30 115
G25L372130208 丰润收费站上行 2016/8/5 8 0 70
G25L372130208 丰润收费站上行 2016/8/5 8 35 85.5
G25L372130208 丰润收费站上行 2016/8/5 9 5 79.5
G25L372130208 丰润收费站上行 2016/8/5 10 30 54
有如下要求：采用t 时段之前n（n=12）个数据的平均值q平均、方差σ来识别此类数据。
当q平均－2σ≤ Q ≤q平均＋2σ时，认为当量量数据是正常的，否则认为数据为异常数据。
我采用如下的方法：
if object_id('tempdb..#t_0')is not null drop table #t_0
;with
t as (select *,dt=(datediff(d,0,观测日期)*24+[小时])*12+[分钟]/5 from 当量表_1)
select
观测站名称,
行驶方向,
dt=dateadd(minute,dt*5,0),
当量

into #t_0
from t
if not object_id(N'Tempdb..#tabB2') is null
drop table #tabB2
--表二数据生成行号rn
SELECT *,ROW_NUMBER() OVER(PARTITION BY 观测站名称,行驶方向 order by dt) as rn
INTO #tabB2
FROM #t_0
DECLARE @rn INT,--行号
@dl float ,--平均当量
@t float,
@dl1 float ,
@dl2 float ,
@dl3 float ,
@dl4 float ,
@dl5 float ,
@dl6 float ,
@dl7 float ,
@dl8 float ,
@dl9 float ,
@dl10 float ,
@dl11 float ,
@dl12 float
SET @rn=0
SET @dl=0
SELECT @rn=rn FROM #tabB2
SELECT @dl=SUM(当量)/12 FROM #tabB2
WHERE rn BETWEEN @rn-1 AND @rn-12
SELECT @dl1=当量 FROM #tabB2
WHERE rn = @rn-1
SELECT @dl2=当量 FROM #tabB2
WHERE rn = @rn-2
SELECT @dl3=当量 FROM #tabB2
WHERE rn = @rn-3
SELECT @dl4=当量 FROM #tabB2
WHERE rn = @rn-4
SELECT @dl5=当量 FROM #tabB2
WHERE rn = @rn-5
SELECT @dl6=当量 FROM #tabB2
WHERE rn = @rn-6
SELECT @dl7=当量 FROM #tabB2
WHERE rn = @rn-7
SELECT @dl8=当量 FROM #tabB2
WHERE rn = @rn-8
SELECT @dl9=当量 FROM #tabB2
WHERE rn = @rn-9
SELECT @dl10=当量 FROM #tabB2
WHERE rn = @rn-10
SELECT @dl11=当量 FROM #tabB2
WHERE rn = @rn-11
SELECT @dl12=当量 FROM #tabB2
WHERE rn = @rn-12

select @t=((@dl1-@dl)*(@dl1-@dl)+(@dl2-@dl)*(@dl2-@dl)+(@dl3-@dl)*(@dl3-@dl)+(@dl4-@dl)*(@dl4-@dl)+(@dl4-@dl)*(@dl4-@dl)
+(@dl5-@dl)*(@dl5-@dl)+(@dl6-@dl)*(@dl6-@dl)+(@dl7-@dl)*(@dl7-@dl)+(@dl8-@dl)*(@dl8-@dl)+(@dl9-@dl)*(@dl9-@dl)+(@dl10-@dl)*(@dl10-@dl)
+(@dl11-@dl)*(@dl11-@dl)+(@dl12-@dl)*(@dl12-@dl))/12 from #tabB2
select 观测站名称,行驶方向,dt,当量,(case
when(当量>=@dl-2*sqrt(@t) and 当量<=@dl+2*(@t)) then '数据正常'

else '数据异常' end
) as 数据类型 from #t_0

得出结果确是全部异常，不知道哪里错了。求教各位大神。

...全文