在SQL中插值的最佳方法 [英] Best way to interpolate values in 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屋！