Weighted Add

[rodhoward]

I'm not sure if this is possible or not but I have account session data that we're storing in a massive table. Data like mac address, bytes downloaded, uploaded, session duration etc.

I'd like to report on that data in a Top users (mac address) report. E.G top 10 users by downloads. or top 10 users by session duration. Is there a way to do that.. using this extension it seems like this is what its for?

I think I can do a top 10 users by session count.. as that is just the frequency of the mac address in the account sessions table.. however that is the only one of the reports (order by's) that I need. My idea was to have multiple topN columns in a summary table and then lookup those mac addresses in the main table once I have the list of top 10 to narow down my query. e.g. topUsersBySessionCount topUsersByDuration topUsersByDownloads etc.

Can you confirm if this is possible or not using TopN..

Regards
Rod

[heydardsm]

Rod Howard (@rodhoward)
You can use this instead:
https://github.com/heydardsm/topn

[rodhoward]

Thanks! It does fit the bill but I'm a little worried about the performance. It's not a statistical algorithm and it doesn't seem to support merging (for example I group my data by hotspot and based on the report I need to group different hotspots together). I haven't given it a good go though I've just looked at the documentation please correct me if I'm wrong.

[heydardsm]

Rod Howard (@rodhoward)

It does fit the bill but I'm a little worried about the performance.

For performance, we need to test always. It doesn't matter what have been said in the doc. :))))

It's not a statistical algorithm

Could you please explain a bit more ? it won’t give an estimate, if that’s what you’re asking. I tested it for millions of data, and it worked efficiently. It's been used at my current company on prod as well. to provide leaderboards of our games. Like top scores players based on some criteria. However we can make it even faster by adding a conditional indexes.
For example if you're sure that your top10 data is always greater that 10,000, we can create an index with a condition to keep rows with frequently higher than 10,000. This would decrease the size of index and increase the efficiently. I would consider this. Probably gonna create a function to simplify it.

it doesn't seem to support merging

It doesn't. It's V1.
Could you please do me a favor and open an issue there and explain what exactly you need. You can add some data samples. I'll consider it for sure.

[rodhoward]

It's my understanding that topn uses a fixed size "sketch". Then when you add a new item it checks if it exists and either adds or updates the value. If it's starting to run out of space, it will evict the smalles value to maintain the fixed size. Merging the sketches is efficient merging is super important for me as these sketches will be stored in time buckets e.g. user sessions for the day, and each of those time buckets are split over different hotspots so when creating a report on a group of hotspots I need to merge these sketches and report e.g. the top user. It seems possible that the value in the sketch could be something other than a count but they don't seem to support it ATM hence my question.

[heydardsm]

Rod Howard (@rodhoward)
Yeah, you got it right. That's exactly how topn works. fixed size, evicts the smallest.
One catch though: if your sketch already has some really big numbers, new data might struggle to get in. Even if it eventually becomes important.

[heydardsm]

Btw, if you would like, I can implement what you need in my project:
https://github.com/heydardsm/topn
Just open an issue, explain what you need, providing some sample data and some sample output would help a lot.