2009/7/11 Richard Neill <rn214@cam.ac.uk>:
>
> The following bug has been logged online:
>
> Bug reference: =C2=A0 =C2=A0 =C2=A04916
> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Richard Neill
> Email address: =C2=A0 =C2=A0 =C2=A0rn214@cam.ac.uk
> PostgreSQL version: 8.4
> Operating system: =C2=A0 Linux
> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0wish: more statistical functions =
(median, percentiles
> etc)
> Details:
>
> In addition to the existing aggregate functions (avg, stddev etc), =C2=A0=
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=3D# select nth_percentil(array(select length from film order by 1),9=
0);
nth_percentil
---------------
173
(1 row)
pagila=3D# 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=3D# 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=3D# 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
