查找月份开始和结束的最近日期

Question

表包含特定参数的每日快照，但数据可能会在几天内丢失。任务是计算量每月，这个缘故，我们需要在每月的开始/结束的值，如果数据丢失，我们需要对最近的日期，即：

[Time]     Value 
2015-04-28 00:00:00.000 76127 
2015-05-03 00:00:00.000 76879 
2015-05-22 00:00:00.000 79314 
2015-06-07 00:00:00.000 81443 

目前我使用下面的代码：

select 
    * 
from(
    select 
    [Time], 
    Value, 
    ROW_NUMBER() over (partition by CASE WHEN [Time] < '2015-05-01' THEN 1 ELSE 0 END order by abs(DATEDIFF(DAY, '2015-05-01', [Time]))) as rn2, 
    ROW_NUMBER() over (partition by CASE WHEN [Time] > '2015-05-01' THEN 1 ELSE 0 END order by abs(DATEDIFF(DAY, [Time], '2015-05-01'))) as rn3, 
    ROW_NUMBER() over (partition by CASE WHEN [Time] < '2015-05-31' THEN 1 ELSE 0 END order by abs(DATEDIFF(DAY, '2015-05-31', [Time]))) as rn4, 
    ROW_NUMBER() over (partition by CASE WHEN [Time] > '2015-05-31' THEN 1 ELSE 0 END order by abs(DATEDIFF(DAY, [Time], '2015-05-31'))) as rn5, 
    DATEDIFF(DAY, '2015-05-01', [Time]) as doff, 
    DATEDIFF(DAY, '2015-05-31', [Time]) as doff2 
    from 
    ValueTable 
    where 
    [Time] between '2015-04-01' and '2015-06-30' 
) r 
where 
    doff = 0 or doff2 = 0 or (doff != 0 and rn2 = 1 and rn3 = 1) or (doff2 != 0 and rn4 = 1 and rn5 = 1) 

有没有更有效的方法来做到这一点？

Answer 1

下面的代码看起来更复杂，因为它更长。但是，它应该非常快，因为它可以很好地利用ValueTable([Time])上的索引。

这个想法是寻找完全匹配。如果没有完全匹配，则在日期前后查找第一个和最后一个记录。这需要union all六个子查询，但每个人都应该做一个索引的最佳使用：

with exact_first as (
     select t.* 
     from ValueTable t 
     where [Time] = '2015-05-01' 
    ), 
    exact_last as (
     select t.* 
     from ValueTable t 
     where [Time] = '2015-05-01' 
    ) 
(select ef.* 
from exact_first ef 
) union all 
(select top 1 t.* 
from ValueTable t 
where [Time] < '2015-05-01' and 
     not exists (select 1 from exact_first ef2) 
order by [Time] 
) union all 
(select top 1 t.* 
from ValueTable t 
where [Time] > '2015-05-01' and 
     not exists (select 1 from exact_first ef2) 
order by [Time] desc 
) union all 
(select el.* 
from exact_last el 
) union all 
(select top 1 t.* 
from ValueTable t 
where [Time] < '2015-05-31' and 
     not exists (select 1 from exact_last ef2) 
order by [Time] 
) union all 
(select top 1 t.* 
from ValueTable t 
where [Time] > '2015-05-31' and 
     not exists (select 1 from exact_last ef2) 
order by [Time] desc; 
)