Hit Send inadvertandly, sorry.
My rowid isn't increasing with something predictable, so I can't do
something like WHERE rowid % n = 0. I can use WHERE random() % n = 0
giving me sort of what I want (better than row % n, but I still need
something better).
If your sampling is essentially regular, why not make it
select ... where timestamp % N between min_interval and max_interval
N being the typical time delta of your n rows above and interval
bounds reducing the possiblity of gross under- and over-sampling. May
need adjustment but the query should run faster than full table load.
Let's assume your timestamps are unix epoch and your device samples on
an average 1-second basis (delays in processing could cause two samples
with the same timestamp or no sample with a given epoch). You're
certain that there is always at least one sample between seconds (say)
8 and 11 of every hour. You want only one sample every hour, taken
randomly(*) between seconds 8 and 11.
select sample from from mytable where timestamp % 86400 between 8 and
11 group by timestamp / 86400;
(*) SQL[ite] doesn't give you a chance to specify which row will
represent each group, hence the randomly in 8..11
Would that do what you want?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
