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]> 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]. > 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/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/CALGqCvNBDRn7A4adEjxtHgR3ES_3DA8%2BDa-%3Dd6FOA%3DFv9m-uaA%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
