Re: median for postgresql 8.3

Hello

see http://okbob.blogspot.com/2009/11/aggregate-function-median-in-postgresql.html

Regards

Pavel Stehule

2010/11/16 maarten <maarten(dot)foque(at)edchq(dot)com>:
> Hello everyone,
>
> I was doing some analysis of data to find average delays between some
> timestamp values etc...
> When the number of rows the average is computed over is small, this can
> give distorted values.  So I've obviously added a count column to see if
> the average represents much data.
> However, I would also like to add the median value to give me a pretty
> good idea of whats happening even for smaller counts.
>
> I couldn't find such an aggregate function in the manual (version 8.3)
> and some websearching didn't uncover it either.
>
> I was thinking about
> SELECT max(id) FROM test ORDER BY id ASC LIMIT
>        (SELECT count(*)/2 FROM test)
>
> But two things are wrong with that:
> Limit can't use subqueries :(
> And ORDER BY gives me the error: 'must be used in aggregate function
> etc...) but I can probably work around this by using an ordered subquery
> in stead of the table directly.
>
> Furthermore, I need the median for a timestamp column, which would
> probably complicate things more than when it is a number column.
>
> I'd like to be able to do this using only the database. (So no
> programming functions, special addons etc...)
>
> Any ideas anyone?
>
> regards,
> Maarten
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>