Re: median for postgresql 8.3

2010/11/17 Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>:
> On 16 November 2010 17:37, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> Hello
>>
>> see http://okbob.blogspot.com/2009/11/aggregate-function-median-in-postgresql.html
>>
>
> An 8.3-compatible way of doing it is:
>
> SELECT CASE WHEN c % 2 = 0 AND c > 1 THEN (a[1]+a[2])/2 ELSE a[1] END
> FROM
> (
>  SELECT ARRAY(SELECT a FROM milrows ORDER BY a OFFSET (c-1)/2 LIMIT 2) AS a, c
>   FROM (SELECT count(*) AS c FROM milrows) AS count
>  OFFSET 0
> )
> AS midrows;
>

nice :)

Pavel

> In my tests this is faster than the analytic and array-based methods,
> but not by a huge amount.
>
> Regards,
> Dean
>
>
>
>> 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
>>>
>>
>> --
>> 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
>>
>