That sounds similar to what I ended up doing. There's always a way... but sometimes the way gets a little more complex than it seems like it should.
On Friday, March 25, 2016 at 1:51:08 PM UTC-5, Todd White wrote: > > I'm new (two weeks) to InfluxDB and struggling with a query. > > I have a measurement of "work in process" that gets the count of work > items in process for several processes, collected at 5 minute intervals. > > So, for a single process the measure at different times may go up or down > depending on how many new items were added vs. how many were removed > (worked). Basically a measure of queue sizes. > > I want to write queries against this measurement grouped by different > times (10m, 1h, 1d, ...) that SUM the values at the end of each time > period. In otherwords, what was the total number of work items in process > at the end of each time period where the time periods can vary. > > I've tried to mock up sample below. > > > insert wip,process_name=Process1 wip_ct=50 1458922800000000000 > > insert wip,process_name=Process2 wip_ct=36 1458922800000000000 > > insert wip,process_name=Process3 wip_ct=1 1458922800000000000 > > insert wip,process_name=Process1 wip_ct=54 1458923100000000000 > > insert wip,process_name=Process2 wip_ct=24 1458923100000000000 > > insert wip,process_name=Process3 wip_ct=3 1458923100000000000 > > insert wip,process_name=Process1 wip_ct=53 1458923400000000000 > > insert wip,process_name=Process2 wip_ct=6 1458923400000000000 > > insert wip,process_name=Process3 wip_ct=7 1458923400000000000 > > insert wip,process_name=Process1 wip_ct=47 1458923700000000000 > > insert wip,process_name=Process1 wip_ct=4 1458924000000000000 > > insert wip,process_name=Process2 wip_ct=49 1458924000000000000 > > > select * from wip > name: wip > --------- > time process_name wip_ct > 1458922800000000000 Process1 50 > 1458922800000000000 Process3 1 > 1458922800000000000 Process2 36 > 1458923100000000000 Process3 3 > 1458923100000000000 Process2 24 > 1458923100000000000 Process1 54 > 1458923400000000000 Process2 6 > 1458923400000000000 Process1 53 > 1458923400000000000 Process3 7 > 1458923700000000000 Process1 47 > 1458924000000000000 Process2 49 > 1458924000000000000 Process1 4 > > Querying using a sum() gets the sum of all wip counts within each time > grouping, not the sum of the max(time) counts withing each time group > > > select sum(wip_ct) from wip where time > now() - 1h group by time(10m) > fill(0) > name: wip > --------- > time sum > 1458921600000000000 0 > 1458922200000000000 0 > 1458922800000000000 168 > 1458923400000000000 113 > 1458924000000000000 53 > 1458924600000000000 0 > 1458925200000000000 0 > > Querying using a last() just gets one of the values within each time > grouping. So, in the ten minute period containing 1458922800000000000 to > 1458923100000000000, it picked 54 as the single value to return. > > > select last(wip_ct) from wip where time > now() - 1h group by time(10m) > fill(0) > name: wip > --------- > time last > 1458921600000000000 0 > 1458922200000000000 0 > 1458922800000000000 54 > 1458923400000000000 47 > 1458924000000000000 49 > 1458924600000000000 0 > 1458925200000000000 0 > > I don't think I can combine aggregates, but I was hoping to work towards > something like the following: > > > select sum(last(wip_ct)) from wip where time > now() - 1h group by > time(10m) fill(0) > ERR: error parsing query: expected field argument in sum() > > Trying to restrict the summed values to only those falling on the last > time within each time group (the time = last(time) part of the where > clause) still gives the sum of all times within each time group. > > > select sum(wip_ct) from wip where time > now() - 1h and time = > last(time) group by time(10m) fill(0) > name: wip > --------- > time sum > 1458922200000000000 0 > 1458922800000000000 168 > 1458923400000000000 113 > 1458924000000000000 53 > 1458924600000000000 0 > 1458925200000000000 0 > 1458925800000000000 0 > > I also tried changing the 2nd part of the WHERE clause to "time = > max(time)" which produced the same results > > So, how can I get the sum of all values for the last timestamp within each > group, or where the time within each group equals the max time within each > group? > > Thanks, > Todd > > -- 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/14f26a0a-acb0-4c52-86d3-13b661aff5bd%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
