A.L. Verminburger A.L. Verminburger - 6 months ago 19
SQL Question

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

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.

CL. CL.
Answer

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;