Re: BUG #4916: wish: more statistical functions (median, percentiles etc)

2009/7/11 Richard Neill <rn214(at)cam(dot)ac(dot)uk>:
>
> The following bug has been logged online:
>
> Bug reference:      4916
> Logged by:          Richard Neill
> Email address:      rn214(at)cam(dot)ac(dot)uk
> PostgreSQL version: 8.4
> Operating system:   Linux
> Description:        wish: more statistical functions (median, percentiles
> etc)
> Details:
>
> In addition to the existing aggregate functions (avg, stddev etc),  it would
> be nice if postgres could return further information. For example, the
> quartiles, percentiles, and median.
>
> [mode would also be useful, as an explicit function, though we can get it
> easily enough using count(1) order by count desc].
>
> According to google, this has been a wish since at least year 2000 for
> various people, but doesn't seem to be implemented.
>
> Thanks - Richard

hello

you can use following tricks:

create or replace function nth_percentil(anyarray, int)
returns anyelement as $$
select $1[$2/100.0 * array_upper($1,1) + 1];
$$ language sql immutable strict;

pagila=# select nth_percentil(array(select length from film order by 1),90);
nth_percentil
---------------
173
(1 row)

pagila=# select count(case when length < 173 then 1 end)::float /
count(*) * 100.0
from film;
?column?
----------
89.6
(1 row)

create or replace function median(anyarray)
returns float as $$
select ($1[round(array_upper($1,1)/2.0)] + $1[array_upper($1,1) -
round(array_upper($1,1)/2.0) + 1]) / 2.0::float;
$$ language sql immutable strict;

pagila=# select median(array[1,2]), median(array[1,2,3]),
median(array[1,2,3,4]);
median | median | median
--------+--------+--------
1.5 | 2 | 2.5
(1 row)

pagila=# select median(array(select length from film order by 1));
median
--------
114
(1 row)

tested on pagila database

regards
Pavel Stehule
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>