Thread: Aggregate working on ordered data

Aggregate working on ordered data

From
Christoph Dalitz
Date:
Hello,

while trying to implement an aggregate function "median", I am
facing two problems:

 a) In order to compute the median, the input values of the aggregate
    function need to come in ordered.

 b) Computation of the statistical median requires *two* runs of
    the aggregation: a simple count and a run until count/2 on the
    ordered data.

Is it at all possible to implemnt such an aggregate function in PostgreSQL?
If yes, all suggestions are appreciated.

Thanks,

Christoph Dalitz

Re: Aggregate working on ordered data

From
"Peter Darley"
Date:
Christoph,
    Someone has probably answered you by now, but I'm kinda slow, so I'm adding
my 2 cents.
    Here is a Median function that was written by Stephen Dobson and updated a
bit by myself.  It's not terribly slow, but could probably be faster.  If
you make any improvements to it, please return it to the list.  It is
actually built of two functions that both need to exist.  The comments are
in a state if disrepair.
Thanks,
Peter Darley


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Christoph Dalitz
Sent: Thursday, December 19, 2002 1:41 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Aggregate working on ordered data


Hello,

while trying to implement an aggregate function "median", I am
facing two problems:

 a) In order to compute the median, the input values of the aggregate
    function need to come in ordered.

 b) Computation of the statistical median requires *two* runs of
    the aggregation: a simple count and a run until count/2 on the
    ordered data.

Is it at all possible to implemnt such an aggregate function in PostgreSQL?
If yes, all suggestions are appreciated.

Thanks,

Christoph Dalitz

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Attachment

Re: Aggregate working on ordered data

From
Christoph Dalitz
Date:
On Thu, 2 Jan 2003 12:15:27 -0800
"Peter Darley" <pdarley@kinesis-cem.com> wrote:
>
> Here is a Median function that was written by Stephen Dobson and updated a
> bit by myself.  It's not terribly slow, but could probably be faster.  If
> you make any improvements to it, please return it to the list.  It is
> actually built of two functions that both need to exist.  The comments are
> in a state if disrepair.
>
Thanks for the hint containing the median implementation given in the
"interactive docs". I have added my 2 Euros as a "doc note" on
http://www.postgresql.org/idocs/index.php?sql-createaggregate.html

Meanwhile I have written an own implementation using a "local temporary table"
that is filled in the "sfunc" and aggregated in the final function.

As I plan to assign this as an exercise to my students, I have not attached
the solution to this (public) email.
If you are interested in the function, please contact me directly so that I can
send it to you.

Christoph Dalitz