sql - 查找数据中缺失的日期

Question

我每天从多个天线接收数据的 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 个以上的文件或根本不发送文件。如果发送了 1 个以上的文件，则唯一的 3 位文件编号会区分文件，但是我正在尝试查找未发送文件的日期。

我已经尝试了几个 groupby 语句来比较给定月份中的数据文件数量，并查看它是否与该月的天数匹配 - 但问题是有时天线每天发送超过 1 个文件，这可能是人为的如果我们只是比较计数，则弥补“丢失”的文件。

我正在寻找一种更强大的方法来查找丢失文件的日期或日期范围。我研究了 Partition 和 Over 函数，觉得那里可能有潜力，但我不确定如何使用它们，因为我对 SQL 还很陌生。

我正在使用 Microsoft SQL Server 2016

Answer 1

您可以使用 common table expression (或简称 cte)来创建一个日期表。然后您可以 join从此表到您的天线数据并查找返回 null 的日期值(value):

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)

为使用此答案的任何人编辑:
虽然递归 CTE 会生成日期列表，但这并不是最有效的方法。如果速度对您很重要，请改用基于集合的计数表:

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

              -- Generate table with 10 rows
with t(t) as (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1)
              -- Add row numbers (-1 to start at adding 0 to retain @MinDate value) based on tally table to @MinDate for the number of days +1 (to ensure Min and Max date are included) between the two dates
    ,d(d) as (select top(datediff(day, @MinDate, @MaxDate)+1) dateadd(day,row_number() over (order by (select null))-1,@MinDate)
              from t t1,t t2,t t3,t t4,t t5,t t6    -- Cross join creates 10^6 or 10*10*10*10*10*10 = 1,000,000 row table
              )
select *
from d;