Лучший способ интерполяции значений в SQL

Ответ 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

Ответ 2

Трюк с 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]

Ответ 3

Как уже указывал @Mark, CROSS JOIN имеет свои ограничения. Как только целевое значение выходит за пределы заданных значений, никакие записи не возвращаются.

Также указанное решение ограничивается одним результатом. Для моего проекта мне понадобилась интерполяция для целого списка значений х и вышло следующее решение. Может быть, это интересно и другим читателям?

-- 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

При запуске выше script вы получите следующие точки сетки данных в таблице #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 -группу, используя where id=1 в CTE valstyp. Это можно изменить в соответствии с вашими требованиями. ]]

и таблицу результатов #vals с интерполированными данными в столбце yy:

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

Последний столбец itype указывает тип интерполяции /extapolation, который использовался для вычисления значения:

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

Этот рабочий пример можно найти здесь.