在SQL中插值的最佳方式

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

有没有办法做一个简单的查询(SQL Server 2005),或者这种东西需要以编程方式完成(C#…)？

解决方法

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!