For the quick and dirty solution, I think you can use something like this to create your view. You would need to index time, and even with the index, I'm not too sure about speed.
select x1.time, x1.value, x2.time from x as x1 left join x as x2 on x2.time=(select max(time) from x where time<x1.time); I would favor the virtual table approach, as I think the other solutions require more complicated queries to account for the fact that interpolation is going on. I imagine something like: create table x(time integer, value integer); create virtual table interp_x using interpolator(x,start=1325376000,stop=1325379600,granularity=1) Would then allow you to do whatever you want with ease. Kev On 8 Feb 2012, at 11:14, Steinar Midtskogen wrote: > Related to this thread, I wonder if it's possible to create a view > which can give me a value from the row immediately above. E.g. given > the table: > > unix_time val > ----------+--- > 1325376000|val1 > 1325376300|val2 > 1325376600|val3 > 1325376900|val4 > > (the first column is a unix timestamp and unique) > > can I create a view which gives me: > > unix_time val prev_unix_time > ----------+----+---------- > 1325376000|val1| > 1325376300|val2|1325376000 > 1325376600|val3|1325376300 > 1325376900|val4|1325376600 > > Something like this will not work: > > create view new as select unix_time, val, (select unix_time from old where > new.unix_time < old.unix_time order by unix_time desc limit 1) as > prev_unix_time from old; > > as I can't refer to new.unix_time inside the view that defines "new". > > The idea is, if this is possible, then I should be able to get my > weighted average by something like this (not verified, but you get the > idea): > > select sum(val * (unix_time - prev_unix_time)) / sum(unix_time - > prev_unix_time) from new; > > -- > Steinar > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
