27,579
社区成员
发帖
与我相关
我的任务
分享
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
*/
--测试数据
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
--测试数据
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;