0

in the question Take the median of a grouped set I asked how it's possible to get the mean() value of a set of measurements over time.

Background:

I am storing lighthouse-data. Each lighthouse-run is a measuring_point and belongs to a measurement. Each measurement has its own unique uuid.

So the structure does look like this:

  • site
    • measurement
      • measurement_point
      • measurement_point
      • measurement_point
    • measurement
      • measurement_point
      • measurement_point
      • measurement_point
  • site
    • measurement
      • measurement_point
      • measurement_point
      • measurement_point
    • measurement
      • measurement_point
      • measurement_point
      • measurement_point

For each measurement_point I am getting the mean()-value as explained here: Take the median of a grouped set

from(bucket: "test")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "lighthouse")
  |> filter(fn: (r) => r["_field"] == "speedindex")
  |> filter(fn: (r) => r["site"] == "1d1a13a3-bb07-3447-a3b7-d8ffcae74045")
  |> group(columns: ["measurement"])
  |> mean()
  |> yield(name: "mean")

Now I saw that if I do that, the records ore "losing" their _time, which leads to this graph:

enter image description here

As you can see: Although the measurements have not been performed at the same time, the values are "stacked" at the same time.

If I render a simple table, I am getting this result:

enter image description here

This is fine, but I guess it shows the problem: The mean() value is calculated but there is no thing as a "mean time". And even if I choose median, which should pick the median value (which would have a time, I guess) the _time-value is missing.

What can I do to get the time back?

SPQRInc
  • 162
  • 4
  • 23
  • 64
  • 1
    Technically, median could have a time (it doesn't), but do you really need it? All these functions do some aggregation over a period, and for most the same aggregation make no sense to be applied to time. You can identify the period either by start or stop time. `aggregateWindow` function by default fills `_time` with stop time (see `timeSrc` and `timeDst` params) by simply calling `|> duplicate(column: timeSrc, as: timeDst)`, .ie `|> duplicate(column: "_stop", as: "_time")` – alespour Nov 18 '22 at 15:50
  • Hi, hm, I don't know if I get you correctly. Why doesn't it make sense to be applied on time? What I want to get: For each measurement (contains 3 values) the one "in the middle". And as I possibly get thousands of measurements, I want to graph the results later and to do that I'd need the time for the x-axis? So I guess I don't really get what you mean, but maybe this can help to clarify my question. – SPQRInc Nov 18 '22 at 21:14
  • You only have `_start` and `_stop` time (marking the aggregation time window) available that you can chose as time for x-axis. – alespour Nov 21 '22 at 08:56

0 Answers0