代码之家 › 专栏 › 技术社区 › Dario

如何使用SQL查找缺少的数据行?

database mysql

Dario · 技术社区 · 16 年前

我得到了一个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

编辑 :

好的,那临时桌子呢?是否有一种优雅的方式自动填充时间范围?如果两个脚本试图同时运行,会不会导致表出现问题?

7 回复 | 直到 16 年前

Steve De Caux 16 年前

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之间有一个时间戳。

编辑-用于检查的thx,Craig

编辑2:

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

开始时间戳(我使用示例中的curdate();和
子句指定中的42个迭代

... 这意味着我们可以使用原始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天内的最大间隙

Sparky 16 年前

如果可以创建临时表,则可以使用联接解决此问题

CREATE TEMPORARY TABLE DateRange
(theDate DATE);

在表格中填入日期之间的所有10分钟间隔,然后使用以下命令

SELECT theDate
FROM DateRange dr
LEFT JOIN Meteorological mm on mm.date = dr.theDate
WHERE mm.date IS NULL

如果需要快速查找丢失数据的天数,可以使用

select Date(mm.Date),144-count(*) as TotMissing 
from Meteorological mm 
group by Date(mm.Date) 
having count(*) < 144

Mark Canlas 16 年前

对于联接,生成的所有日期的引用是基表,数据是联接表。找出连接数据不存在的对,并选择生成的日期。

Adriaan Stander 16 年前

Sql Server ,检查没有日期+间隔跟随者的日期。我认为MySql确实有一些dateadd函数,但是您可以尝试这样的功能。这将显示丢失数据的范围。

DECLARE @TABLE TABLE(
        DateValue DATETIME
)

INSERT INTO @TABLE SELECT '10 Feb 2009 10:00:00'
INSERT INTO @TABLE SELECT '10 Feb 2009 10:10:00'
INSERT INTO @TABLE SELECT '10 Feb 2009 10:40:00'
INSERT INTO @TABLE SELECT '10 Feb 2009 10:50:00'
INSERT INTO @TABLE SELECT '10 Feb 2009 11:00:00'

SELECT  *
FROM    @TABLE currentVal
WHERE   ((SELECT * FROM @TABLE nextVal WHERE DATEADD(mi,10,currentVal.DateValue) = nextVal.DateValue) IS NULL AND currentVal.DateValue != (SELECT MAX(DateValue) FROM @TABLE))
OR      ((SELECT * FROM @TABLE prevVal WHERE DATEADD(mi,-10,currentVal.DateValue) = prevVal.DateValue) IS NULL  AND currentVal.DateValue != (SELECT MIN(DateValue) FROM @TABLE))

Mark Byers 16 年前

SELECT T1.[date] AS DateFrom, MIN(T3.[date]) AS DateTo
    FROM [test].[dbo].[WeatherData] T1
    LEFT JOIN [test].[dbo].[WeatherData] T2 ON DATEADD(MINUTE, 10, T1.date) = T2.date
    LEFT JOIN [test].[dbo].[WeatherData] T3 ON T3.date > T1.Date
    WHERE T2.[value] IS NULL
    GROUP BY T1.[date]

如果您有大量数据,您可能希望尝试将范围限制为一次一个月,以避免服务器上的繁重负载,因为此操作可能非常密集。

结果如下:

DateFrom                    DateTo
2009-10-02 10:10:00.000 2009-10-02 10:40:00.000
2009-10-02 11:00:00.000 NULL

最后一行表示从最后一个时间戳到将来的所有数据。

然后,您可以使用另一个连接从另一个数据库获取在这些时间间隔之间具有时间戳的行。

Disillusioned 16 年前

我们的想法是首先生成一个“紧凑”的结果集,总结差距列表。即以下数据:

MeasureDate
2009-12-06 13:00:00
2009-12-06 13:10:00
--missing data
2009-12-06 13:30:00
--missing data
2009-12-06 14:10:00
2009-12-06 14:20:00
2009-12-06 14:30:00
--missing data
2009-12-06 15:00:00

如果实际间隙严格在端点之间(即不包括端点),则将转换为以下值:

GapStart            GapEnd
2009-12-06 13:10:00 2009-12-06 13:30:00
2009-12-06 13:30:00 2009-12-06 14:10:00
2009-12-06 14:30:00 2009-12-06 15:00:00
2009-12-06 15:00:00 NULL

10分钟后获取所有没有条目的测量数据,因为这将是间隙的开始。注:即使不是严格意义上的空白,也将包括最后一个条目;但这不会有任何负面影响。
通过在间隙开始后使用第一个测量点添加间隙末端来增加上述值。
支离破碎
使用带有不等式的内部联接来识别备份中可能存在的任何缺失数据。(运行测试和检查以验证备份数据的完整性。)
假设您的备份数据是可靠的,并且不会在测量中产生异常的、毫无根据的峰值,请将数据插入主数据库。

/* TiC Copyright
This query is writtend (sic) by me, and cannot be used without 
expressed (sic) written permission. (lol) */

/*Step 3*/
SELECT  gap.GapStart, gap.GapEnd,
        rem.MeasureDate, rem.Col1, ...
FROM    (
        /*Step 2*/
        SELECT  gs.GapStart, (
                SELECT  MIN(wd.MeasureDate)
                FROM    WeatherData wd
                WHERE   wd.MeasureDate > gs.GapStart
                ) AS GapEnd
        FROM    (
                /*Step 1*/
                SELECT  wd.MeasureDate AS GapStart
                FROM    WeatherData wd
                WHERE   NOT EXISTS (
                        SELECT  *
                        FROM    WeatherData nxt
                        WHERE   nxt.MeasureDate = DATEADD(mi, 10, wd.MeasureDate)
                        )
                ) gs
        ) gap
        INNER JOIN RemoteWeatherData rem ON
            rem.MeasureDate > gap.GapStart
        AND rem.MeasureDate < gap.GapEnd

插入。。。

INSERT INTO WeatherData (MeasureDate, Col1, ...)
SELECT  /*gap.GapStart, gap.GapEnd,*/
        rem.MeasureDate, rem.Col1, ...
...

-1

Thorsten 16 年前

进行自联接,然后计算较小且差值大于间隔的最大值。

Select t1.ts, max(t2.ts)
FROM atable t1 join atable t2 on t1.ts > t2.ts
GROUP BY t1.ts
HAVING (t1.ts - max(t2.ts))*24*60 > 10

此查询将直接在停机之后和之前为您提供时间戳,您可以从中构建。