在SQL中插值的最佳方法

Question

我有一个特定日期的汇率表：

          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：

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＃…）完成这种工作？

Answer 1

像这样的东西（已更正）：

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