Thread: Median, Quartile and Percentile Aggregate Functions

I am in need of median, quartile and percentile functions in postgres.

Has anybody implemented any of them allready?

I have the functions temporary solved in PHP right now but they are really
slow as datarows are increasing in count.

---
   Patrik Hall <phall@fkf.fi> - +358 50 500 4668 - ICQ# 13414838

Patrik Hall wrote:
>
> I am in need of median, quartile and percentile functions in postgres.
>
> Has anybody implemented any of them allready?
>
> I have the functions temporary solved in PHP right now but they are really
> slow as datarows are increasing in count.

I don't know much about php, but there is a perl stats module called
Statistics::Descriptive, available at CPAN, that has what you seek and
seems pretty quick (I haven't measured it).  I find that doing these
sorts of functions in perl tends overall to be faster than doing them
in postgres, though that would not be my intuition.

Cheers,
Ed Loehr

Patrik Hall writes:

> I am in need of median, quartile and percentile functions in postgres.
>
> Has anybody implemented any of them allready?

Haven't heard, but if you can come up with a way to calculate them with a
single pass over the data rows (keeping several state variables, if
necessary) then you're half way there to implementing your own aggregate
function. See the Programmer's Guide on how that might be done.

Incidentally I couldn't think of a way to do just that. In that case it
will be *hard*.

--
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Peter Eisentraut wrote:
>
> Patrik Hall writes:
>
> > I am in need of median, quartile and percentile functions in postgres.
> >
> > Has anybody implemented any of them allready?
>
> Haven't heard, but if you can come up with a way to calculate them with a
> single pass over the data rows (keeping several state variables, if
> necessary) then you're half way there to implementing your own aggregate
> function. See the Programmer's Guide on how that might be done.
>
> Incidentally I couldn't think of a way to do just that. In that case it
> will be *hard*.

Forgive me if I already said this...May I suggest a lovely little perl
package called Statistics::Descriptive which I use for this sort of
thing?  I know it's not exactly what your were looking for, but it's a
possible consideration if you happen to be accessing pgsql via
perl/DBI.

Cheers,
Ed Loehr

Ed Loehr writes:

> Forgive me if I already said this...May I suggest a lovely little perl
> package called Statistics::Descriptive which I use for this sort of
> thing?  I know it's not exactly what your were looking for, but it's a
> possible consideration if you happen to be accessing pgsql via
> perl/DBI.

For those of us that access databases via a variety of clients this isn't
exactly a very stylish option. It has always been my philosophy to let the
database drive the application and not the other way around. Others are
free to disagree.

--
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden