在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#...）进行处理？

- Cyril Gandon

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

- ypercubeᵀᴹ

---

这当然是他想要的。我只是实现了一个糟糕的特殊情况。顺便说一下，你在最后一个 DATEDIFF 前面有一个多余的括号，你的前两个 CASE WHEN 应该返回 Rate 而不是 Date。无论如何加1。 - Mikael Eriksson

Answer 2

正如@Mark已经指出的，CROSS JOIN有其局限性。一旦目标值超出定义值的范围，就不会返回任何记录。

此外，上述解决方案仅限于一个结果。对于我的项目，我需要为整个x值列表进行插值，并想出了以下解决方案。也许其他读者也会感兴趣？

-- generate some grid data values in table #ddd:
CREATE TABLE #ddd (id int,x float,y float, PRIMARY KEY(id,x));
INSERT INTO  #ddd VALUES (1,3,4),(1,4,5),(1,6,3),(1,10,2),
                         (2,1,4),(2,5,6),(2,6,5),(2,8,2);
SELECT * FROM #ddd;                         

-- target x-values in table #vals (results are to go into column yy):
CREATE TABLE #vals (xx float PRIMARY KEY,yy float null, itype int);
INSERT INTO  #vals (xx) VALUES (1),(3),(4.3),(9),(12);

-- do the actual interpolation
WITH valstyp AS (
  SELECT id ii,xx,
         CASE WHEN min(x)<xx THEN CASE WHEN max(x)>xx THEN 1 ELSE 2 END ELSE 0 END flag,
         min(x) xmi,max(x) xma 
  FROM #vals INNER JOIN #ddd ON id=1 GROUP BY xx,id
), ipol AS (
  SELECT v.*,(b.x-xx)/(b.x-a.x) f,a.y ya,b.y yb 
  FROM valstyp v 
  INNER JOIN #ddd a ON a.id=ii AND a.x=(SELECT max(x) FROM #ddd WHERE id=ii 
             AND (flag=0 AND x=xmi OR flag=1 AND x<xx OR flag=2 AND x<xma))
  INNER JOIN #ddd b ON b.id=ii AND b.x=(SELECT min(x) FROM #ddd WHERE id=ii 
             AND (flag=0 AND x>xmi OR flag=1 AND x>xx OR flag=2 AND x=xma))
)
UPDATE v SET yy=ROUND(f*ya+(1-f)*yb,8),itype=flag FROM #vals v INNER JOIN ipol i ON i.xx=v.xx;

-- list the interpolated results table:
SELECT * FROM #vals

运行上述脚本后，您将在表格#ddd中获得以下数据网格点。

id x  y 
-- -- - 
1  3  4 
1  4  5 
1  6  3 
1  10 2 
2  1  4 
2  5  6 
2  6  5 
2  8  2 

[[该表包含两个身份的网格点（id=1和id=2）。在我的例子中，我只引用了1-组，通过在valstyp CTE中使用where id=1。这可以根据您的要求进行更改。 ]]

以及插值数据在列yy中的结果表#vals:

xx  yy   itype 
--- ---- ----- 
1   2    0     
3   4    0     
4.3 4.7  1     
9   2.25 1     
12  1.5  2     

最后一列itype表示用于计算值的插值/外推类型：

0:  extrapolation to lower end
1:  interpolation within given data range
2:  extrapolation to higher end

这个工作示例可以在这里找到。

- Carsten Massmann

---

感谢这位专注的用户在五年后回答了我的问题，我已经点赞了 :) - Cyril Gandon

Answer 3

使用CROSS JOIN的技巧在于，如果两个表中任意一个表没有行(1 * 0 = 0)，则不会返回任何记录，这可能会导致查询出错。更好的方法是使用带有不等式条件的FULL OUTER JOIN(避免获取多行结果)。

( SELECT TOP 1 
        Date, Rate 
    FROM Rates
    WHERE Date <= @InputDate
    ORDER BY Date DESC
  ) AS prev
  FULL OUTER JOIN
  ( SELECT TOP 1 
        Date, Rate 
    FROM Rates
    WHERE Date >= @InputDate
    ORDER BY Date ASC
  ) AS next
ON (prev.Date <> next.Date) [or Rate depending on what is unique]

- Parvez