Thread: percentile_cont from array?
I find myself needing to get percentile_cont values from array types and thus did this, wondering if anyone has any "you idiot that's available out of the box here" kind of feedback?
create or replace function public.percentile_array(numeric, numeric[])
returns real
language 'sql'
immutable
as $$
with u as (
select unnest($2) as i
)
select percentile_cont($1) within group (order by i asc) from u;
$$;
alter function public.percentile_array owner to postgres;
select public.percentile_array(0.9, array[1,2,3,4,5]); -- 4.6
returns real
language 'sql'
immutable
as $$
with u as (
select unnest($2) as i
)
select percentile_cont($1) within group (order by i asc) from u;
$$;
alter function public.percentile_array owner to postgres;
select public.percentile_array(0.9, array[1,2,3,4,5]); -- 4.6
On Sep 11, 2023, at 4:01 PM, Wells Oliver <wells.oliver@gmail.com> wrote:I find myself needing to get percentile_cont values from array types and thus did this, wondering if anyone has any "you idiot that's available out of the box here" kind of feedback?
I think it is a personal preference; does the function make the code more readable? The CTE is not needed, nor is the function.
create or replace function percentile_array(numeric, numeric[])
returns real
language 'sql'
immutable
as $$
select percentile_cont($1) within group (order by i asc) from unnest($2) i;
$$;
select percentile_cont(.9) within group (order by i asc)
from unnest(array[1,2,3,4,5]) i
;