疑难问题，求助各位大神，望不吝赐教！

weixin_40101242 2018-01-25 09:58:01
有如下数据：
观测站编号 观测站名称 行驶方向 观测日期 小时 分钟 当量
G25L372130208 丰润收费站 上行 2016/8/3 16 30 71
G25L372130208 丰润收费站 上行 2016/8/3 17 0 103
G25L372130208 丰润收费站 上行 2016/8/3 17 55 99.5
G25L372130208 丰润收费站 上行 2016/8/3 18 25 109.5
G25L372130208 丰润收费站 上行 2016/8/3 19 30 70
G25L372130208 丰润收费站 上行 2016/8/3 19 50 84.5
G25L372130208 丰润收费站 上行 2016/8/3 20 20 65
G25L372130208 丰润收费站 上行 2016/8/3 20 55 79.5
G25L372130208 丰润收费站 上行 2016/8/3 21 25 92
G25L372130208 丰润收费站 上行 2016/8/3 22 50 50.5
G25L372130208 丰润收费站 上行 2016/8/3 23 20 37.5
G25L372130208 丰润收费站 上行 2016/8/3 23 55 44.5
G25L372130208 丰润收费站 上行 2016/8/3 24 25 34
G25L372130208 丰润收费站 上行 2016/8/3 24 45 25
G25L372130208 丰润收费站 上行 2016/8/4 1 15 20.5
G25L372130208 丰润收费站 上行 2016/8/4 2 5 27.5
G25L372130208 丰润收费站 上行 2016/8/4 2 40 29
G25L372130208 丰润收费站 上行 2016/8/4 3 10 49.5
G25L372130208 丰润收费站 上行 2016/8/4 3 45 48
G25L372130208 丰润收费站 上行 2016/8/4 4 35 49
G25L372130208 丰润收费站 上行 2016/8/4 5 5 69
G25L372130208 丰润收费站 上行 2016/8/4 5 40 44.5
G25L372130208 丰润收费站 上行 2016/8/4 6 10 70.5
G25L372130208 丰润收费站 上行 2016/8/4 7 0 90
G25L372130208 丰润收费站 上行 2016/8/4 7 35 85
G25L372130208 丰润收费站 上行 2016/8/4 8 5 86
G25L372130208 丰润收费站 上行 2016/8/4 8 40 138.5
G25L372130208 丰润收费站 上行 2016/8/4 9 30 109
G25L372130208 丰润收费站 上行 2016/8/4 10 0 101
G25L372130208 丰润收费站 上行 2016/8/4 10 35 87
G25L372130208 丰润收费站 上行 2016/8/4 11 5 58
G25L372130208 丰润收费站 上行 2016/8/4 12 30 59
G25L372130208 丰润收费站 上行 2016/8/4 13 0 48
G25L372130208 丰润收费站 上行 2016/8/4 13 35 81
G25L372130208 丰润收费站 上行 2016/8/4 13 55 74
G25L372130208 丰润收费站 上行 2016/8/4 14 25 76.5
G25L372130208 丰润收费站 上行 2016/8/4 15 30 73
G25L372130208 丰润收费站 上行 2016/8/4 16 0 104.5
G25L372130208 丰润收费站 上行 2016/8/4 16 55 117.5
G25L372130208 丰润收费站 上行 2016/8/4 17 25 105
G25L372130208 丰润收费站 上行 2016/8/4 18 30 96.5
G25L372130208 丰润收费站 上行 2016/8/4 18 50 97.5
G25L372130208 丰润收费站 上行 2016/8/4 19 20 71.5
G25L372130208 丰润收费站 上行 2016/8/4 19 55 83.5
G25L372130208 丰润收费站 上行 2016/8/4 20 25 66
G25L372130208 丰润收费站 上行 2016/8/4 21 50 50
G25L372130208 丰润收费站 上行 2016/8/4 22 20 70
G25L372130208 丰润收费站 上行 2016/8/4 22 55 35
G25L372130208 丰润收费站 上行 2016/8/4 23 25 38.5
G25L372130208 丰润收费站 上行 2016/8/4 23 45 21.5
G25L372130208 丰润收费站 上行 2016/8/4 24 15 39
G25L372130208 丰润收费站 上行 2016/8/4 24 50 65.5
G25L372130208 丰润收费站 上行 2016/8/5 1 5 23
G25L372130208 丰润收费站 上行 2016/8/5 1 40 29.5
G25L372130208 丰润收费站 上行 2016/8/5 2 10 18
G25L372130208 丰润收费站 上行 2016/8/5 2 45 17.5
G25L372130208 丰润收费站 上行 2016/8/5 3 35 27.5
G25L372130208 丰润收费站 上行 2016/8/5 4 5 10
G25L372130208 丰润收费站 上行 2016/8/5 4 40 35
有如下要求：采用t 时段之前n（n=12）个数据的平均值q平均、方差σ来识别此类数据。
当（q平均－2σ≤ Q ≤q平均＋2σ）时，认为当量数据是正常的，否则认为数据为异常数据。
σ这样求解，
大神之前帮了很多，可是自己按照大神的改编，得出的结果却是错误的，不得已还得求助大神啊，
...全文
304 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
zjcxc 2018-01-25
  • 打赏
  • 举报
 回复
如果上面的理解没错，还可以再简化成这样（你的日期+时分的表示方法，建议加个计算列，换成成真正的日期，这样日期排序和比较在单字段上就可以） 
SELECT *
FROM tb A
	CROSS APPLY(
		SELECT SQRT( SUM(V) ) as 计算当量
		FROM(
			SELECT TOP(12)
				POWER(B.当量 - AVG(B.当量)OVER(ORDER BY GETDATE()), 2) as v
			FROM tb B
			WHERE B.观测站名称 = A.观测站名称 AND B.行驶方向 = A.行驶方向
				AND( -- t 时段之前n（n=12）个数据， 这个判断你自己再确定 
					B.观测日期 < A.观测日期
					OR B.观测日期 = A.观测日期 AND B.小时 < A.小时
					OR B.观测日期 = A.观测日期 AND B.小时 = A.小时 AND B.分钟 < A.分钟
				)
			ORDER BY B.观测日期 DESC, B.小时 DESC, B.分钟 DESC
		) TOP12
	) V
WHERE 条件
zjcxc 2018-01-25
  • 打赏
  • 举报
 回复
根据你后面说的算法，大致是这么回事吧 
SELECT *
FROM tb A
	CROSS APPLY(
		SELECT SQRT( (
			POWER([1]-pavg.v,2) + POWER([2]-pavg.v,2) + POWER([3]-pavg.v,2) + POWER([4]-pavg.v,2) +
			POWER([5]-pavg.v,2) + POWER([6]-pavg.v,2) + POWER([7]-pavg.v,2) + POWER([8]-pavg.v,2) +
			POWER([0]-pavg.v,2) + POWER([10]-pavg.v,2) + POWER([11]-pavg.v,2) + POWER([12]-pavg.v,2)
			)/12) as 计算当量
		FROM(
			SELECT TOP(12)
				当量, ROW_NUMBER()OVER(ORDER BY B.观测日期 DESC, B.小时 DESC, B.分钟 DESC) as _r
			FROM tb B
			WHERE B.观测站名称 = A.观测站名称 AND B.行驶方向 = A.行驶方向
				AND( -- t 时段之前n（n=12）个数据， 这个判断你自己再确定 
					B.观测日期 < A.观测日期
					OR B.观测日期 = A.观测日期 AND B.小时 < A.小时
					OR B.观测日期 = A.观测日期 AND B.小时 = A.小时 AND B.分钟 < A.分钟
				)
			ORDER BY B.观测日期 DESC, B.小时 DESC, B.分钟 DESC
		) TOP12
			PIVOT( MAX(TOP12.当量) FOR _r IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) )p
			CROSS APPLY(SELECT ([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12])/12.) pavg(v) -- 平均值
	) V
WHERE 条件
weixin_40101242 2018-01-25
  • 打赏
  • 举报
 回复
引用 7 楼 sinat_28984567 的回复:
[quote=引用 6 楼 weixin_40101242 的回复:] [quote=引用 2 楼 sinat_28984567 的回复:] 这个还是借助程序实现吧 百度 Sql Server CLR http://blog.csdn.net/ddc201301/article/details/53671085
用c#编写这个算法，然后再用数据库调用啊。[/quote] 比在数据库中写好多了吧[/quote] 关键是c#我也不懂，只会连接数据库调用数据
二月十六 2018-01-25
  • 打赏
  • 举报
 回复
引用 6 楼 weixin_40101242 的回复:
[quote=引用 2 楼 sinat_28984567 的回复:] 这个还是借助程序实现吧 百度 Sql Server CLR http://blog.csdn.net/ddc201301/article/details/53671085
用c#编写这个算法，然后再用数据库调用啊。[/quote] 比在数据库中写好多了吧
weixin_40101242 2018-01-25
  • 打赏
  • 举报
 回复
引用 2 楼 sinat_28984567 的回复:
这个还是借助程序实现吧 百度 Sql Server CLR http://blog.csdn.net/ddc201301/article/details/53671085
用c#编写这个算法，然后再用数据库调用啊。
weixin_40101242 2018-01-25
  • 打赏
  • 举报
 回复
引用 3 楼 zjcxc 的回复:
数学老师就没教我这知识啊

可以这样理解，假如有三个数，1、2、3，q平均为（1+2+3）/3=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 @a VARCHAR(20),
@b VARCHAR(20),
@c DATETIME
DECLARE test_cur CURSOR FOR
SELECT 观测站名称,行驶方向,dt FROM #t_0

OPEN test_cur
FETCH NEXT FROM test_cur INTO @a,@b,@c
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @rn INT,--行号
@t float,
@dl 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 @dl1=0
set @dl2=0
set @dl3=0
set @dl4=0
set @dl5=0
set @dl6=0
set @dl7=0
set @dl8=0
set @dl9=0
set @dl10=0
set @dl11=0
set @dl12=0
set @t=0
select @rn=rn from #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and dt=@c
SELECT @dl1=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-1

SELECT @dl2=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-2
SELECT @rn=rn,@dl3=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-3
SELECT @rn=rn,@dl4=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-4
SELECT @rn=rn,@dl5=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-5
SELECT @rn=rn,@dl6=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-6
SELECT @rn=rn,@dl7=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-7
SELECT @rn=rn,@dl8=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-8
SELECT @rn=rn,@dl9=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-9
SELECT @rn=rn,@dl10=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-10
SELECT @rn=rn,@dl11=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-11
SELECT @rn=rn,@dl12=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-12
--PRINT @a
--PRINT @b
--PRINT @c
--SELECT @c
SELECT @dl=(@dl1+@dl2+@dl3+@dl4+@dl5+@dl6+@dl7+@dl8+@dl9+@dl10+@dl11+@dl12)/12 FROM #tabB2
WHERE
观测站名称=@a
and 行驶方向=@b
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
WHERE
观测站名称=@a
and 行驶方向=@b
and dt=@c
IF @rn<>0
select 观测站名称,行驶方向,dt,当量,@dl-2*sqrt(@t) as f1,@dl+2*sqrt(@t) as f2 from #t_0
ELSE
PRINT @a+'--'+@b+'--'+convert(varchar(100),@c,120)+'在表#tabB中未找到对应的时间'
FETCH NEXT FROM test_cur INTO @a,@b,@c
END
CLOSE test_cur
DEALLOCATE test_cur
weixin_40101242 2018-01-25
  • 打赏
  • 举报
 回复
引用 3 楼 zjcxc 的回复:
数学老师就没教我这知识啊

可以这样理解，假如有三个数，1、2、3，q平均为（1+2+3）/3=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 @a VARCHAR(20),
@b VARCHAR(20),
@c DATETIME
DECLARE test_cur CURSOR FOR
SELECT 观测站名称,行驶方向,dt FROM #t_0

OPEN test_cur
FETCH NEXT FROM test_cur INTO @a,@b,@c
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @rn INT,--行号
@t float,
@dl 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 @dl1=0
set @dl2=0
set @dl3=0
set @dl4=0
set @dl5=0
set @dl6=0
set @dl7=0
set @dl8=0
set @dl9=0
set @dl10=0
set @dl11=0
set @dl12=0
set @t=0
select @rn=rn from #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and dt=@c
SELECT @dl1=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-1

SELECT @dl2=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-2
SELECT @rn=rn,@dl3=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-3
SELECT @rn=rn,@dl4=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-4
SELECT @rn=rn,@dl5=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-5
SELECT @rn=rn,@dl6=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-6
SELECT @rn=rn,@dl7=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-7
SELECT @rn=rn,@dl8=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-8
SELECT @rn=rn,@dl9=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-9
SELECT @rn=rn,@dl10=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-10
SELECT @rn=rn,@dl11=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-11
SELECT @rn=rn,@dl12=当量 FROM #tabB2
WHERE 观测站名称=@a
and 行驶方向=@b
and rn=@rn-12
--PRINT @a
--PRINT @b
--PRINT @c
--SELECT @c
SELECT @dl=(@dl1+@dl2+@dl3+@dl4+@dl5+@dl6+@dl7+@dl8+@dl9+@dl10+@dl11+@dl12)/12 FROM #tabB2
WHERE
观测站名称=@a
and 行驶方向=@b
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
WHERE
观测站名称=@a
and 行驶方向=@b
and dt=@c
IF @rn<>0
select 观测站名称,行驶方向,dt,当量,@dl-2*sqrt(@t) as f1,@dl+2*sqrt(@t) as f2 from #t_0
ELSE
PRINT @a+'--'+@b+'--'+convert(varchar(100),@c,120)+'在表#tabB中未找到对应的时间'
FETCH NEXT FROM test_cur INTO @a,@b,@c
END
CLOSE test_cur
DEALLOCATE test_cur
zjcxc 2018-01-25
  • 打赏
  • 举报
 回复
数学老师就没教我这知识啊
二月十六 2018-01-25
  • 打赏
  • 举报
 回复
这个还是借助程序实现吧 百度 Sql Server CLR http://blog.csdn.net/ddc201301/article/details/53671085
OwenZeng_DBA 2018-01-25
  • 打赏
  • 举报
 回复
这种复杂的运算用程序代码来实现比较合适
Android源代码开发和调试环境搭建完整版PPT
这对应用开发也是非常有用的，因为在开发中碰到疑难杂症时可以跟踪到系统内部去定位问题。对于涉及到C/C++代码的开发，例如JNI开发和安全相关开发，更加建议在Android源代码开发环境进行，这样就可以利用gdb以及...
专治疑难系列 - 解决“npm ERR!”报错问题
一、问题描述 二、解决办法 三、问题已解决
WPF疑难解惑(1)
本课程内容来自于学员们在实际工作中遇到的与WPF相关各种疑难问题。 课程中的问题，皆由学员在交流群中提出，并由赵庆明老师亲自解答。赵庆明老师解答之后，将其录制为视频，供其他学员参考和学习。 本课程将为《WPF...
云计算疑难杂症解决方案一
问题描述： 基于共享存储的环境，如果基于模板批量启动很多虚拟机，会出现启动很慢的情况，甚至导致虚拟机启动失败； 问题的原因是： 网络带宽不足，共享存储iops不够，或者突然碰到存储内在进行数据重构之类的； ...
呕心沥血整理，python离线安装第三方库（带疑难杂症实例）！！！
文章目录项目场景解决方案第一步 ...解决疑难杂症4.1 先将这两个库分别解压4.2 使用 setup.py 安装5.见证奇迹的时刻小结统一声明 项目场景   银行项目，出于安全性考虑，无法连接外网； 平常安装 python 第
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章