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

小编典典

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

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

2020-05-17