FIRST() and LAST() are the better choice, actually. Some of the data coming through using DISTINCT() was incorrect.
Thank you, On Tuesday, 4 October 2016 15:52:05 UTC-7, Sean Beckett wrote: > > FIRST() or LAST() functions would also work to "select" the only value in > the 10s buckets. They are also guaranteed never to return multiple values, > which might be important. DISTINCT() could return multiple values if the > timestamps don't stay very regular. > > On Tue, Oct 4, 2016 at 3:07 PM, William Cork <[email protected] > <javascript:>> wrote: > >> Spent all this time writing, found the answer right after posting. Here >> is my solution for the record: >> >> Use DISTINCT as the function. >> >> >> >> On Tuesday, 4 October 2016 13:56:32 UTC-7, William Cork wrote: >>> >>> We have some data that we would like to subsample without any >>> modifications to the data. What's needed is to simply drop the data between >>> certain intervals. >>> >>> There are 3 retention policies. The one consuming the values (30 day >>> retention, 10Hz samples) and two other subsamples (1 year retention, 10s >>> samples 5ms offset; INF retention, 3 minute samples 5ms offset). >>> >>> It seems that all queries with GROUP BY time(...) require an aggregate >>> function to operate. Is there a means to collect a subsample without any >>> functions operating on our data? Some of our values are discrete (ex. a >>> state of 0.5 does us no good). >>> >>> Basically, if we have data that looks like: >>> >>> > SELECT pvName,value FROM "30_days"."BPM" WHERE pvName = >>> 'BR1:BPM2:SA:X' GROUP BY pvName LIMIT 10 >>> name: BPM >>> tags: pvName=BR1:BPM2:SA:X >>> time pvName value >>> ---- ------ ----- >>> 2016-09-12T17:58:22.573523283Z BR1:BPM2:SA:X 1411764 >>> 2016-09-12T17:58:22.673306941Z BR1:BPM2:SA:X 858536 >>> 2016-09-12T17:58:22.773090839Z BR1:BPM2:SA:X 1170731 >>> 2016-09-12T17:58:22.872874736Z BR1:BPM2:SA:X 1326829 >>> 2016-09-12T17:58:22.972658634Z BR1:BPM2:SA:X 1182265 >>> 2016-09-12T17:58:23.072436571Z BR1:BPM2:SA:X 1170731 >>> 2016-09-12T17:58:23.172220468Z BR1:BPM2:SA:X 866995 >>> 2016-09-12T17:58:23.272004365Z BR1:BPM2:SA:X 1120000 >>> 2016-09-12T17:58:23.371788024Z BR1:BPM2:SA:X 1279999 >>> 2016-09-12T17:58:23.471571922Z BR1:BPM2:SA:X 1497536 >>> >>> Is there a way to subsample into a CQ with something like: >>> >>> > CREATE CONTINUOUS QUERY "cq_10s" ON "BPM" BEGIN >>> SELECT pvName,value >>> INTO "1_year"."BPM" >>> WHERE pvName = /.*/ >>> FROM "30_days"."BPM" GROUP BY time(10s,5ms) >>> END >>> >>> I may have messed up the wildcard there, too. >>> >>> >>> Is this sort of thing possible with InfluxQL? >>> >>> Thanks, >>> -Will >>> >> -- >> Remember to include the InfluxDB version number with all issue reports >> --- >> You received this message because you are subscribed to the Google Groups >> "InfluxDB" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> Visit this group at https://groups.google.com/group/influxdb. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/influxdb/57ecffc0-54d4-4339-bf9f-47535159dab7%40googlegroups.com >> >> <https://groups.google.com/d/msgid/influxdb/57ecffc0-54d4-4339-bf9f-47535159dab7%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> >> For more options, visit https://groups.google.com/d/optout. >> > > > > -- > Sean Beckett > Director of Support and Professional Services > InfluxDB > -- Remember to include the InfluxDB version number with all issue reports --- You received this message because you are subscribed to the Google Groups "InfluxDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/influxdb. To view this discussion on the web visit https://groups.google.com/d/msgid/influxdb/cb231101-5a57-4224-b96b-b66ef216e3f2%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
