我有一个在一定日期的价格表:
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
我想通过简单的线性插值来获得输出速率.
所以如果我进入17/06/2011,线性插值是3,7 =(5 2,4)/ 2
解决方法
这样的东西(更正):
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,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