Re: user defined aggregate for percentile calculations

On Sun, 5 Apr 2009, Kashmir wrote:

> would it possible at all to create a percentile-aggregate in pgres?

I normally just do this right in the database without specifically
accelerating it with an aggregate.  Not very efficient but it works fine
for reasonably sized data sets that fit into the database cache.  Here's
an example that computes some statistics about temporary data in a table
named "timing" into a summary statistics table named tests:

update tests set trans=(select count(*) from timing);
update tests set
   avg_latency=(select avg(latency) from timing),
   max_latency=(select max(latency) from timing),
   percentile_90_latency=
     (select latency from timing
      order by latency offset (round(0.90*trans)) limit 1);

Even if that's not efficient enough for your final app, you might use that
sort of thing as a prototype until you get a better implementation, rather
than dropping into Perl.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD