SQL function table interpolation

Question

I have an SQL table of (x,y) values.

x     y
0.0   0.0
0.1   0.4
0.5   1.0
5.0   2.0
6.0   4.0
8.0   4.0
10.0  5.0

The x column is indexed. I am using sqlite.

My ultimate goal is to get a y(x) for any x value. I will use linear interpolation using table values. Similar as shown in the plot below.

Is there a way to perform the linear interpolation directly using a select query? Otherwise getting the interval values where the x belongs, would be enough.

Is there a query that will give me the last smaller and the first bigger pair of a given x, so that I can compute the interpolated y(x) value?

For example if x=2.0 to get:

0.5   1.0
5.0   2.0

In case x is out of the table to get the two first/last values to perform an extrapolation.

For example if x=20.0 to get:

8.0   4.0
10.0  5.0

Answer 1

It would be hard to do this in plain SQLLite, without analytical functions. In more complex SQL engines, You could use LEG and LEAD analytical functions for obtaining set of pairs You want easily enough. In SQLLite though, I would create two cursors, like those:

Cursor C1:

SELECT 
    x,y
FROM
    table
WHERE
    x>=2
ORDER BY
    x asc
;

Cursor C2:

SELECT 
    x,y
FROM
    table
WHERE
    x<=2
ORDER BY
    x desc
;

And perform rest of operations in other language - fetching once from both, or if one cursor do not return value, twice from the other. Also, some additional exceptions need to be handled - what if Your set have less than two values. Or if You have given X in Your set - You do not need interpolata at all... And so on.

Answer 2

I would go with a simple substraction.

You are looking to the two nearest input so :

SELECT x, y 
FROM my_table
ORDER BY Abs(:val - x)
LIMIT 2

However this will lead to a full table scan.