La mejor manera de interpolar valores en SQL

Ya no necesitas indagar más por internet ya que estás al sitio perfecto, poseemos la respuesta que deseas sin complicaciones.

Solución:

Algo así (corregido):

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

Como @Mark ya señaló, el CROSS JOIN tiene sus limitaciones. Tan pronto como el valor objetivo quede fuera del rango de valores definidos, no se devolverán registros.

Además, la solución anterior se limita a un solo resultado. Para mi proyecto, necesitaba una interpolación para una lista completa de valores x y se me ocurrió la siguiente solución. ¿Tal vez sea de interés para otros lectores también?

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

Al ejecutar el script anterior, obtendrá los siguientes puntos de cuadrícula de datos en la tabla #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 

[[ The table contains grid points for two identities (id=1 and id=2). In my example I referenced only the 1-group by using where id=1 in the valstyp CTE. This can be changed to suit your requirements. ]]

y la tabla de resultados #vals con los datos interpolados en la columna yy:

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

la ultima columna itype indica el tipo de interpolación/extrapolación que se utilizó para calcular el valor:

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

Este ejemplo de trabajo se puede encontrar aquí.

El truco con CROSS JOIN aquí es que no devolverá ningún registro si alguna de las tablas no tiene filas (1 * 0 = 0) y la consulta puede fallar. La mejor manera de hacerlo es usar FULL OUTER JOIN con condición de desigualdad (para evitar obtener más de una fila)

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

---

---

Utiliza Nuestro Buscador