Support limiting sub-groups to top N

[kkrugler]

In Elasticsearch we can do a group by gender, and then sub-group in each gender by state, and sum a metric, and get the top 10 results (for that sum of that metric) per state.

There doesn't seem to be a way to currently do that in Pinot, though @kishoreg suggested it would be possible via a UDF (let's call it "subBuckets"), something like select <top bucket column>, subBuckets(<aggregation operation>, <sub-bucket column>, <max_values>) from <table> group by <top bucket column>. For example:

select gender, subBuckets(sum(billed_expenses), state, 10) from medicare_expenses group by gender

[kishoreg]

will be better to not have 'sum(billed_expenses)' in quotes but at a high level this looks good

[kkrugler]

Thanks, updated.

[xiangfu0]

Can you also put the expected output?
I suspect it could be:

["male", [[800, "NY"], ..., [200, "IL"]]],
["female", [[1000, "NY"], ..., [300, "IL"]]]

[kkrugler]

@fx19880617 - good question re output format. With Elasticsearch it's a nested JSON response. I don't know what the standard is for Pinot, the above seems reasonable. You get additional details in the Elastic response for things like maximum error (due to how the top results from each shard get combined, and that could result in not actually getting the top N), etc.

[xiangfu0]

@fx19880617 - good question re output format. With Elasticsearch it's a nested JSON response. I don't know what the standard is for Pinot, the above seems reasonable. You get additional details in the Elastic response for things like maximum error (due to how the top results from each shard get combined, and that could result in not actually getting the top N), etc.

I see.

The output of subBuckets(sum(billed_expenses), state, 10) can be a map(JSON blob) if we need more info or stats per group.

Also not sure if this is the case, but maybe we can just define the order by clause in the function and have syntactic sugar for TOP_N queries.

subBuckets(<aggregation operation>, <sub-bucket column>, <order-by operation>, <max_values>)

One example could be that I want to show metrics for 10 same states ordered alphabetically in each gender.

[kishoreg]

its almost feels like its an nested query. in other words - it is a window udf