T-SQL在sql server中按日期範圍對時間間隔進行分組

原始表有列 Timestamp with Interval

間隔：按時間戳本身排序時，目前時間戳與上一個時間戳之間的分鐘差

Timestamp               Interval(InMinute)
2016-12-31 00:28:00     NULL
2016-12-31 00:29:00     1
2016-12-31 00:30:00     1
2016-12-31 00:45:00     15
2016-12-31 01:00:00     15
2016-12-31 01:15:00     15
2016-12-31 01:16:00     1
2016-12-31 01:17:00     1
2016-12-31 01:18:00     1
2016-12-31 01:19:00     1

我想使用 T-SQL 檢測時間間隔變化並產生輸出

StartDate                EndDate                Interval
2016-12-31 00:28:00      2016-12-31 00:30:00    1
2016-12-31 00:30:00      2016-12-31 01:15:00    15
2016-12-31 01:15:00      2016-12-31 01:19:00    1

我想知道間隔保持不變的時間。第二行表示從2016-12-31 00:30:00到2016-12-31 01:15:00，間隔保持不變，即 15。但之後又變回 12016-12-31 01:15:00

有一個例子，來自 Itzik Ben Gan（序列中的間隙和島嶼）這是基於這篇文章：間隙

DECLARE @vt_Source AS TABLE
( ts datetime NOT NULL PRIMARY KEY,
interval tinyint NULL
)

INSERT INTO @vt_Source(ts, interval)
VALUES('2016-12-31 00:28:00',     NULL)
,('2016-12-31 00:29:00'  ,   1)
,('2016-12-31 00:30:00'  ,   1)
,('2016-12-31 00:45:00'  ,   15)
,('2016-12-31 01:00:00'  ,   15)
,('2016-12-31 01:15:00'  ,   15)
,('2016-12-31 01:16:00'  ,   1)
,('2016-12-31 01:17:00'  ,   1)
,('2016-12-31 01:18:00'  ,   1)
,('2016-12-31 01:19:00'  ,   1)


SELECT
  min(ts_prev) AS startDate
 ,max(ts) AS endDate
 ,interval
FROM
  (SELECT
        ts
        ,interval
        ,ROW_NUMBER() OVER(ORDER BY ts ASC) AS  rn_all
        ,ROW_NUMBER() OVER(PARTITION BY interval ORDER BY ts ASC) AS rn_group
        ,LAG(ts,1,ts) OVER(ORDER BY ts ASC) AS ts_prev
   FROM
       @vt_Source
  )A
WHERE
  A.interval IS NOT NULL
GROUP BY
  rn_all - rn_group
  ,interval
ORDER BY 
  startDate ASC

為此輸出：

startDate           endDate          interval
31/12/2016 00:28:00 31/12/2016 00:30:00 1
31/12/2016 00:30:00 31/12/2016 01:15:00 15
31/12/2016 01:15:00 31/12/2016 01:19:00 1

我添加了一個WHERE子句來消除第一行，即NULL在間隔列上的那一行

http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=fe1888d0a934d73de0ed9887aaf4d482

引用自：https://dba.stackexchange.com/questions/171106