I have a table TableX looks like:

And I would like to process the median on column Value to obtain:

I'm trying to use the percentile_cont:

select name, percentile_cont(0.5) within group (order by name, value) from TableX

But obtained error:

ERROR:  function percentile_cont(numeric, character varying, numeric) does not exist

My postgresql version is 13 , and that the following line actually works.

SELECT percentile_cont(array(SELECT j/10.0 FROM generate_series(1,10) j)) WITHIN GROUP(ORDER BY i) AS median FROM generate_series(1,11) AS s(i);

Pretty sure my script is wrong... would appreciate your thoughts. Thanks!

CodePudding user response：

create table percent_test (name varchar, value float);

insert into percent_test values ('Bob', 100), ('Cathy', 500), ('Cathy', 200), ('Cathy', 100);

select name, percentile_cont(0.5) within group (order by value) from  percent_test group  by name;
 name  | percentile_cont 
------- -----------------
 Bob   |             100
 Cathy |             200

Per Aggregate Functions:

percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY double precision ) → double precision

percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY interval ) → interval

Computes the continuous percentile, a value corresponding to the specified fraction within the ordered set of aggregated argument values. This will interpolate between adjacent input items if needed.

So the order by in the within group can only be a numeric value not a character value. Push the name grouping out to the from part of the query.