Crear una vista SQLite donde una fila depende de la fila anterior

Me gustaría crear una vista en SQLite, donde un campo en una fila depende del valor de un campo en la fila anterior. Podría hacer esto en Oracle utilizando la function analítica de LAG , pero no estoy seguro de cómo hacerlo en SQLite.

Por ejemplo, si mi tabla se veía así:

 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 

Me gustaría que se vea mi vista, con 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 

Editar:

El equivalente de la consulta que busco se vería en Oracle algo así como (no lo he probado, pero creo que es correcto):

 SELECT day, price, price - LAG( price, 1 ) OVER ( ORDER BY day ) AS change FROM mytable WHERE item = 'apple' 

Es la misma idea que la otra, pero solo usa los campos en lugar del rowid. Esto hace exactamente lo que quieres:

 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 

Si desea agregar el WHERE item='apple' , lo agregará a ambas cláusulas WHERE .

Suponiendo que no elimines esto funcionará:

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

Esto funciona porque cada fila tiene su propio rowid incluso si no lo especifica en la instrucción CREATE .

Si eliminas, se convierte en:

 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 

Esto funciona bajo la suposition de que los rowids siempre están aumentando.

Esto debería ser el truco para cada item (probado en 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 

Esto supone que la combinación entre el day y el item es única. Y la forma en que funciona es tomando todos los valores menores que el day dado, clasificando descendente y luego LIMIT solo el primer valor, simulando una function LAG .

Para un comportamiento LEAD , simplemente voltea < to > y DESC a ASC .