create table taglist (tagid integer primary key autoincrement, tagname text
collate nocase unique);
create table tagdata (tagid integer references taglist(tagid), timestamp
integer not null, value not null, unique(tagid, timestamp));
select tagname, coalesce(timestamp, tsstart), value
from taglist, (select strftime('%s', 'now') as tsend, strftime('%s', 'now',
'-1 hour') as tsstart) as tsrange,
outer join tagdata
where tagname='flowrate'
and tagdata.tagid = taglist.tagid
and timestamp between tsstart and tsend;
will implement a sparse table that you query per tag.
Alternatively, to implement your original question, write an aggregate function
which returns the first non-null value it comes across, and use the negated
unix timestamp as an explicit rowid, depending on the fact that a table-scan
does an in-order traversal of the table btree:
create table data
(
ts integer primary key,
v1 float,
v2 float,
v3 float,
v4 float
);
insert into data values (-9, 1, NULL, NULL, NULL);
insert into data values (-8, NULL, 2, NULL, NULL);
insert into data values (-7, 5, NULL, 3, NULL);
insert into data values (-6, NULL, 6, NULL, 4);
insert into data values (-5, NULL, NULL, 7, NULL);
insert into data values (-4, NULL, NULL, NULL, 8);
insert into data values (-3, NULL, NULL, NULL, NULL);
insert into data values (-2, 9, NULL, 9, NULL);
insert into data values (-1, NULL, NULL, NULL, NULL);
select * from data;
-9|1.0|||
-8||2.0||
-7|5.0||3.0|
-6||6.0||4.0
-5|||7.0|
-4||||8.0
-3||||
-2|9.0||9.0|
-1||||
select fnn(v1), fnn(v2), fnn(v3), fnn(v4)
from data
where ts between -9 and 0;
1.0|2.0|3.0|4.0
select fnn(v1), fnn(v2), fnn(v3), fnn(v4)
from data
where ts between -8 and 0;
5.0|2.0|3.0|4.0
select fnn(v1), fnn(v2), fnn(v3), fnn(v4)
from data
where ts between -7 and 0;
5.0|6.0|3.0|4.0
select fnn(v1), fnn(v2), fnn(v3), fnn(v4)
from data
where ts between -6 and 0;
9.0|6.0|7.0|4.0
select fnn(v1), fnn(v2), fnn(v3), fnn(v4)
from data
where ts between -9 and -7;
1.0|2.0|3.0|
Your custom aggregate function looks like this:
typedef struct fnnCtx fnnCtx;
struct fnnCtx
{
double fnn;
int flag;
};
SQLITE_PRIVATE void fnnStep(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
fnnCtx* p = sqlite3_aggregate_context(context, sizeof(fnnCtx));
if (p && sqlite3_value_numeric_type(argv[0]) != SQLITE_NULL && p->flag == 0)
{
p->fnn = sqlite3_value_double(argv[0]);
p->flag = 1;
}
}
SQLITE_PRIVATE void fnnFinal(sqlite3_context *context)
{
fnnCtx* p = sqlite3_aggregate_context(context, 0);
if (p && p->flag == 1)
sqlite3_result_double(context, p->fnn);
}
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
