MySQL日期时间-差距识别

Question

我正在设计一个MySQL数据库，该数据库将包含从远程源收集的逐分钟数据。

我计划让数据表包含一个日期时间字段，因此每行可能有3个月的数据，代表每分钟的值。

如何轻松识别数据中的差距？我应该使用日期时间吗？

我将使用PHP5应用程序本身。

谢谢，Jim

Answer 1

我会给每一行一个唯一的顺序引用（1,2,3…），然后在表上进行自联接

...from mytable t1 join mytable t2 where (t1.ref+1)=t2.ref

根据您存储日期的方式，您可以减去它们（以某种方式），并确定是否存在差距。将整个内容封装在一个查询中，该查询提取有间隙的行。

您可能需要考虑将日期存储为特定开始日期后的秒数。

Answer 2

识别差距是一个有趣的问题。最佳方法将取决于差距的大小，但这里有另一种解决方法，如果差距与你的记录数量相比相当大，这种方法可能会更好。

在查询中使用MySQL聚合函数来计算一组bucket的记录数。桶的大小需要与你感兴趣的间隙相似。假设你对大约一天左右的间隙感兴趣，我会做这样的事情：

SELECT TO_DAYS(my_timestamp), COUNT(*)
FROM my_table
GROUP BY TO_DAYS(my_timestamp)

这将返回天数和时间戳计数之间的关联。我会用Perl或Java（甚至R，见下文）这样的语言来处理数据。

我使用的技术是测试观察到的频率（计数）和预期频率之间的差异，即记录的总数除以天的范围。预计每天的频率如下：

SELECT (SELECT COUNT(*) FROM my_table) / 
        ((SELECT TO_DAYS(MAX(my_timestamp)) FROM my_table) - 
         (SELECT TO_DAYS(MIN(my_timestamp)) FROM my_table) + 1)

现在，对于每个bucket（请记住，在第一个结果中，完全缺失的天数将不会返回，而不会以零计数返回——您需要将它们视为零，您可以使用统计检验卡方检验来估计这种可能性（有关详细信息，请参阅：http://en.wikipedia.org/wiki/Pearson%27s_chi-square_test）。计算基本上是（预期-观察）^2/预期）。这是对偏差可能性的估计。

如果您需要计算出哪些bucket在样本中较低，请在此计算值上设置一个合理的阈值，并查找该值超过阈值的bucket。设计一个合适的值可能需要一些实验，但这是确定差距的一种合理方法。