查找最近的开始日期和月末

Table contains daily snapshots of specific parameter, but data can be missing for some days. Task is to calculate amount per month, for this sake we need values on start/end of the month, if data is missing, we need pairs of nearest dates i.e:

表包含特定参数的每日快照,但某些天可能会丢失数据。任务是计算每月的金额,为此我们需要在月末开始/结束时的值,如果数据缺失,我们需要成对的最近日期,即:

[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

Currently i use following code:

目前我使用以下代码:

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)

Is there any more efficient way to do it?

有没有更有效的方法呢?

1 个解决方案

#1

The following code is going to look more complicated because it is longer. However, it should be very fast, because it can make very good use of an index on ValueTable([Time]).

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

The idea is to look for exact matches. If there are no exact matches, then find the first and last records before and after the dates. This requires union all on six subqueries, but each should make optimal use of an index:

我们的想法是寻找完全匹配。如果没有完全匹配,则查找日期之前和之后的第一个和最后一个记录。这需要在六个子查询上进行联合,但每个子查询都应该最佳地使用索引:

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