PostgreSQL aggregates with multiple parameters

Question

I tried to wrap my head around creating aggregates in PostgreSQL (8.4 or 9.1) that accept one or more parameter parameters.

An example would be to create a PL/R extension to calculate the pth quantile using 0 <= p <= 1 . This would look like quantile(x,p) and as part of the request:

 select category,quantile(x,0.25) from TABLE group by category order by category; 

Where is TABLE (category:text, x:float) .

Suggestions?

Answer 1

Hope this example helps. You need a function that takes (accumulator, aggregated-arguments) and returns a new accumulator value. Play with the code below and this should make you understand how it all fits together.

 BEGIN; CREATE FUNCTION sum_product_fn(int,int,int) RETURNS int AS $$ SELECT $1 + ($2 * $3); $$ LANGUAGE SQL; CREATE AGGREGATE sum_product(int, int) ( sfunc = sum_product_fn, stype = int, initcond = 0 ); SELECT sum(i) AS one, sum_product(i, 2) AS double, sum_product(i,3) AS triple FROM generate_series(1,3) i; ROLLBACK; 

This should give you something like:

  one | double | triple -----+--------+-------- 6 | 12 | 18 

Answer 2

This can be achieved using the ntile window function.

 -- To calculate flexible quantile ranges in postgresql, for example to calculate n equal -- frequency buckets for your data for use in a visualisation (such as binning for a -- choropleth map), you can use the following SQL: -- this functions returns 6 equal frequency bucket ranges for my_column. SELECT ntile, avg(my_column) AS avgAmount, max(my_column) AS maxAmount, min(my_column) AS minAmount FROM (SELECT my_column, ntile(6) OVER (ORDER BY my_column) AS ntile FROM my_table) x GROUP BY ntile ORDER BY ntile 

More information about the ntile () function can be found in the window http://database-programmer.blogspot.com/2010/11/really-cool-ntile-window-function.html