Hello
see http://okbob.blogspot.com/2009/11/aggregate-function-median-in-postgresql.html
Regards
Pavel Stehule
2010/11/16 maarten <maarten.foque@edchq.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
