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

Go To StackoverFlow.com

7

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'
2012-04-04 00:22
by eaolson
Define "previous". Looks like the single permitted record for the date one day before the current record. Is that always true - Larry Lustig 2012-04-04 01:15
No. I've expanded the example. By "previous" I basically mean the row row before the one in question, once they're ordered - eaolson 2012-04-04 01:41
Define "ordered". Basically, the problem is that SQL databases (by definition) have no concept of row ordering aside from what you specify specifically, by column name, on retrieval. You can generally get the result you want, but you are going to have to derive the "previous row" through relational algebra in your view definition and, to do that, you need to be very clear about how you define "previous" in plain language - Larry Lustig 2012-04-04 10:51
I've updated my question to give you a better idea of what I'm looking for. I could do it with an Oracle analytic function, but not sure how to do it in sqlite - eaolson 2012-04-05 00:35


2

Its the same idea as the other, but just uses the fields instead of the rowid. This does exactly what you want:


CREATE TABLE Prices (
    day DATE,
    price FLOAT
);

INSERT INTO Prices(day, price) VALUES(date('now', 'localtime', '+1 day'), 0.5);
INSERT INTO Prices(day, price) VALUES(date('now', 'localtime', '+0 day'), 1);
INSERT INTO Prices(day, price) VALUES(date('now', 'localtime', '-1 day'), 2);
INSERT INTO Prices(day, price) VALUES(date('now', 'localtime', '-2 day'), 7);
INSERT INTO Prices(day, price) VALUES(date('now', 'localtime', '-3 day'), 8);
INSERT INTO Prices(day, price) VALUES(date('now', 'localtime', '-4 day'), 10);

SELECT p1.day, p1.price, p1.price-p2.price 
FROM
    Prices p1, Prices p2,
    (SELECT t2.day AS day1, MAX(t1.day) AS day2 
    FROM Prices t1, Prices t2
    WHERE t1.day < t2.day
    GROUP BY t2.day) AS prev
WHERE p1.day=prev.day1
    AND p2.day=prev.day2

If you want to add the WHERE item='apple' bit you'd add that to both WHERE clauses.

2012-04-05 07:33
by chacham15


2

This should do the trick for every item (tested on SQLite):

SELECT 
    day
    ,price
    ,price - (SELECT t2.price 
              FROM mytable t2 
              WHERE 
                  t2.item = t1.item AND 
                  t2.day < t1.day      
              ORDER BY t2.day DESC
              LIMIT 1
             ) AS change
FROM mytable t1

This assumes the combination between day and item is unique. And the way it works is by taking all the values less than the given day, sorting descending and then LIMIT just the first value, simulating a LAG function.

For a LEAD behavior, just flip < to > and DESC to ASC.

2017-12-04 10:07
by DomingoR


1

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;
2018-08-17 19:31
by Lukasz Szozda


0

Assuming that you don't delete this will work:


SELECT t2.DAY, t2.price, t2.price-t1.price 
FROM TABLENAME t1, TABLENAME t2 
WHERE t1.rowid=t2.rowid-1

This works because every row has its own rowid even if you dont specify it in the CREATE statement.

If you do delete, it becomes:


SELECT t2.day, t2.price, t2.price-t1.price 
FROM 
     (SELECT l1.day, l1.price, 
          (SELECT COUNT(*) 
          FROM TABLENAME l2 
          WHERE l2.rowid < l1.rowid) AS count
      FROM TABLENAME l1) AS t1,
     (SELECT l1.day, l1.price, 
          (SELECT COUNT(*) 
          FROM TABLENAME l2 
          WHERE l2.rowid < l1.rowid) AS count
      FROM TABLENAME l1) AS t2
WHERE t1.count=t2.count-1

This works under the assumption that rowids are always increasing.

2012-04-04 00:37
by chacham15
That makes assumptions about the history of the table (order the rows were inserted, that they're never updated, etc.) that I'd rather not make - eaolson 2012-04-04 01:44