在表中查找丢失的时间间隔(Find missing time intervals in a tabl

我有以下表格包含值从几个不同的设备读取每15分钟：

ID   DeviceID   Date                    Value
----------------------------------------------
1    3          24.08.2011 00:00:00     0.51
2    3          24.08.2011 00:15:00     2.9
3    3          24.08.2011 00:30:00     0
4    3          24.08.2011 00:45:00     7.1
5    3          24.08.2011 01:00:00     1.05
6    3          24.08.2011 03:15:00     3.8

我想找到的所有表格中的空白，为每一个设备，那里没有记录，对于一个给定的月份。 对于上面的表，结果应该是这样的：

DeviceID    StartDate               EndDate
-------------------------------------------------------
3           24.08.2011 01:00:00     24.08.2011 03:15:00

该表有大约35000设备和100万个条目。

这是我尝试过; 这是相当缓慢的，但返回我需要什么。 然而，除了其速度，还有另外一个问题：它只能找到丢失的时间间隔达到在给定月份的设备的最后一个条目; 之后事情会被忽略，因此有可能错过遗漏值的一个额外的时间间隔。

SELECT
    t2.Date AS StartDate
    , t1.Date AS EndDate
FROM
    TestTable t1
    INNER JOIN TestTable t2 ON t1.DeviceID = t2.DeviceID
WHERE
    (t2.Date = (SELECT MAX(Date) FROM TestTable t3 WHERE t3.Date < t1.Date AND t3.DeviceID = t1.DeviceID)
        AND DATEDIFF(MINUTE, t2.Date, t1.Date) > 15)
    AND t1.DeviceID = @id
    AND DATEPART(YEAR, t1.Date) = @year AND DATEPART(MONTH, t1.Date) = @month

Answer 1:

以下应该工作，并为设备ID不返回只是一个单一的记录。

这样做的主旨是

• 添加ROWNUMBER每个记录，通过有序的Date ，并重新启动每个DeviceID 。
• 自加入到创建由两个原始行的组合行的结果。 每个行的列之间的关系是ROWNUMBER（1）和所述DeviceID 。
• 只保留那些行，其中相关的Date超过15分钟。

SQL语句

;WITH t AS (
  SELECT  *, rn = ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY Date)
  FROM    TestTable
)  
SELECT  t1.DeviceID, t1.Date, t2.Date
FROM    t t1
        INNER JOIN t t2 ON t2.DeviceID = t1.DeviceID AND t2.rn = t1.rn + 1
WHERE   DATEDIFF(MINUTE, t1.Date, t2.Date) > 15        

测试脚本

;WITH TestTable (ID, DeviceID, Date, Value) AS (
  SELECT 1, 3, '2011-08-24 00:00:00', 0.51 UNION ALL
  SELECT 2, 3, '2011-08-24 00:15:00', 2.9 UNION ALL
  SELECT 3, 3, '2011-08-24 00:30:00', 0 UNION ALL
  SELECT 4, 3, '2011-08-24 00:45:00', 7.1 UNION ALL
  SELECT 5, 3, '2011-08-24 01:00:00', 1.05 UNION ALL
  SELECT 6, 3, '2011-08-24 03:15:00', 3.8 
)
, t AS (
  SELECT  *, rn = ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY Date)
  FROM    TestTable
)  
SELECT  t1.DeviceID, t1.Date, t2.Date
FROM    t t1
        INNER JOIN t t2 ON t2.DeviceID = t1.DeviceID AND t2.rn = t1.rn + 1
WHERE   DATEDIFF(MINUTE, t1.Date, t2.Date) > 15        

文章来源: Find missing time intervals in a table