PostgreSQL equivalent of Oracle's PERCENTILE_CONT function

Question

Has anyone found a PostgreSQL equivalent of Oracle's PERCENTILE_CONT function? I searched, and could not find one, so I wrote my own.

Here is the solution that I hope helps you out.

The company I work for wanted to migrate a Java EE web application from using an Oracle database over to using PostgreSQL. Several stored procedures relied heavily upon using Oracle's unique PERCENTILE_CONT() function. This function does not exist in PostgreSQL.

I tried searching to see if anyone had "ported over" that function into PG to no avail.

Solution 1

After more searching I found a page that listed the pseudo-code for how Oracle implements this function at :

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions110.htm

I determined to write my own function within PG to mimic Oracle's feature.

I found an array sorting technique by David Fetter at ::

http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#General_array_sort

and

Sorting array elements

Here (for clarity) is David's code:

CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(
    SELECT $1[s.i] AS "foo"
    FROM
        generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY foo
);
$$;

So here is the function I wrote :

CREATE OR REPLACE FUNCTION percentile_cont(myarray real[], percentile real)
RETURNS real AS
$$

DECLARE
  ary_cnt INTEGER;
  row_num real;
  crn real;
  frn real;
  calc_result real;
  new_array real[];
BEGIN
  ary_cnt = array_length(myarray,1);
  row_num = 1 + ( percentile * ( ary_cnt - 1 ));
  new_array = array_sort(myarray);

  crn = ceiling(row_num);
  frn = floor(row_num);

  if crn = frn and frn = row_num then
    calc_result = new_array[row_num];
  else
    calc_result = (crn - row_num) * new_array[frn] 
            + (row_num - frn) * new_array[crn];
  end if;

  RETURN calc_result;
END;
$$
  LANGUAGE 'plpgsql' IMMUTABLE;

Here are the results of some comparison testing:

CREATE TABLE testdata
(
  intcolumn bigint,
  fltcolumn real
);

Here is the test data :

insert into testdata(intcolumn, fltcolumn)  values  (5, 5.1345);
insert into testdata(intcolumn, fltcolumn)  values  (195, 195.1345);
insert into testdata(intcolumn, fltcolumn)  values  (1095, 1095.1345);
insert into testdata(intcolumn, fltcolumn)  values  (5995, 5995.1345);
insert into testdata(intcolumn, fltcolumn)  values  (15, 15.1345);
insert into testdata(intcolumn, fltcolumn)  values  (25, 25.1345);
insert into testdata(intcolumn, fltcolumn)  values  (495, 495.1345);
insert into testdata(intcolumn, fltcolumn)  values  (35, 35.1345);
insert into testdata(intcolumn, fltcolumn)  values  (695, 695.1345);
insert into testdata(intcolumn, fltcolumn)  values  (595, 595.1345);
insert into testdata(intcolumn, fltcolumn)  values  (35, 35.1345);
insert into testdata(intcolumn, fltcolumn)  values  (30195, 30195.1345);
insert into testdata(intcolumn, fltcolumn)  values  (165, 165.1345);
insert into testdata(intcolumn, fltcolumn)  values  (65, 65.1345);
insert into testdata(intcolumn, fltcolumn)  values  (955, 955.1345);
insert into testdata(intcolumn, fltcolumn)  values  (135, 135.1345);
insert into testdata(intcolumn, fltcolumn)  values  (19195, 19195.1345);
insert into testdata(intcolumn, fltcolumn)  values  (145, 145.1345);
insert into testdata(intcolumn, fltcolumn)  values  (85, 85.1345);
insert into testdata(intcolumn, fltcolumn)  values  (455, 455.1345);

Here are the comparison results :

ORACLE RESULTS
ORACLE RESULTS

select  percentile_cont(.25) within group (order by fltcolumn asc) myresult
from testdata;
select  percentile_cont(.75) within group (order by fltcolumn asc) myresult
from testdata;

myresult
- - - - - - - -
57.6345                

myresult
- - - - - - - -
760.1345               

POSTGRESQL RESULTS
POSTGRESQL RESULTS

select percentile_cont(array_agg(fltcolumn), 0.25) as myresult
from testdata;

select percentile_cont(array_agg(fltcolumn), 0.75) as myresult
from testdata;

myresult
real
57.6345

myresult
real
760.135

I hope this helps someone out by not having to reinvent the wheel.

Enjoy! Ray Harris

Solution 2

With PostgreSQL 9.4 there is native support for percentiles now, implemented in Ordered-Set Aggregate Functions:

percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression) 

continuous percentile: returns a value corresponding to the specified fraction in the ordering, interpolating between adjacent input items if needed

percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)

multiple continuous percentile: returns an array of results matching the shape of the fractions parameter, with each non-null element replaced by the value corresponding to that percentile

See the documentation for more details: http://www.postgresql.org/docs/current/static/functions-aggregate.html

Comments

[echo]

you can avoid the arroy_sort by using ORDER BY. It is actually a little faster than the array_sort.

[Max Candocia]

I used this function and updated my database. There's a function with the same name starting in 9.4, and there's a conflict between the two functions due to naming similarities and the way they're implemented. This happened after I migrated from 9.3 to 9.6.