问题描述
我在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>
有另一种方法来做到这一点会更有效率吗?或者这是非常优化的?
我认为使用将是最有效的方式:
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
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;
这篇关于根据最近的先前日期,最有效的方式来匹配2个表之间的值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!