Re: finding medians

Josh Burdick <jburdick(at)gradient(dot)cis(dot)upenn(dot)edu> writes:
> illustrates the limitations of the current aggregate function setup,
> which works so nicely for avg() and stddev().
> I don't have any good solutions. I tried using a float4[] to store
> each element as it's added, but I couldn't get array updates working in
> PL/PgSQL, so that didn't help.
> Perhaps aggregate functions could be passed an array? Or a cursor,
> pointing at the first line? I'm not sure.

I don't think that would help. The real problem here is the amount of
internal storage needed. AFAIK there are no exact algorithms for finding
the median that require less than O(N) workspace for N input items.
Your "hack" with a temporary table is not a bad approach if you want to
work for large N.

There are algorithms out there for finding approximate medians using
limited workspace; it might be reasonable to transform one of these into
a Postgres aggregate function.

regards, tom lane