根据最近的先前日期，最有效的方式来匹配2个表之间的值？ [英] What's the most efficient way to match values between 2 tables based on most recent prior date?

问题描述

我在MS SQL Server中有两个表：

dailyt - 其中包含每日数据： / p>

  date val 
 --------------------- 
 2014-05-22 10 
 2014-05-21 9.5 
 2014-05-20 9 
 2014-05-19 8 
 2014-05-18 7.5 
等... 
  

和 periodt - 其中包含不规则时段的数据：

  date val 
 -------- ------------- 
 2014-05-21 2 
 2014-05-18 1 
  

给定 dailyt 中的一行，我想通过在 periodt中添加相应的值来调整其值之前或等于 dailyt 行的最近日期。因此，输出将如下所示：

addt

  date val 
 --------------------- 
 2014-05-22 12< - 从2014-05-21添加2 
 2014-05-21 11.5<  - 从2014-05-21添加2 
 2014-05-20 10<  - 从2014-05年起添加1  - 18 
 2014-05-19 9<  - 从2014-05-18添加1 
 2014-05-18 8.5<  - 从2014-05-18添加1 
  

我知道这样做的一个方法是加入 dailyt 和 periodt 表 periodt.date< = dailyt.date ，然后强加一个 ROW_NUMBER（） （PARTITION BY dailyt.date ORDER BY periodt.date DESC）条件，然后在行号上具有 WHERE 条件= 1。 / p>

有另一种方法来做到这一点会更有效率吗？或者这是非常优化的？

解决方案

我认为使用 APPLY 将是最有效的方式：

  SELECT d.Val，
 p.Val，
 NewVal = d.Val + ISNULL（p.Val，0）
 FROM Dailyt AS d 
 OUTER APPLY 
（SELECT TOP 1 Val 
 FROM Periodt p 
 WHERE p.Date< = d.Date 
 ORDER BY p.Date DESC 
） AS p 
  

SQL小提琴的示例

I've got two tables in MS SQL Server:

dailyt - which contains daily data:

date             val
---------------------
2014-05-22       10
2014-05-21       9.5
2014-05-20       9
2014-05-19       8
2014-05-18       7.5
etc...

And periodt - which contains data coming in at irregular periods:

date             val
---------------------
2014-05-21       2
2014-05-18       1

Given a row in dailyt, I want to adjust its value by adding the corresponding value in periodt with the closest date prior or equal to the date of the dailyt row. So, the output would look like:

addt

date             val
---------------------
2014-05-22       12      <- add 2 from 2014-05-21
2014-05-21       11.5    <- add 2 from 2014-05-21
2014-05-20       10      <- add 1 from 2014-05-18
2014-05-19       9       <- add 1 from 2014-05-18
2014-05-18       8.5     <- add 1 from 2014-05-18

I know that one way to do this is to join the dailyt and periodt tables on periodt.date <= dailyt.date and then imposing a ROW_NUMBER() (PARTITION BY dailyt.date ORDER BY periodt.date DESC) condition, and then having a WHERE condition on the row number to = 1.

Is there another way to do this that would be more efficient? Or is this pretty much optimal?

解决方案

I think using APPLY would be the most efficient way:

SELECT  d.Val,
        p.Val,
        NewVal = d.Val + ISNULL(p.Val, 0)
FROM    Dailyt AS d
        OUTER APPLY
        (   SELECT  TOP 1 Val
            FROM    Periodt p
            WHERE   p.Date <= d.Date
            ORDER BY p.Date DESC
        ) AS p;

Example on SQL Fiddle

这篇关于根据最近的先前日期，最有效的方式来匹配2个表之间的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！