I have a view in SQLite where two columns need to follow a recursive formula. Example input data is below:

CREATE TABLE "Test" (
    "Date"  DATE,
    "Ca"    NUMERIC,
    "Cb"    NUMERIC,
    "Cc"    NUMERIC
);

INSERT INTO Test (Date, Ca, Cb, Cc)
VALUES ( '2020-01-01',  NULL, NULL, 100.0),
( '2020-01-02', 0.1, NULL, NULL),
( '2020-01-03', 0.2 , NULL, NULL),
( '2020-01-04', 0.1 ,  NULL, NULL),
( '2020-01-05', 0.4 , NULL, NULL),
( '2020-01-06', 0.3 , NULL, NULL),
( '2020-01-07', 0.2 , NULL, NULL),
( '2020-01-08', 0.4 , NULL, NULL)
;

The requirement is for columns [Cb] and [Cc] to be recursive such that (in rough SQL logic):

• [Cb] = LAG([Cc]) * [Ca]
• [Cc] = LAG([Cc]) [Cb]

So the second cell in column [Cb] would be equal to 10 or 100 * 0.1 and the second cell in [Cc] would be equal to 110 or 100 10.

The full solution, for this example, would look like this:

The length of the table is dynamic, so the recursive solution must be dynamic as well.

I have read through and explored SQLite's recursive CTE feature, but I can't seem to figure out how to apply this in the context of a view. Or it is possible I should be approaching this problem differently. Any help or guidance would be greatly appreciated.

CodePudding user response：

In this context, there is no difference between a view and a table. First, I add row_number, so that it is straightforward to select the next row in the rCTE. Then you can calculate your target:

WITH RECURSIVE
    numbered AS (
        SELECT row_number() OVER (ORDER BY "Date") AS idx, * FROM Test
    ),
    new_test AS (
            SELECT * FROM numbered WHERE idx = 1
        UNION ALL
            SELECT
                numbered.idx,
                numbered."Date",
                numbered.Ca,
                new_test.Cc * numbered.Ca AS Cb,
                new_test.Cc   new_test.Cc * numbered.Ca AS Cc
            FROM new_test, numbered
            WHERE numbered.idx = new_test.idx   1
    )
SELECT "Date", Ca, round(Cb, 1) AS Cb, round(Cc, 1) AS Cc FROM new_test