On 19/10/2012 3:09 PM, Steinar Midtskogen wrote:
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:
Is each column associated with a different sensor? If so, do sensors
routinely generate data at the same time and combine their entries? Do
sensors emit data frequently?
If any of the above is false, I'd definitely store each column in its
own table, because the timestamp (the only possible thing to share) is
replicated anyway. That does get you back to the multiple subquery
thing, but a few views should hide that pretty effectively.
Unfortunately, it doesn't look like sqlite3 is smart enough to push
projections down through views, otherwise a single
"view-to-rule-them-all" would have zero runtime overhead and save a
*lot* of typing.
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.
Indeed it is. And very useful at times.
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');
Or just pass -unix_time to first(), seeing as how it's a number.
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
