统计监测异常情况的开始与恢复时间段

CruelYoung123 2013-11-04 04:26:13
如下所示：

数据库状态为：

时间 状态
2013-10-20 00:01:50 200
2013-10-20 00:04:45 200
2013-10-20 00:08:27 200
2013-10-20 00:11:17 200
2013-10-20 00:14:07 200
2013-10-20 00:17:04 400
2013-10-20 00:21:15 400
2013-10-20 00:24:15 400
2013-10-20 00:28:29 400
2013-10-20 00:32:21 200
2013-10-20 00:35:10 200
2013-10-20 00:38:51 200
2013-10-20 00:41:40 400
2013-10-20 00:44:30 200
2013-10-20 00:47:22 200
2013-10-20 00:50:46 200
2013-10-20 00:54:19 200
2013-10-20 00:58:12 200
2013-10-20 01:01:02 200
2013-10-20 01:03:55 200
2013-10-20 01:08:15 200
2013-10-20 01:11:39 200
2013-10-20 01:15:19 200
2013-10-20 01:20:11 200
2013-10-20 01:24:10 200
2013-10-20 01:28:08 200
2013-10-20 01:32:04 200
2013-10-20 01:34:55 200
2013-10-20 01:38:16 200
2013-10-20 01:41:21 200
2013-10-20 01:45:21 200
2013-10-20 01:51:27 200
2013-10-20 01:56:31 200
2013-10-20 02:03:00 200
2013-10-20 02:08:01 200
2013-10-20 02:13:40 200
2013-10-20 02:18:37 200
2013-10-20 02:23:38 200
2013-10-20 02:30:06 200
2013-10-20 02:33:55 200
2013-10-20 02:37:44 200
2013-10-20 02:41:41 200
2013-10-20 02:45:35 200
2013-10-20 02:51:04 200
2013-10-20 02:55:13 200
2013-10-20 02:59:24 200
2013-10-20 03:03:15 200
2013-10-20 03:07:04 200
2013-10-20 03:11:25 1001
2013-10-20 03:16:20 1001
2013-10-20 03:21:42 200
2013-10-20 03:25:32 200
2013-10-20 03:29:23 200
2013-10-20 03:33:15 200
2013-10-20 03:38:19 200
2013-10-20 03:42:09 200
2013-10-20 03:45:59 200
2013-10-20 03:50:25 200
2013-10-20 03:54:15 200
2013-10-20 03:58:17 200
2013-10-20 04:02:07 1003
2013-10-20 04:06:00 200
2013-10-20 04:11:20 200
2013-10-20 04:16:14 200
2013-10-20 04:20:57 200
2013-10-20 04:24:48 200
2013-10-20 04:28:47 200
2013-10-20 04:32:39 200



以上状态200为正常情况， 需要统计不正常的开始时间及恢复正常的时间，以及不正常的持续时间间隔，希望用SQL语句得到如下自动化的结果。
序号 开始时间 恢复时间 时间间隔
1
...全文
267 13 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
CruelYoung123 2013-11-05
  • 打赏
  • 举报
 回复
序号 开始时间 恢复时间 时间间隔 1 2013-10-20 00:17:04 2013-10-20 00:32:21 15 2 2013-10-20 00:21:15 2013-10-20 00:32:21 11 3 2013-10-20 00:24:15 2013-10-20 00:32:21 8 4 2013-10-20 00:28:29 2013-10-20 00:32:21 4 这个4条记录恢复时间都是2013-10-20 00:32:21， 这里重复四条。。 其实异常时间段就是 序号 开始时间 恢复时间 时间间隔 1 2013-10-20 00:17:04 2013-10-20 00:32:21 15 后面2.3.4 是多余重复统计， 也是不正确的。。。
CruelYoung123 2013-11-05
  • 打赏
  • 举报
 回复
非常感谢。。。。各位
Leon_He2014 2013-11-05
  • 打赏
  • 举报
 回复 

select '2013-10-20 00:01:50' as statetime,200 stateid
into #temp
union all select '2013-10-20 00:04:45',200
union all select '2013-10-20 00:08:27',200
union all select '2013-10-20 00:11:17',200
union all select '2013-10-20 00:14:07',200
union all select '2013-10-20 00:17:04',400
union all select '2013-10-20 00:21:15',400
union all select '2013-10-20 00:24:15',400
union all select '2013-10-20 00:28:29',400
union all select '2013-10-20 00:32:21',200
union all select '2013-10-20 00:35:10',200
union all select '2013-10-20 00:38:51',200
union all select '2013-10-20 00:41:40',400
union all select '2013-10-20 00:44:30',200
union all select '2013-10-20 00:47:22',200
union all select '2013-10-20 00:50:46',200
union all select '2013-10-20 00:54:19',200
union all select '2013-10-20 00:58:12',200
union all select '2013-10-20 01:01:02',200
union all select '2013-10-20 01:03:55',200
union all select '2013-10-20 01:08:15',200
union all select '2013-10-20 01:11:39',200
union all select '2013-10-20 01:15:19',200
union all select '2013-10-20 01:20:11',200
union all select '2013-10-20 01:24:10',200
union all select '2013-10-20 01:28:08',200
union all select '2013-10-20 01:32:04',200
union all select '2013-10-20 01:34:55',200
union all select '2013-10-20 01:38:16',200
union all select '2013-10-20 01:41:21',200
union all select '2013-10-20 01:45:21',200
union all select '2013-10-20 01:51:27',200
union all select '2013-10-20 01:56:31',200
union all select '2013-10-20 02:03:00',200
union all select '2013-10-20 02:08:01',200
union all select '2013-10-20 02:13:40',200
union all select '2013-10-20 02:18:37',200
union all select '2013-10-20 02:23:38',200
union all select '2013-10-20 02:30:06',200
union all select '2013-10-20 02:33:55',200
union all select '2013-10-20 02:37:44',200
union all select '2013-10-20 02:41:41',200
union all select '2013-10-20 02:45:35',200
union all select '2013-10-20 02:51:04',200
union all select '2013-10-20 02:55:13',200
union all select '2013-10-20 02:59:24',200
union all select '2013-10-20 03:03:15',200
union all select '2013-10-20 03:07:04',200
union all select '2013-10-20 03:11:25',1001
union all select '2013-10-20 03:16:20',1001
union all select '2013-10-20 03:21:42',200
union all select '2013-10-20 03:25:32',200
union all select '2013-10-20 03:29:23',200
union all select '2013-10-20 03:33:15',200
union all select '2013-10-20 03:38:19',200
union all select '2013-10-20 03:42:09',200
union all select '2013-10-20 03:45:59',200
union all select '2013-10-20 03:50:25',200
union all select '2013-10-20 03:54:15',200
union all select '2013-10-20 03:58:17',200
union all select '2013-10-20 04:02:07',1003
union all select '2013-10-20 04:06:00',200
union all select '2013-10-20 04:11:20',200
union all select '2013-10-20 04:16:14',200
union all select '2013-10-20 04:20:57',200
union all select '2013-10-20 04:24:48',200
union all select '2013-10-20 04:28:47',200
union all select '2013-10-20 04:32:39',200

select ROW_NUMBER() over(order by gptime) id ,min(statetime) statetime,gptime endtime
,DATEDIFF(minute,min(statetime),gptime) timediff
from
(
select *
,(select min(statetime) from #temp b where b.statetime>=a.statetime and b.stateid=200) gptime
from #temp a
) t
where stateid<>200
group by gptime

/*
id	statetime	endtime	timediff
1	2013-10-20 00:17:04	2013-10-20 00:32:21	15
2	2013-10-20 00:41:40	2013-10-20 00:44:30	3
3	2013-10-20 03:11:25	2013-10-20 03:21:42	10
4	2013-10-20 04:02:07	2013-10-20 04:06:00	4
*/
CruelYoung123 2013-11-05
  • 打赏
  • 举报
 回复
还请各位帮忙解决一下。。。 2013-10-20 00:17:04 到 2013-10-20 00:32:21只算一次异常状态
LongRui888 2013-11-04
  • 打赏
  • 举报
 回复
引用 7 楼 CruelYoung123 的回复:
这个语句还是有一些问题。。。连接异常的应该只报一次，如 这个结果。。第一条应该是： 1 2013-10-20 00:17:04 2013-10-20 00:32:21 15 2.3.4条其实就是算一次异常时间问题。
后面3条是一条，这个怎么定义呢
CruelYoung123 2013-11-04
  • 打赏
  • 举报
 回复
这个语句还是有一些问题。。。连接异常的应该只报一次，如 这个结果。。第一条应该是： 1 2013-10-20 00:17:04 2013-10-20 00:32:21 15 2.3.4条其实就是算一次异常时间问题。
ai_li7758521 2013-11-04
  • 打赏
  • 举报
 回复
引用 2 楼 CruelYoung123 的回复:
谢谢楼上。。。但是。。。在数据库中数据是海量的。。。 那个union select怎么操作哈？
其中CTE就相当于你自己的数据表。用你的数据表名替换CTE即可。
ai_li7758521 2013-11-04
  • 打赏
  • 举报
 回复 
WITH CTE(时间,状态)
AS
(
 select '2013-10-20 00:01:50',200 union all 
 select '2013-10-20 00:04:45',200 union all 
 select '2013-10-20 00:08:27',200 union all 
 select '2013-10-20 00:11:17',200 union all 
 select '2013-10-20 00:14:07',200 union all 
 select '2013-10-20 00:17:04',400 union all 
 select '2013-10-20 00:21:15',400 union all 
 select '2013-10-20 00:24:15',400 union all 
 select '2013-10-20 00:28:29',400 union all 
 select '2013-10-20 00:32:21',200 union all 
 select '2013-10-20 00:35:10',200 union all 
 select '2013-10-20 00:38:51',200 union all 
 select '2013-10-20 00:41:40',400 union all 
 select '2013-10-20 00:44:30',200 union all 
 select '2013-10-20 00:47:22',200 union all 
 select '2013-10-20 00:50:46',200 union all 
 select '2013-10-20 00:54:19',200 union all 
 select '2013-10-20 00:58:12',200 union all 
 select '2013-10-20 01:01:02',200 union all 
 select '2013-10-20 01:03:55',200 union all 
 select '2013-10-20 01:08:15',200 union all 
 select '2013-10-20 01:11:39',200 union all 
 select '2013-10-20 01:15:19',200 union all 
 select '2013-10-20 01:20:11',200 union all 
 select '2013-10-20 01:24:10',200 union all 
 select '2013-10-20 01:28:08',200 union all 
 select '2013-10-20 01:32:04',200 union all 
 select '2013-10-20 01:34:55',200 union all 
 select '2013-10-20 01:38:16',200 union all 
 select '2013-10-20 01:41:21',200 union all 
 select '2013-10-20 01:45:21',200 union all 
 select '2013-10-20 01:51:27',200 union all 
 select '2013-10-20 01:56:31',200 union all 
 select '2013-10-20 02:03:00',200 union all 
 select '2013-10-20 02:08:01',200 union all 
 select '2013-10-20 02:13:40',200 union all 
 select '2013-10-20 02:18:37',200 union all 
 select '2013-10-20 02:23:38',200 union all 
 select '2013-10-20 02:30:06',200 union all 
 select '2013-10-20 02:33:55',200 union all 
 select '2013-10-20 02:37:44',200 union all 
 select '2013-10-20 02:41:41',200 union all 
 select '2013-10-20 02:45:35',200 union all 
 select '2013-10-20 02:51:04',200 union all 
 select '2013-10-20 02:55:13',200 union all 
 select '2013-10-20 02:59:24',200 union all 
 select '2013-10-20 03:03:15',200 union all 
 select '2013-10-20 03:07:04',200 union all 
 select '2013-10-20 03:11:25',1001 union all 
 select '2013-10-20 03:16:20',1001 union all 
 select '2013-10-20 03:21:42',200 union all 
 select '2013-10-20 03:25:32',200 union all 
 select '2013-10-20 03:29:23',200 union all 
 select '2013-10-20 03:33:15',200 union all 
 select '2013-10-20 03:38:19',200 union all 
 select '2013-10-20 03:42:09',200 union all 
 select '2013-10-20 03:45:59',200 union all 
 select '2013-10-20 03:50:25',200 union all 
 select '2013-10-20 03:54:15',200 union all 
 select '2013-10-20 03:58:17',200 union all 
 select '2013-10-20 04:02:07',1003 union all 
 select '2013-10-20 04:06:00',200 union all 
 select '2013-10-20 04:11:20',200 union all 
 select '2013-10-20 04:16:14',200 union all 
 select '2013-10-20 04:20:57',200 union all 
 select '2013-10-20 04:24:48',200 union all 
 select '2013-10-20 04:28:47',200 union all 
 select '2013-10-20 04:32:39',200
),
C1 AS
(
	SELECT G=ROW_NUMBER() OVER(ORDER BY [时间])-RANK() OVER(PARTITION BY [状态] ORDER BY [时间]),*
	FROM CTE
),
C2 AS
(
	SELECT 开始时间=MIN([时间]),恢复时间=MAX([时间]),时间间隔 =DATEDIFF(SECOND,MIN([时间]),MAX([时间]))
	FROM C1
	WHERE 状态!=200
	GROUP BY G
)

SELECT 序号=ROW_NUMBER() OVER(ORDER BY 开始时间),*
FROM C2

--序号	开始时间	恢复时间	时间间隔
--1	2013-10-20 00:17:04	2013-10-20 00:28:29	685
--2	2013-10-20 00:41:40	2013-10-20 00:41:40	0
--3	2013-10-20 03:11:25	2013-10-20 03:16:20	295
--4	2013-10-20 04:02:07	2013-10-20 04:02:07	0
Andy__Huang 2013-11-04
  • 打赏
  • 举报
 回复
你的表不是已经存储有数据了吗，直接从你的表里面select出来就可以，也就用句这个语句就可以了 
select 序号=ROW_NUMBER() over(order by 时间),
	开始时间=时间,恢复时间=时间2,时间间隔=DATEDIFF(minute,时间,时间2)
from 
(
select *,时间2=(select top 1 时间 from [你的表] b where b.时间>a.时间 and b.状态=200)
from [你的表] a
where 状态<>200
)t
發糞塗牆 2013-11-04
  • 打赏
  • 举报
 回复
他只是造数据，看下面的select就可以了
CruelYoung123 2013-11-04
  • 打赏
  • 举报
 回复
谢谢楼上。。。但是。。。在数据库中数据是海量的。。。 那个union select怎么操作哈？
Andy__Huang 2013-11-04
  • 打赏
  • 举报
 回复 
;with cte(时间,状态) as
(
select '2013-10-20 00:01:50',200
union all select '2013-10-20 00:04:45',200
union all select '2013-10-20 00:08:27',200
union all select '2013-10-20 00:11:17',200
union all select '2013-10-20 00:14:07',200
union all select '2013-10-20 00:17:04',400
union all select '2013-10-20 00:21:15',400
union all select '2013-10-20 00:24:15',400
union all select '2013-10-20 00:28:29',400
union all select '2013-10-20 00:32:21',200
union all select '2013-10-20 00:35:10',200
union all select '2013-10-20 00:38:51',200
union all select '2013-10-20 00:41:40',400
union all select '2013-10-20 00:44:30',200
union all select '2013-10-20 00:47:22',200
union all select '2013-10-20 00:50:46',200
union all select '2013-10-20 00:54:19',200
union all select '2013-10-20 00:58:12',200
union all select '2013-10-20 01:01:02',200
union all select '2013-10-20 01:03:55',200
union all select '2013-10-20 01:08:15',200
union all select '2013-10-20 01:11:39',200
union all select '2013-10-20 01:15:19',200
union all select '2013-10-20 01:20:11',200
union all select '2013-10-20 01:24:10',200
union all select '2013-10-20 01:28:08',200
union all select '2013-10-20 01:32:04',200
union all select '2013-10-20 01:34:55',200
union all select '2013-10-20 01:38:16',200
union all select '2013-10-20 01:41:21',200
union all select '2013-10-20 01:45:21',200
union all select '2013-10-20 01:51:27',200
union all select '2013-10-20 01:56:31',200
union all select '2013-10-20 02:03:00',200
union all select '2013-10-20 02:08:01',200
union all select '2013-10-20 02:13:40',200
union all select '2013-10-20 02:18:37',200
union all select '2013-10-20 02:23:38',200
union all select '2013-10-20 02:30:06',200
union all select '2013-10-20 02:33:55',200
union all select '2013-10-20 02:37:44',200
union all select '2013-10-20 02:41:41',200
union all select '2013-10-20 02:45:35',200
union all select '2013-10-20 02:51:04',200
union all select '2013-10-20 02:55:13',200
union all select '2013-10-20 02:59:24',200
union all select '2013-10-20 03:03:15',200
union all select '2013-10-20 03:07:04',200
union all select '2013-10-20 03:11:25',1001
union all select '2013-10-20 03:16:20',1001
union all select '2013-10-20 03:21:42',200
union all select '2013-10-20 03:25:32',200
union all select '2013-10-20 03:29:23',200
union all select '2013-10-20 03:33:15',200
union all select '2013-10-20 03:38:19',200
union all select '2013-10-20 03:42:09',200
union all select '2013-10-20 03:45:59',200
union all select '2013-10-20 03:50:25',200
union all select '2013-10-20 03:54:15',200
union all select '2013-10-20 03:58:17',200
union all select '2013-10-20 04:02:07',1003
union all select '2013-10-20 04:06:00',200
union all select '2013-10-20 04:11:20',200
union all select '2013-10-20 04:16:14',200
union all select '2013-10-20 04:20:57',200
union all select '2013-10-20 04:24:48',200
union all select '2013-10-20 04:28:47',200
union all select '2013-10-20 04:32:39',200
)
select 序号=ROW_NUMBER() over(order by 时间),
	开始时间=时间,恢复时间=时间2,时间间隔=DATEDIFF(minute,时间,时间2)
from 
(
select *,时间2=(select top 1 时间 from cte b where b.时间>a.时间 and b.状态=200)
from cte a
where 状态<>200
)t

/*
序号	开始时间	恢复时间	时间间隔
1	2013-10-20 00:17:04	2013-10-20 00:32:21	15
2	2013-10-20 00:21:15	2013-10-20 00:32:21	11
3	2013-10-20 00:24:15	2013-10-20 00:32:21	8
4	2013-10-20 00:28:29	2013-10-20 00:32:21	4
5	2013-10-20 00:41:40	2013-10-20 00:44:30	3
6	2013-10-20 03:11:25	2013-10-20 03:21:42	10
7	2013-10-20 03:16:20	2013-10-20 03:21:42	5
8	2013-10-20 04:02:07	2013-10-20 04:06:00	4
*/
实时水情监测数据异常诊断与插补技术方法
简介：本文档介绍了一种应用于水情监测领域的实时异常诊断与数据插补技术。这项技术主要包括实时监测河流、湖泊、水库等的水量、水质、水位等参数，用于洪水预警、水资源管理和环境保护。文档详细阐述了数据采集、...
大数据分析工具构建智能监测与异常预警
程序监测和异常预警就是来保障系统服务持续可用的两大举措，一方面需要对故障和异常及时发现，另一方面及时通知并...系统智能监测与异常预警，主要从三个方面入手，分别是：运行环境监测、服务状态监测、服务性能监测。
Python数据分析案例55——基于LSTM结构自编码器的多变量时间序列异常值监测
构建了基于lstm的自编码器结构的多变量时间序列异常值监测。
Apikit 自学日记：API 异常监控-监控报告
在 api 管理中，查看 api 异常监控的监控报告，在 apikit 中也是常用的功能，通常你可以在流程综合报告页中看到当前流程在选定时间段内的整体监控情况...
单日2000W+订单，如何忙中不错？美团外卖业务异常检测实践详解
目前单日完成订单两千多万单，交易频次高，如果不能及时发现潜在业务指标异常，有可能引发重大事故。 写在前面 外卖业务持续高速成长，业务迭代快，逻辑复杂，关联服务多。如何快速准确识别系统各项指标的异常，...
疑难问题

22,301

社区成员

121,734

社区内容

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

加载中

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

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

+ 用AI写文章