Finden Sie fehlende Zeitintervalle in einer Tabelle

Question

Ich habe folgende Tabelle, die Werte enthält, die alle 15 Minuten von verschiedenen Geräten ausgelesen werden:

ID   DeviceID   Datum                    Wert
----------------------------------------------
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

Ich würde gerne alle Lücken in der Tabelle für jedes Gerät finden, in denen keine Einträge vorhanden sind, für einen bestimmten Monat. Für die obige Tabelle sollte das Ergebnis so aussehen:

DeviceID    StartDatum               EndDatum
-------------------------------------------------------
3           24.08.2011 01:00:00     24.08.2011 03:15:00

Die Tabelle hat ungefähr 35000 Geräte und 100 Millionen Einträge.

Das habe ich versucht; es ist ziemlich langsam, liefert aber was ich brauche. Allerdings gibt es neben seiner Geschwindigkeit ein weiteres Problem: Es findet nur fehlende Intervalle bis zum letzten Eintrag für ein Gerät im angegebenen Monat; alles danach wird ignoriert, sodass möglicherweise ein zusätzliches Intervall fehlender Werte übersehen wird.

SELECT
    t2.Date AS StartDatum
    , t1.Date AS EndDatum
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

Das Folgende sollte funktionieren und nicht nur einen einzelnen Datensatz für eine Geräte-ID zurückgeben.

Das Wesentliche dabei ist:

• Fügen Sie jedem Datensatz eine Reihennummer hinzu, sortiert nach Datum und Neustart für jede Geräte-ID.
• Verknüpfen Sie sich selbst, um ein Ergebnis mit Zeilen zu erstellen, die aus der Kombination von zwei Originalzeilen bestehen. Die Beziehung zwischen den Spalten jeder Zeile ist die Reihennummer (+1) und die Geräte-ID.
• Behalten Sie nur die Zeilen bei, bei denen das zugehörige Datum mehr als 15 Minuten auseinander liegt.

SQL-Anweisung

;MIT t ALS (
  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        

Testskript

;MIT TestTable (ID, DeviceID, Date, Wert) ALS (
  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