Create a SQLite view where a row depends on the previous row

sql, sqlite

Solution

Oracle equivalent is correct. Starting from SQLite 3.25.0 you could use `LAG` natively:

WITH mytable(ITEM,DAY,PRICE) AS (
    VALUES
    ('apple',  CAST('20110107' AS DATE),    1.25),
    ('orange', CAST('20110102' AS DATE),    1.00),
    ('apple',  CAST('20110101' AS DATE),    1.00),
    ('orange', CAST('20110103' AS DATE),    2.00),
    ('apple',  CAST('20110108' AS DATE),    2.00),
    ('apple',  CAST('20110110' AS DATE),    1.50)
)
SELECT day, price, price-LAG(price) OVER (ORDER BY day) AS change
FROM mytable
WHERE item = 'apple'
ORDER BY DAY;

db<>fiddle demo

Problem

I'd like to create a view in SQLite where a field in one row depends on the value of a field in the previous row. I could do this in Oracle using the `LAG` analytic function, but not sure how to go about it in SQLite. For example, if my table looked like: ``` ITEM DAY PRICE apple 2011-01-07 1.25 orange 2011-01-02 1.00 apple 2011-01-01 1.00 orange 2011-01-03 2.00 apple 2011-01-08 1.00 apple 2011-01-10 1.50 ``` I'd like my view to look like, with `WHERE item = 'apple'`: ``` DAY PRICE CHANGE 2011-01-01 1.00 (null) 2011-01-07 1.25 0.25 2011-01-08 2.00 0.75 2011-01-10 1.50 -0.50 ``` Edit: The equivalent of the query I'm looking for would look in Oracle something like (haven't tried this, but I think this is correct): ``` SELECT day, price, price - LAG( price, 1 ) OVER ( ORDER BY day ) AS change FROM mytable WHERE item = 'apple' ```

Original source