检测时间戳列中超过 30 分钟的间隙 [英] Detect gaps over 30 min in timestamp column

问题描述

我已经阅读并尝试使用标准的间隙和孤岛检测方法，但没有成功，因为我需要能够忽略任何少于 30 分钟的间隙.由于性能问题，我无法使用游标.

I have read up on and attempted using the standard method of gaps and island detection in a series with no success because I need to be able to ignore any gaps less than 30 minutes. I can not use a cursor due to performance issues.

每次有至少 30 分钟的间隔时，我都需要一个新的行以开始和结束.如果没有至少 30 的间隔，则结果将是具有时间戳的最小值和最大值的一行.如果有 1 个至少 30 的间隙，则将有 2 行 - 从系列的开始到间隙以及从间隙到结尾.如果有更多的间隙，我们会为间隙之间的每个间隔获取行，等等.

Everytime there is a gap of at least 30 min, I need a new row with the start and end. If there are no gaps of at least 30, result would be one row with the min and max of the timestamps. If there is 1 gap of at least 30, there would be 2 rows - from the start of the series to the gap and from the gap to the end. If there are more gaps, we get rows for each interval between the gaps, etc.

输入:

timestamp 

2015-07-15 15:01:21 
2015-07-15 15:17:44 
2015-07-15 15:17:53 
2015-07-15 15:18:34 
2015-07-15 15:21:41 
2015-07-15 15:58:12 
2015-07-15 15:59:12 
2015-07-15 16:05:12
2015-07-15 17:02:12

所需的输出:

from | to

2015-07-15 15:01:21 | 2015-07-15 15:21:41 
2015-07-15 15:58:12 | 2015-07-15 16:05:12
2015-07-15 17:02:12 | 2015-07-15 17:02:12

推荐答案

使用公用表表达式的简单解决方案.如果您至少有 1000 行，则与游标性能进行比较.

Easy solution using common table expression. Compare with cursor performance if you have at least 1000 rows.

create table #tmp (Dt datetime)

insert into #tmp values 
('2015-07-15 15:01:21'),
('2015-07-15 15:17:44'), 
('2015-07-15 15:17:53'), 
('2015-07-15 15:18:34'), 
('2015-07-15 15:21:41'), 
('2015-07-15 15:58:12'), 
('2015-07-15 15:59:12'), 
('2015-07-15 16:05:12'),
('2015-07-15 17:02:12')

;with tbl as (
select dt, row_number() over(order by dt) rn
from #tmp
)
select t1.dt [from],t2.dt [to], datediff(minute,t1.dt,t2.dt) gap
from tbl t1 
inner join tbl t2 on t1.rn+1 = t2.rn
where datediff(minute,t1.dt,t2.dt) >30

这篇关于检测时间戳列中超过 30 分钟的间隙的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！