Thread: 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
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
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