PostgreSQL 相当于 Oracle 的 PERCENTILE_CONT 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14300004/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL equivalent of Oracle's PERCENTILE_CONT function
提问by thatdevguy
Has anyone found a PostgreSQL equivalent of Oracle's PERCENTILE_CONT function? I searched, and could not find one, so I wrote my own.
有没有人发现 PostgreSQL 相当于 Oracle 的 PERCENTILE_CONT 函数？我搜索了，没有找到，所以我自己写了一个。
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.
我工作的公司想要将 Java EE Web 应用程序从使用 Oracle 数据库迁移到使用 PostgreSQL。几个存储过程严重依赖于使用 Oracle 独特的 PERCENTILE_CONT() 函数。这个函数在 PostgreSQL 中不存在。
I tried searching to see if anyone had "ported over" that function into PG to no avail.
我尝试搜索以查看是否有人将该功能“移植”到 PG 中，但无济于事。
回答by thatdevguy
After more searching I found a page that listed the pseudo-code for how Oracle implements this function at :
经过更多搜索，我找到了一个页面，其中列出了 Oracle 如何实现此功能的伪代码：
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions110.htm
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.
我决定在 PG 中编写自己的函数来模仿 Oracle 的特性。
I found an array sorting technique by David Fetter at ::
我在以下位置找到了 David Fetter 的数组排序技术：
http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#General_array_sort
http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#General_array_sort
and
和
Here (for clarity) is David's code:
这里（为了清楚起见）是大卫的代码：
CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT [s.i] AS "foo"
FROM
generate_series(array_lower(,1), array_upper(,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
享受！雷·哈里斯
回答by alfonx
With PostgreSQL 9.4 there is native support for percentiles now, implemented in Ordered-Set Aggregate Functions:
PostgreSQL 9.4 现在有对百分位数的原生支持，在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
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)连续百分位数：返回与排序中指定分数相对应的值，如果需要，在相邻输入项之间进行插值
percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)多个连续百分位数：返回与分数参数形状匹配的结果数组，每个非空元素替换为对应于该百分位数的值
See the documentation for more details: http://www.postgresql.org/docs/current/static/functions-aggregate.html
有关更多详细信息，请参阅文档：http: //www.postgresql.org/docs/current/static/functions-aggregate.html
