Correctly aggregate over space and time

1

Hey team, struggling to wrap my head around writing a query to correctly aggregate over space and time.

In this example I am looking at an inventory metric (a point in time metric), and so I the time aggregation needs to be last. You can see me doing this using aggregateWindow to aggregate my data over time with fn=last. However, as soon as I want to use a group transformation, last no longer makes sense. I want it to aggregate last over time for each series, and then group using a sum over space. How can I do a group sum but an aggregateWindow last ? I’ve attached a screenshot of the my data. Setting group="", should result in a single line of 65.

Screen Shot 2021-05-05 at 10.25.36 AM
Screen Shot 2021-05-05 at 10.25.36 AM2556×1152 446 KB

2

Hello @Daniel_Langer,
I would imagine that it would result in several tables for each timestamp where aggregateWindow() was applied with:

from(bucket: "system")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "cpu")
  |> filter(fn: (r) => r["_field"] == "usage_system" or r["_field"] == "usage_user")
  |> filter(fn: (r) => r["cpu"] == "cpu-total")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
// using limit just to narrow the results and help understand what is happening with the transformation
  |> limit(n: 2)
  |> group(columns: ["_time"], mode:"by")
  |> sum()

Or one table with multiple lines for each timestamp where you applied aggregateWindow:

import "influxdata/influxdb/schema"
from(bucket: "system")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "cpu")
  |> filter(fn: (r) => r["_field"] == "usage_system" or r["_field"] == "usage_user")
  |> filter(fn: (r) => r["cpu"] == "cpu-total")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> limit(n: 2)
  |> schema.fieldsAsCols()
  |> map(fn: (r) => ({ r with _value: r.usage_user + r.usage_system }))

Does that help at all?

Thanks.