好难的一句sql,求各位大神帮忙

liuyinfeng 2017-03-16 07:49:20
数据结构如下:
时间 value值
2017-01-01 00:00:01 273
2017-01-01 00:01:01 272
2017-01-01 00:02:01 271
2017-01-01 00:03:01 272
2017-01-01 00:04:01 271
2017-01-01 00:05:01 265
2017-01-01 00:06:01 272
2017-01-01 00:07:01 276
2017-01-01 00:08:01 272
2017-01-01 00:09:01 278
2017-01-01 00:10:01 279
2017-01-01 00:11:01 268
2017-01-01 00:12:01 272
2017-01-01 00:13:01 271
2017-01-01 00:14:01 268
2017-01-01 00:15:01 271
2017-01-01 00:16:01 268

求value值大于270的,持续时间大于等于4秒的,各次报警时长,例如上述例子应返回两条记录

开始时间 结束时间 时长
2017-01-01 00:00:01 2017-01-01 00:04:01 4秒
2017-01-01 00:06:01 2017-01-01 00:10:01 4秒

由于数据库权限问题,不能使用存储过程,不能使用临时表,单纯sql完成,求各位大神指点
...全文
502 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
中国风 2017-03-16
  • 打赏
  • 举报
回复
value值大于270的,持续时间大于等于4的 ------- 应该是4分钟吧 e.g.
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
declare @T table([Date1] Datetime,[value] int)
Insert @T
select '2017-01-01 00:00:01',273 union all
select '2017-01-01 00:01:01',272 union all
select '2017-01-01 00:02:01',271 union all
select '2017-01-01 00:03:01',272 union all
select '2017-01-01 00:04:01',271 union all
select '2017-01-01 00:05:01',265 union all
select '2017-01-01 00:06:01',272 union all
select '2017-01-01 00:07:01',276 union all
select '2017-01-01 00:08:01',272 union all
select '2017-01-01 00:09:01',278 union all
select '2017-01-01 00:10:01',279 union all
select '2017-01-01 00:11:01',268 union all
select '2017-01-01 00:12:01',272 union all
select '2017-01-01 00:13:01',271 union all
select '2017-01-01 00:14:01',268 union all
select '2017-01-01 00:15:01',271 union all
select '2017-01-01 00:16:01',268
 
SELECT  MIN([Date1]) AS 开始时间 ,
        MAX([Date1]) AS 结束时间,
		DATEDIFF(n, MIN([Date1]), MAX([Date1]))  AS 时长
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( ORDER BY [Date1] )
                    - ROW_NUMBER() OVER ( PARTITION BY SIGN(value - 270) ORDER BY [Date1] ) AS Grp
          FROM      @T
        ) AS t
WHERE   t.value > 270
GROUP BY Grp
HAVING  DATEDIFF(n, MIN([Date1]), MAX([Date1])) >= 4;

/*
开始时间	结束时间	时长
2017-01-01 00:00:01.000	2017-01-01 00:04:01.000	4
2017-01-01 00:06:01.000	2017-01-01 00:10:01.000	4
*/
二月十六 2017-03-16
  • 打赏
  • 举报
回复
上边那个稍微麻烦点,又改了改,效果一样
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
CREATE TABLE #T(time DATETIME,value INT)
Insert #T
select '2017-01-01 00:00:01',273 union all
select '2017-01-01 00:01:01',272 union all
select '2017-01-01 00:02:01',271 union all
select '2017-01-01 00:03:01',272 union all
select '2017-01-01 00:04:01',271 union all
select '2017-01-01 00:05:01',265 union all
select '2017-01-01 00:06:01',272 union all
select '2017-01-01 00:07:01',276 union all
select '2017-01-01 00:08:01',272 union all
select '2017-01-01 00:09:01',278 union all
select '2017-01-01 00:10:01',279 union all
select '2017-01-01 00:11:01',268 union all
select '2017-01-01 00:12:01',272 union all
select '2017-01-01 00:13:01',271 union all
select '2017-01-01 00:14:01',268 union all
select '2017-01-01 00:15:01',271 union all
select '2017-01-01 00:16:01',268
Go
--测试数据结束
;WITH tempa AS (
SELECT * ,
DATEADD(MINUTE,
-ROW_NUMBER() OVER ( ORDER BY CASE WHEN a.value > 270 THEN 1
ELSE 0
END, a.time ), time) AS num
FROM #T a
)
SELECT MIN(tempa.time) AS 开始时间 ,
MAX(tempa.time) AS 结束时间 ,
DATEDIFF(MINUTE, MIN(tempa.time), MAX(tempa.time)) AS 时长
FROM tempa
GROUP BY tempa.num
HAVING COUNT(1)>=4
二月十六 2017-03-16
  • 打赏
  • 举报
回复
语句:
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
CREATE TABLE #T(time DATETIME,value INT)
Insert #T
select '2017-01-01 00:00:01',273 union all
select '2017-01-01 00:01:01',272 union all
select '2017-01-01 00:02:01',271 union all
select '2017-01-01 00:03:01',272 union all
select '2017-01-01 00:04:01',271 union all
select '2017-01-01 00:05:01',265 union all
select '2017-01-01 00:06:01',272 union all
select '2017-01-01 00:07:01',276 union all
select '2017-01-01 00:08:01',272 union all
select '2017-01-01 00:09:01',278 union all
select '2017-01-01 00:10:01',279 union all
select '2017-01-01 00:11:01',268 union all
select '2017-01-01 00:12:01',272 union all
select '2017-01-01 00:13:01',271 union all
select '2017-01-01 00:14:01',268 union all
select '2017-01-01 00:15:01',271 union all
select '2017-01-01 00:16:01',268
Go
--测试数据结束
;WITH a AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY a.time) num FROM #T a
),b AS (
SELECT *,
ROW_NUMBER() OVER(ORDER BY CASE WHEN a.value>270 THEN 100 ELSE -100 END,num)-num gn
FROM a
)
SELECT MIN(b.time) AS 开始时间 ,
MAX(b.time) AS 结束时间 ,
DATEDIFF(MINUTE, MIN(b.time),MAX(b.time) ) AS 时长
FROM b
GROUP BY b.gn
HAVING COUNT(1) >= 4;


结果:
RINK_1 2017-03-16
  • 打赏
  • 举报
回复
SELECT MIN(TIME) AS MIN_TIME,MAX(TIME) AS MAX_TIME,DATEDIFF(MI,MIN(TIME),MAX(TIME)) AS TIME_LEN FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY TIME) AS RN2 FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY TIME) AS RN1 FROM TABLE) AS A WHERE VALUE>270) AS A GROUP BY RN2-RN1 HAVING DATEDIFF(SS,MIN(TIME),MAX(TIME))>=4 ORDER BY MIN_TIME
hans_java 2017-03-16
  • 打赏
  • 举报
回复
坐等大神回复,顺便学习一下!
liuyinfeng 2017-03-16
  • 打赏
  • 举报
回复
大神,打错了,是四分
liuyinfeng 2017-03-16
  • 打赏
  • 举报
回复
就是每一行记录中时间的差啊
卖水果的net 2017-03-16
  • 打赏
  • 举报
回复
持续时间大于等于4秒的 4 秒,是怎么算的?

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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