[Jean-Christophe Deschamps] >>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?
This is a good suggestion. A drawback is that the interval can't be too small otherwise there is a risk that a sample would be missed. So I will get more samples than I need. In you example, if there is a sample every second more or less, I would usually get 3-4 samples every hour instead of just one which would suffice. In reality I have around 5 samples every 300 seconds, and almost certainly at least one sample within 320 seconds. Then, most of the 5 samples will typically be within a few seconds, and rarely spread evenly across the 300 second interval. So the sampling is only semi-regular. I will mostly be selecting from a virtual table, so another option for me could be to add a column which simply returns an incrementing number, which I can use % on, or perhaps better a column containing a flag which is set every time the timestamp has increased more than a certain limit since the last time the flag was set. But something less ad hoc is better. -- Steinar _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
