How to apply a parametrised SQLITE query to each row of a table returned by another query (in the context of interpolation)?

Question

Input

My input consists of x_0_y_0 and x_1.

x_0_y_0 (table dates) contains the dates and values of the form:

timestamp   value
2000-01-01  1
2000-01-03  3
2000-01-05  5

x_1 (query dates_to_interpolate) contains the dates of the form:

timestamp
2000-01-01
2000-01-02
2000-01-03
2000-01-04
2000-01-05

Output

My desired output x_1_y1 (query interpolation_results) would be of the form:

timestamp    value
2000-01-01   1
2000-01-02   2
2000-01-03   3
2000-01-04   4
2000-01-05   5

Method (Attempt)

Using info in this thread and some native sqlite syntax I managed to get a query which gives an interpolated value (say 4) for a single selected date, say '2000-01-04'.

SELECT CASE WHEN next.timestamp IS NULL  THEN prev.value
            WHEN prev.timestamp IS NULL  THEN next.value
            WHEN next.timestamp = prev.timestamp  THEN prev.value
              ELSE ( ( julianday(date('2000-01-04')) - julianday(prev.timestamp) ) * next.value
                +    ( julianday(next.timestamp) - julianday(date('2000-01-04')) ) * prev.value
                   ) / (julianday(next.timestamp) - julianday(prev.timestamp))
       END AS interpolated_value 
FROM 
(SELECT dates.timestamp, dates.value FROM dates WHERE dates.timestamp <= date('2000-01-04') ORDER BY dates.timestamp DESC LIMIT 1) AS prev
CROSS JOIN 
(SELECT dates.timestamp, dates.value FROM dates WHERE dates.timestamp >= date('2000-01-04') ORDER BY dates.timestamp ASC LIMIT 1) AS next

Now I would like to parametrise this using @interpolation_date rather than '2000-01-04' so I have a function of the form interpolated_value(). It seems this could work if I was to call this programatically (say from Python or MATLAB). The problem is that I would like to apply interpolated_value() to dates_to_interpolate using sqlite syntax only (in a single query). I found out that sqlite does not support parameters in views, it does not support custom functions. I did see attempts at implementing loops (which I think I might need here); loops seems to involve recursive triggers or WITH syntax which I am not quite familiar with.

Answer 1

You want something like this:

SELECT timestamp,
       (...) AS value
FROM x1;

This can simply be done by making the other query a correlated subquery, i.e., just replace '2000-01-04' with x1.timestamp wherever it occurs:

SELECT timestamp,
       (SELECT CASE WHEN next.timestamp IS NULL  THEN prev.value
                    WHEN prev.timestamp IS NULL  THEN next.value
                    WHEN next.timestamp = prev.timestamp  THEN prev.value
                      ELSE ( ( julianday(x1.timestamp) - julianday(prev.timestamp) ) * next.value
                        +    ( julianday(next.timestamp) - julianday(x1.timestamp) ) * prev.value
                           ) / (julianday(next.timestamp) - julianday(prev.timestamp))
               END AS interpolated_value 
        FROM 
        (SELECT dates.timestamp, dates.value FROM dates WHERE dates.timestamp <= x1.timestamp ORDER BY dates.timestamp DESC LIMIT 1) AS prev
        CROSS JOIN 
        (SELECT dates.timestamp, dates.value FROM dates WHERE dates.timestamp >= x1.timestamp ORDER BY dates.timestamp ASC LIMIT 1) AS next
       ) AS value
FROM x1;