Hello
Is there any way to get the percentile of a...
# troubleshootingj
Jonathan Meyer
07/19/2021, 5:00 PMHello
Is there any way to get the percentile of an aggregation ?
Say, we aggregate in some way (per group), then get the percentile of these aggregated groups ?
It sounds a lot like a sub query, which isn't supported (yet), but maybe there's another way ?
x
Xiang Fu
07/19/2021, 5:19 PM
Xiang Fu
07/19/2021, 5:20 PM
Copy code
PERCENTILE(column, N)
Returns the Nth percentile of the group where N is a decimal number between 0 and 100 inclusive
PERCENTILE(playerScore, 50), PERCENTILE(playerScore, 99.9)
PERCENTILEEST(column, N)
Returns the Nth percentile of the group using Quantile Digest algorithm
PERCENTILEEST(playerScore, 50), PERCENTILEEST(playerScore, 99.9)
PercentileTDigest(column, N)
Returns the Nth percentile of the group using T-digest algorithm
PERCENTILETDIGEST(playerScore, 50), PERCENTILETDIGEST(playerScore, 99.9)j
Jonathan Meyer
07/19/2021, 5:22 PMThese aggregations works on raw data, what if data needs to be averaged before end for example ?
percentile("select avg(value), entity where ... group by entity", 0.1)x
Xiang Fu
07/19/2021, 5:24 PM
I see, i don’t think this is supported right now
j
Jonathan Meyer
07/19/2021, 5:42 PMWhat would be the next best thing ?
Given that cardinality is lowish (10000+ groups), perform percentile computation client-side ?
Any other option ?
m
Mayank
07/19/2021, 6:01 PM
For now, perhaps client side.
j
Jackie
07/19/2021, 6:12 PMYeah. In order to do that, we need the nested query support which supports execution over the query response of the inner query. Currently that is not supported, and we have this ticket tracking it: https://github.com/apache/incubator-pinot/issues/7043
✔️ 1
j
Jonathan Meyer
07/19/2021, 6:12 PMI'll try and benchmark that solution (client side), but in the meantime it looks like there's no other way
Thanks @Xiang Fu & @Mayank 😉