The Vertica Forum is getting a makeover! The site will be undergoing maintenance from Tuesday 8/13 to Friday 8/16. We appreciate your patience and cooperation during this time.

Median as aggregation function

Hi, we need to compute median as an aggregation function, e.g. for each year we need median of values which belong to that year. Unfortunately Vertica appears to only have median as an analytic (window) function: https://my.vertica.com/docs/6.1.x/HTML/index.htm#13281.htm. This doesn't aggregate values, only shows a median value for each row in a particular group. Why is it so? For instance SUM, MAX, AVG have both aggregation and analytic variants. So what's preventing median to have also its aggregation variant? Also, is there any useful workaround? I can think of wrapping the median calculation in a subselect like this: SELECT customer_state, MAX( MEDIAN ) FROM ( SELECT customer_state MEDIAN(annual_income) MEDIAN OVER (PARTITION BY customer_state) FROM customer_dimension ) GROUP BY 1; But that is unneccessarily ineffective I guess.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.