https://pinot.apache.org/ logo
#general
Title
# general
t

troywinter

05/26/2021, 3:59 AM
Can I use an aggregation result as argument to a UDF during query?
x

Xiang Fu

05/26/2021, 5:27 AM
I think only having clause is supported, udf on aggregation results is not supported afaik
t

troywinter

05/26/2021, 5:29 AM
The problem is we have sum(a)/sum(b) doing some rate calculation, but sum(b) can be 0, then the result will be NaN, when doing an order by, NaN will be ranked first, I want to fill NaN to 0 in this case.
j

Jackie

05/26/2021, 6:34 AM
Can you try adding
having sum(b) != 0
and see if it solves the problem?
t

troywinter

05/26/2021, 6:37 AM
adding having will filter the result, I actually want to fill NaN with 0.
j

Jackie

05/26/2021, 6:37 AM
You can also plug in your own UDF to replace
NaN
with
0
. Udf on aggregation results is supported (post-aggregation), e.g.
sum(a)/sum(b)
Basically you need
replaceNaN(sum(a)/sum(b))
and plug in
replaceNaN(double value)
t

troywinter

05/26/2021, 6:38 AM
So a scalar UDF will work on post-aggregation, but groovy UDF will not work on post-aggregation?
I see, I will try implement a scalar UDF.
Thanks
j

Jackie

05/26/2021, 6:41 AM
Yes, groovy can only be used for record transform, but not post-aggregation as of now
Can you please file an issue about this? It should not be too hard to add a scalar function for groovy. Contributions are very welcome
t

troywinter

05/26/2021, 6:48 AM
OK, I will file an issue for this.