Create a SQLite view where a row depends on the previous row
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'
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.
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 .
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.
链接地址: http://www.djcxy.com/p/10642.html