問題描述

我已經閱讀并嘗試使用標準的間隙和孤島檢測方法，但沒有成功，因為我需要能夠忽略任何少于 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 分鐘的間隙的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！