查找最接近月初和月底的日期

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;
)