Trying to get top 3 records on the basis of user and value?

InfluxDB 1
1

I have influx DB (time based) where I am trying to retrieve the data for 1 hour. I need to check how many Users logged in system for how many times in each hour by taking value as column.

Data has 3 columns- Time, user, and value

user is logging in at different time. so I am trying to aggregate the value of each user and want to take out top 3 user which has maximum value…

Time User value
12:00 PM user1 3
12:10 user 2 2
12:15 user 1 6
12:20 user 3 5
12:25 user 1 4
12:35 user 4 2
12:40 user 5 5
12:45 user 6 1
12:50 user 2 3
12:55 user 1 5
1:00 PM user 6 3

from(bucket: “testdata”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “xyz”
|> filter(fn: (r) => r[“loc”] == “ABC” or r[“loc”] == “DEF”)
|> filter(fn: (r) => r[“count_type”] == “items”)
|> group(columns: [“user”, “loc”])
|> sort(columns: [“_value”], desc: true)
|> aggregateWindow(every: 24h, fn:sum, createEmpty: false )
|> aggregateWindow(every: 1mo, fn: max)

I am getting resut but I am not getting top 3 result… Am i missing anything ? please let me know.

2

Hello @kanwalgujral,
Can you please share what you’re getting and what you expect?
I’m having a little bit of trouble understanding.
What do you mean by take out? Remove the top 3 from your result or only include top 3?

3

Hi @Anaisdg

Thankyou for getting back…
Actually this is the test data set I made myself which is similar to my original data.

I want to take out the data based on top 3 users who is logging in system more in last one hour…

With the above query I am getting all the users in the result but not on the basis of top 3 results. i want end result by aggregating total number of times user logged in, like maximum number of times.

For eg … user 1 logged in 18 times
user 2 logged in 5 times and user 3 did 5 times in last one hour …so I just want to see 3 result

with above query I am getting result of all the users 1,2,3,4,5,6 … without any sequence…

It’s just aggregating all the value for all the users

user 1 - 18
User 2 - 5
User 3 - 5
User 4 - 2
User 5 - 5
User 6 - 4

I just want it to show me just top 3 user who has higher aggregate value.

I hope it’s clear now ?

If not please ask me again