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
