How's about you store your interpolated value during insert? You can use a binary mask of say, 16384, to indicate the value is interpolated in case you need to know that. In the original data you sent only one value can be interpolated at record 3. You probably want the interpolation to be weighted towards the times when separated by more than one time interval. That should be able to done during the update too I think thought that syntax is a bit beyond me at the moment.
create table v(a integer primary key,b,c,d,e,f); create trigger v_insert after insert on v when new.a > 1 and new.b is not null and (select b from v where a=new.a-1) is null begin update v set b=(new.b+(select b from v where v.a<new.a-1 and b is not null order by a desc limit 1))/2|16384 where a=new.a-1; end; insert into v values(0,null,null,2,null,9); insert into v values(1,1 ,null,3,null,8); insert into v values(2,1 ,null,4, 4,7); insert into v values(3,null,5, , 4,6); insert into v values(4,1 ,6, 6,null,5); select * from v; 0|||2||9 1|1||3||8 2|1||4|4|7 3|16385|5|5|4|6 4|1|6|6||5 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Steinar Midtskogen [stei...@latinitas.org] Sent: Friday, October 19, 2012 2:09 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Find first non-NULL values of several columns Thank you for all suggestions. I will need to do such queries often, so it's just a matter of saving the typing. Unfortunately, views aren't going to be very practical either, because there are a lot of tables and columns (100+), and new ones will be added. The actual use case is as follows: I have tables with a timestamp (unix time) and columns containing sensor readings which are inserted continuously. I frequently need to access the most recent values (or NULL if there is no value within the latest, say, hour). I would like to do something like: SELECT coalesce(col_1), ..., coalesce(col_n) FROM v WHERE unix_time > strftime('%s', 'now', '-1 hour') ORDER BY unix_time DESC; So I would typically want to access the last non-NULL value because of the DESC keyword. But if I understand things correctly, a statement like above will never work because an aggregate function reads the data in no particular order regardless of the ORDER BY statement. I like Igor's suggestion. Although not quite universal, it's clever. Ryan's suggestion should work well, except that I will need a first(a, b) and last(a, b) function (if I want to support both ascending and descending order) and I can leave out the ORDER BY part. So: SELECT last(col_1, unix_time), ..., last(col_n, unix_time) FROM v WHERE unix_time > strftime('%s', 'now', '-1 hour'); Yes, it will have run through the whole set, whereas multiple SELECT col_x FROM v WHERE unix_time > strftime('%s', 'now', '-1 hour') AND col_x IS NOT NULL ORDER BY unix_time DESC LIMIT 1 will stop early. But this will not be a problem for me since I want to have a modest upper limit (1 hour) anyway. -- Steinar Midtskogen _______________________________________________ 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