Counting number of groups

Fluxlang
,
1

I am trying to do something that seems like a simple use case, but can’t get the Script Editor (or the Query Builder, for that matter) to show the data I’m trying to look for.

I am trying to group by a tag value, in my case a clientID tag that my data points all have. From there, I am trying to count the number of clients I have for a given time period so I can get some insight into how many unique clients are reporting data. In short: I’m trying to get a count of unique values for a given tag over a time range.

An example from my bucket:

image
image1652×176 10.2 KB

Running this query:

from(bucket: "brawnfire")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> group(columns: ["clientID"])
  |> count(column: "_value")

I get the expected result (I can only post one image, so text will have to do – I’ve removed some of the clutter for brevity)

table | clientID                             | _value
0     | 201ae4c5-3e90-4379-a680-ac0e852ef54a | 1
1     | 2022f5c1-9d22-4fea-8ba9-6164321ffcb1 | 4

Which shows that there are two unique clientID’s, with one having 1 data point, and the other having 4 data points. I haven’t quite wrapped my head around why they show as separate tables (0, and 1)

But from here, I want to boil this down so I can display that there are just 2 clientID’s (either to show as a single stat, or as a graph so I can visualize the unique client count over time.

My attempts have taken me a few directions, but the query below seems to be the closest:

from(bucket: "brawnfire")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> group(columns: ["clientID"])
  |> count(column: "_value")
  |> count()
  |> sum(column: "_value")

By adding a second count, each of the two rows gets a value of 1, but the sum() doesn’t appear to change anything (i.e. the result table looks exactly the same with/without the final sum() function call)

Any direction or advice here would be greatly appreciated as this doesn’t seem like something strange that I’m trying to do.

Thanks for reading!

2

@Bitdecay Each table in a stream of tables is defined by a group key. The group key is a list of columns for which all values for that column in the table are the same. So when you group by clientID, you have an individual table for each unique client ID. So to count the number of unique tag values (or clientIDs in this case), what you can do is group by that tag, then use an aggregate function to reduce each table to a single row (like you’ve already done with count()), then ungroup the tables (or group by nothing), and run the count() aggregate again. This will give you the number of unique tag values in your returned data.

In the example below, I use limit(n:1) to reduce the number of rows in each table to one because it’s less “expensive” than an aggregate function.

from(bucket: "brawnfire")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> group(columns: ["clientID"])
  |> limit(n:1)
  |> group()
  |> count()
2 Likes
3

@scott The grouping by nothing is definitely the magic sauce I was missing! I didn’t realize that you can ‘ungroup’ that way. Thank you, thank you. That definitely gets my queries functioning the way I need them to. And the limit:1 piece is a nice cherry on top.

Thank you!

4

No problem. Happy to help!