查找数据中缺失的日期

问题描述：

我收到来自多个天线的每日txt文件数据。对于文件的命名约定是：查找数据中缺失的日期

独特的天线ID +年+月+日+随机3位数字

我解析的文件名和创建的表像这样的：

AntennaID fileyear filemonth fileday filenumber  filename 
0000   2016  09   22   459   000020160922459.txt 
0000   2016  09   21   981   000020160921981.txt 
0000   2016  09   20   762   000020160920762.txt 
0001   2016  09   22   635   000120160922635.txt 
. 
. 
. 
etc. (200k rows)

有时候，天线根本不会发送超过1个文件或没有文件。唯一的3位数filenumber区分文件，如果超过1发送，但我试图找到一个文件没有发送的日子。

我试了几次groupby语句来比较给定月份中的数据文件数量，并查看它是否与当月的日期相匹配 - 但问题是有时天线每次发送超过1个文件如果我们只是比较计数，那么可以人为地弥补“缺失”文件。

我正在寻找一种更强大的方法来查找丢失文件的日期或日期范围。我已经研究了分区和重叠函数，并感觉这里可能有潜力，但我不确定如何使用它们，因为我对SQL相当陌生。

我使用Microsoft SQL Server 2016

这是什么叫做“缺口另一个例子，岛屿“问题。你可以谷歌这个词来解决很多问题。 –

答

可以使用common table expression（或简称cte）创建的日期表。您可以从该表到你的天线数据，那么join能并查找返回null值日期：

declare @MinDate date = getdate()-50 
declare @MaxDate date = getdate() 

;with Dates as 
(
select @MinDate as DateValue 

union all 

select dateadd(d,1,DateValue) 
from Dates 
where DateValue < @MaxDate 
) 
select d.DateValue 
from Dates d 
    left join AntennaData a 
     on(d.DateValue = cast(cast(a.fileyear as nvarchar(4)) + cast(a.filemonth as nvarchar(4)) + cast(a.fileday as nvarchar(4)) as date)) 
option (maxrecursion 0)

我的投票把你的声望超过1000.你欠我一杯啤酒。 –

@DanBracuk诱惑你的历史并对9件事进行投票，让你达到15k ... – iamdave

答

您可以使用NOT EXISTS：

DECLARE @BeginDate DATE, @EndDate DATE; 
SET @BeginDate = '20160101'; 
SET @EndDate = '20160922'; 

WITH Dates AS 
(
    SELECT DATEADD(DAY,number,@BeginDate) [Date] 
    FROM master.dbo.spt_values 
    WHERE type = 'P' 
    AND DATEADD(DAY,number,@BeginDate) <= @EndDate 
) 
SELECT * 
FROM Dates A 
WHERE NOT EXISTS(SELECT 1 FROM dbo.Antenna 
       WHERE SUBSTRING([filename],5,8) = A.[Date]);

查找数据中缺失的日期

相关推荐