我想在sqlite中创建一个视图,其中一行中的字段取决于前一行中字段的值.我可以使用LAG分析函数在Oracle中执行此操作,但不确定如何在sqlite中进行此操作.
例如,如果我的表看起来像：
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
我希望我的视图看起来像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
编辑：
相当于我正在寻找的查询会在Oracle中看起来像(没试过这个,但我认为这是正确的)：
SELECT day,price,price - LAG( price,1 ) OVER ( ORDER BY day ) AS change FROM mytable WHERE item = 'apple'
它与另一个相同,但只使用字段而不是rowid.这完全符合您的要求：
CREATE TABLE Prices (
day DATE,price FLOAT
);
INSERT INTO Prices(day,price) VALUES(date('now','localtime','+1 day'),0.5);
INSERT INTO Prices(day,'+0 day'),1);
INSERT INTO Prices(day,'-1 day'),2);
INSERT INTO Prices(day,'-2 day'),7);
INSERT INTO Prices(day,'-3 day'),8);
INSERT INTO Prices(day,'-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
