Flux: Distinct grouping of addresses

Grafana InfluxDB
1

Hello!

I have the following topic.
Let’s say I have the following data

18.11.2023 13:00     Address_1
18.11.2023 14:00     Address_2
18.11.2023 15:00     Address_1
18.11.2023 16:00     Address_3
18.11.2023 17:00     Address_1

I want to draw a bar chart showing me groupd by address the number of visits in the choosen time periode. Like:

Address_1       3
Address_2       1
Address_3       1

Unfortunately I am not able to combine group, count, … to reach the right result

From searching I tried different hints … but none worked e…g

from(bucket: "iobroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "Adresse"  and r["_field"] == "value")
 |> duplicate(column: "_value", as: "new_column")
  |> group(columns: ["new_column"])
  |> count()
2

Hi @dieterlind

How about something like this?

from(bucket: "your_bucket")
  |> range(start: 2023-11-18T13:00:00Z, stop: 2023-11-18T17:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "your_measurement") // Replace with your actual measurement name
  |> group(columns: ["_field"])
  |> count(column: "_value")
  |> yield(name: "count")
3

This results in:

image
image943×747 42.5 KB

Actually I just want the different “values” on the left side and the count(distinct) on the right side …

4

What are all the possible values for _measurement? If its Address_1, Address_2, etc. then try changing these lines as shown & report back…

  |> group(columns: ["_measurement"])
  |> count()
5

Hi!

Yes, it can be several addresses which occur 1 - n times and shall be grouped.

After changing I get

image
image937×694 24.5 KB

So the result is the total number of addresses found but not grouped by addresses
The different addresses are in the “_value” column

6

What happens if you put a distinct function before the count function, like this?

|> distinct(column: "_measurement")
7

like this?

from(bucket: "iobroker")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Adresse")
  |> group(columns: ["_measurement"])
  |> distinct(column: "_measurement")

gives an error
Status: 500. Message: invalid: runtime error @5:6-5:38: distinct: schema collision: cannot group boolean and string types together

this also doesn’t work with the same error:

from(bucket: "iobroker")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Adresse")
  |> group()
  |> distinct(column: "_measurement")
8

How about this…Could you run this query in Influx Data Explorer and then upload here the csv of the output? That will make it easier for the community to evaluate and help you find a solution.

from(bucket: "iobroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Adresse"  and r["_field"] == "value")
  |> yield(name: "help")
9

If the csv-export button would still be available I could provide it … but somehow this is gone

image
image1821×245 23.3 KB

wasn’t the button typically next to the timeframe to select or “view raw data”?

10

Something like

measurement,tag1,location,.parking-position.latitude,time
.parking-position.latitude,parking,Accord,41.7972,1700152037000000873

measurement,tag1,location,.parking-position.longitude,time
.parking-position.longitude,parking,Accord,-74.2319,1700152037000000873

Type it in here manually

11

I think it was last seen in v2.6

Maybe you can copy/paste the data (or at least ~10 representative lines) so we can try to help you.