mysql - 如何使用 SQL 查找丢失的数据行？

Question

我的问题:

我有一个 MySQL 数据库，它按时间顺序存储了大量的气象数据(每 10 分钟插入一次新数据)。不幸的是，有几次停电，因此缺少某些行。我最近设法从气象站获得了某些备份文件，现在我想用它们来填补缺失的数据。

DB ist 结构如下(示例):

date*            the data    
2/10/2009 10:00  ...
2/10/2009 10:10  ...
( Missing data!)
2/10/2009 10:40  ...
2/10/2009 10:50  ...
2/10/2009 11:00  ...
...

*=datatime-类型，主键

我的想法:

由于备份和数据库位于不同的计算机上并且流量很慢，我想创建一个 MySQL 查询，运行时将返回指定范围内所有缺失日期的列表的时间。然后我可以从备份中提取这些日期并将它们插入到数据库中。

问题:

如何编写这样的查询？我没有创建任何辅助表的权限。是否可以在指定的时间间隔内制定一个包含所有必需日期的“虚拟表”，然后在 JOIN 中使用它？还是有完全不同的命题来解决我的问题？

编辑:是的，时间戳始终如上所示(始终为 10 分钟)，只是缺少一些。

好的，临时表呢？有没有一种优雅的方法可以自动用时间范围填充它们？如果两个脚本尝试同时运行，这会导致表出现问题吗？

Answer 1

select t1.ts as hival, t2.ts as loval
from metdata t1, metdata t2
where t2.ts = (select max(ts) from metdata t3
where t3.ts < t1.ts)
and not timediff(t1.ts, t2.ts) = '00:10:00'

此查询将返回可用于选择缺失数据的对联。对于查询返回的每个对联，丢失的数据将在 hival 和 loval 之间有一个时间戳。

编辑 - 感谢检查，Craig

编辑2:

获取丢失的时间戳 - 这个 SQL 变得有点难以阅读，所以我会把它分解一下。首先，我们需要一种方法来以 10 分钟为间隔计算给定低值和高值之间的一系列时间戳值。无法创建表时执行此操作的一种方法是基于以下 sql，它创建从 0 到 9 的所有数字作为结果集。

select d1.* from 
(select 1 as digit
union select 2 
union select 3 
union select 4 
union select 5 
union select 6 
union select 7 
union select 8 
union select 9 
union select 0 
) as d1

...现在通过将该表与其自身的副本组合几次意味着我们可以动态生成指定长度的列表

select curdate() + 
INTERVAL  (d1.digit * 100 + d2.digit * 10 + d3.digit) * 10 MINUTE 
as date 
from (select 1 as digit
union select 2 
union select 3 
union select 4 
union select 5 
union select 6 
union select 7 
union select 8 
union select 9 
union select 0 
) as d1
join
(select 1 as digit
union select 2 
union select 3 
union select 4 
union select 5 
union select 6 
union select 7 
union select 8 
union select 9 
union select 0 
) as d2
join
(select 1 as digit
union select 2 
union select 3 
union select 4 
union select 5 
union select 6 
union select 7 
union select 8 
union select 9 
union select 0 
) as d3
where (d1.digit * 100 + d2.digit * 10 + d3.digit) between 1 and 42
order by 1

...现在这段sql已经接近我们需要的了。它有 2 个输入变量:

1. 一个起始时间戳(我用示例中的 curdate())；和一个
2. 迭代次数 - 哪里 子句指定 42 次迭代 示例，3 x 数字表的最大值为 1000 个间隔

... 这意味着我们可以使用原始 sql 来驱动上面的示例，为每个 hival lowval 对生成一系列时间戳。耐心等待，这个 sql 现在有点长......

select daterange.loval + INTERVAL  (d1.digit * 100 + d2.digit * 10 + d3.digit) * 10 MINUTE as date 
from 
(select t1.ts as hival, t2.ts as loval
from metdata t1, metdata t2
where t2.ts = (select max(ts) from metdata t3
where t3.ts < t1.ts)
and not timediff(t1.ts, t2.ts) = '00:10:00'
) as daterange
join
(select 1 as digit
union select 2 
union select 3 
union select 4 
union select 5 
union select 6 
union select 7 
union select 8 
union select 9 
union select 0 
) as d1
join
(select 1 as digit
union select 2 
union select 3 
union select 4 
union select 5 
union select 6 
union select 7 
union select 8 
union select 9 
union select 0 
) as d2
join
(select 1 as digit
union select 2 
union select 3 
union select 4 
union select 5 
union select 6 
union select 7 
union select 8 
union select 9 
union select 0 
) as d3
where (d1.digit * 100 + d2.digit * 10 + d3.digit) between 1 and
 round((time_to_sec(timediff(hival, loval))-600) /600)
order by 1

...现在有一些史诗般的 sql
注意:使用数字表 3 次给出的最大差距将超过 6 天