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

On Fri, Jul 10, 2009 at 11:37:46PM +0000, Richard Neill wrote:
>
> 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)

This really should go to the pgsql-general list, where I'm redirecting
follow-ups. :)

> 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.

You can get ntiles in 8.4 with the windowing functions. As to median,
it is very precisely defined...several different and incompatible
ways. Any ideas as to how to handle this?

> [mode would also be useful, as an explicit function, though we can get it
> easily enough using count(1) order by count desc].

You can get that with windowing functions, too. :)

> According to google, this has been a wish since at least year 2000
> for various people, but doesn't seem to be implemented.

Patches are welcome :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate