Calculate median on different grouping sets in Vertica
I'm trying to calculate median on different grouping sets in Vertica and I get the following error:
[0A000] [Vertica]VJDBC ERROR: User defined aggregate cannot be used in query with MLAs"
this is a template of my query which doesn't work:
"""select field_a, field_b, APPROXIMATE_MEDIAN(field_c)
group by cube (field_a, field_b)"""
where in this one, when I group by just one field, it works just fine:
"""select field_a, APPROXIMATE_MEDIAN(field_c)
group by field_a"""
Would love to get any idea for why this is happening and how can I rewrite my query so it'll work. My next step is to union the calculations per every grouping, but I hope to find a more elegant solution.
tried to search for this error, tried to run on a smaller case, tried to think how can I get the same result using the analytic version of median, but couldn't find anything that supplies my need and can't find the logic behind why it does not work.