在MySQL的日期时间序列中查找缺失项。

Question

我有一个名为myTable的表，其中包含一个timestamp列the_dates（这是一个unique索引），并且每个值应该相隔五分钟。

例如：

the_dates
2014-03-13 17:30:00
2014-03-13 17:35:00
2014-03-13 17:40:00
2014-03-13 17:45:00

有没有一种方式可以使用mysql检测“缺失”的行？例如，如果下一个值是：

2014-03-13 17:55:00

那么myTable将会丢失：

2014-03-13 17:50:00

我在考虑使用UNIX_TIMESTAMP并检测大于300秒的间隔，也许使用某种形式的INNER JOIN将表格连接到自身，但我太菜了，无法独立完成。

有什么好的想法吗？

- themerlinproject

Answer 1

你可以尝试这样做。每个返回的记录应该是超过5分钟的时间范围。对于你的例子，它应该返回2014-03-13 17:45:00 , 2014-03-13 17:55:00。

SELECT t1.the_dates AS `from`, t2.the_dates AS `to`
FROM mytable AS t1
JOIN mytable AS t2
  ON t1.the_dates < t2.the_dates
LEFT JOIN mytable AS t3
  ON t3.the_dates > t1.the_dates 
   AND t3.the_dates < t2.the_dates
WHERE t3.the_dates IS NULL
  AND t2.the_dates > DATE_ADD(t1.the_dates, INTERVAL 5 MINUTE)
ORDER BY t1.the_dates;

- AgRizzo

---

谢谢，我会试着操作一下并回报结果。不管怎样，我可能最终需要将其拉入一些服务器端脚本来填补空缺。 - themerlinproject

所以，这个方法是可行的。但是它非常耗费资源。在一个650行的表格中需要21秒的时间。我现在改用1天的间隔。有没有什么方法可以让它更快一些？ - Patrick Bassut

@PatrickBassut - 日期字段上是否有索引？这个解决方案相对通用。你能否个性化实现？例如，左连接的目的是确保T2记录是“下一个”记录。你能否更有效地连接到后续行？ - AgRizzo

Answer 2

回到同一个问题上，我在一个大型数据集上测试了提出的解决方案，发现太费时间了。 我尝试了另一种方法，即在下一个可用时间将请求嵌入到同一张表格中：

SELECT 
    t.id,
    t.`timestamp`,
    (
        SELECT UNIX_TIMESTAMP(t1.`timestamp`)-UNIX_TIMESTAMP(t.`timestamp`) 
        FROM mytable AS t1 
        WHERE t1.timestamp>t.timestamp 
        ORDER BY t1.timestamp ASC LIMIT 1
    ) AS difftime
FROM 
    `mytable ` AS t 
    HAVING difftime > 300
    ORDER BY t.`timestamp` ASC

在一个大约有120万条记录的表格上，针对一组特定的3500个项目进行过滤，查询耗时3.5秒。

- Yannick Bétemps