在SQL中插值的最佳方法 [英] Best way to interpolate values in SQL
本文介绍了在SQL中插值的最佳方法的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个表,其中包含特定日期的费率:
I have a table with rate at certain date :
Rates
Id | Date | Rate
----+---------------+-------
1 | 01/01/2011 | 4.5
2 | 01/04/2011 | 3.2
3 | 04/06/2011 | 2.4
4 | 30/06/2011 | 5
我想基于简单的线性插值来获得输出速率.
I want to get the output rate base on a simple linear interpolation.
所以,如果我输入17/06/2011:
So if I enter 17/06/2011:
Date Rate
---------- -----
01/01/2011 4.5
01/04/2011 3.2
04/06/2011 2.4
17/06/2011
30/06/2011 5.0
线性插值是(5 + 2,4) / 2 = 3,7
是否有一种方法可以执行简单的查询(SQL Server 2005),还是需要以编程方式(C#...)完成此类工作?
Is there a way to do a simple query (SQL Server 2005), or this kind of stuff need to be done in a programmatic way (C#...) ?
推荐答案
类似的东西(已更正):
Something like this (corrected):
SELECT CASE WHEN next.Date IS NULL THEN prev.Rate
WHEN prev.Date IS NULL THEN next.Rate
WHEN next.Date = prev.Date THEN prev.Rate
ELSE ( DATEDIFF(d, prev.Date, @InputDate) * next.Rate
+ DATEDIFF(d, @InputDate, next.Date) * prev.Rate
) / DATEDIFF(d, prev.Date, next.Date)
END AS interpolationRate
FROM
( SELECT TOP 1
Date, Rate
FROM Rates
WHERE Date <= @InputDate
ORDER BY Date DESC
) AS prev
CROSS JOIN
( SELECT TOP 1
Date, Rate
FROM Rates
WHERE Date >= @InputDate
ORDER BY Date ASC
) AS next
这篇关于在SQL中插值的最佳方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文