在MySQL中找到时间序列数据中的缺口的方法？

Question

假设我们有一个包含两列的数据库表，entry_time和value。entry_time是时间戳记，而value可以是任何其他数据类型。记录是相对一致的，大约每隔x分钟输入一次。但是，对于许多x的时间来说，可能无法进行输入，从而在数据中产生“间隙”。

在效率方面，如何通过查询找到至少Y时间（新旧）之间的差距的最佳方法是什么？

Answer 1

首先，让我们按小时汇总表中的条目数。

SELECT CAST(DATE_FORMAT(entry_time,'%Y-%m-%d %k:00:00') AS DATETIME) hour,
       COUNT(*) samplecount
  FROM table
 GROUP BY CAST(DATE_FORMAT(entry_time,'%Y-%m-%d %k:00:00') AS DATETIME)

现在，如果您每六分钟（每小时十次）记录一次，则所有samplecount值应为十。此表达式：CAST(DATE_FORMAT(entry_time,'%Y-%m-%d %k:00:00') AS DATETIME)看起来很毛茸茸，但只需将分钟和秒清零，就可以将时间戳截短到出现的小时。

这是相当有效的，并且可以帮助您入门。如果您可以在entry_time列上放置索引并将查询限制为昨天的示例（如此处所示），那将非常有效。

SELECT CAST(DATE_FORMAT(entry_time,'%Y-%m-%d %k:00:00') AS DATETIME) hour,
       COUNT(*) samplecount
  FROM table
 WHERE entry_time >= CURRENT_DATE - INTERVAL 1 DAY
   AND entry_time < CURRENT_DATE
 GROUP BY CAST(DATE_FORMAT(entry_time,'%Y-%m-%d %k:00:00') AS DATETIME)

但是，在检测缺少样本的整个小时数方面并不是很好。它还对采样中的抖动有些敏感。也就是说，如果您的时间最多的样本有时是早半秒（10:59:30），有时又是半秒延迟（11:00:30），则您的每小时摘要计数将关闭。因此，该小时摘要（或日期摘要或分钟摘要等）不是安全的。

您需要一个自联接查询才能完全正确地进行操作。它有点像毛毛球，效率不高。

让我们首先创建一个带有编号样本的虚拟表（子查询）。（这在MySQL中是很痛苦的；其他一些昂贵的DBMS使其变得更容易。无论如何。）

  SELECT @sample:=@sample+1 AS entry_num, c.entry_time, c.value
    FROM (
        SELECT entry_time, value
      FROM table
         ORDER BY entry_time
    ) C,
    (SELECT @sample:=0) s

这个小的虚拟表提供了entry_num，entry_time，值。

下一步，我们将其加入自身。

SELECT one.entry_num, one.entry_time, one.value, 
       TIMEDIFF(two.value, one.value) interval
  FROM (
     /* virtual table */
  ) ONE
  JOIN (
     /* same virtual table */
  ) TWO ON (TWO.entry_num - 1 = ONE.entry_num)

这将表排成一行，每两个表之间偏移一个偏移量，该偏移量由JOIN的ON子句控制。

最后，我们从该表中选择一个interval大于阈值的值，并且在丢失样本之前就有样本的时间。

整体自连接查询是这个。我告诉过你那是一个毛线球。

SELECT one.entry_num, one.entry_time, one.value, 
       TIMEDIFF(two.value, one.value) interval
  FROM (
    SELECT @sample:=@sample+1 AS entry_num, c.entry_time, c.value
      FROM (
          SELECT entry_time, value
            FROM table
           ORDER BY entry_time
      ) C,
      (SELECT @sample:=0) s
  ) ONE
  JOIN (
    SELECT @sample2:=@sample2+1 AS entry_num, c.entry_time, c.value
      FROM (
          SELECT entry_time, value
            FROM table
           ORDER BY entry_time
      ) C,
      (SELECT @sample2:=0) s
  ) TWO ON (TWO.entry_num - 1 = ONE.entry_num)

如果必须在大型表上的生产中执行此操作，则可能需要对数据的子集执行此操作。例如，您可以每天为前两天的样本进行处理。这样会非常有效，并且还可以确保您不会在午夜忽略任何丢失的样本。为此，您的小行编号虚拟表将如下所示。

  SELECT @sample:=@sample+1 AS entry_num, c.entry_time, c.value
    FROM (
        SELECT entry_time, value
      FROM table
         ORDER BY entry_time
         WHERE entry_time >= CURRENT_DATE - INTERVAL 2 DAY
           AND entry_time < CURRENT_DATE /*yesterday but not today*/
    ) C,
    (SELECT @sample:=0) s