PostgreSQL相当于Oracle的PERCENTILE_CONT函数 [英] PostgreSQL equivalent of Oracle's PERCENTILE_CONT function
问题描述
有没有人找到与Oracle的PERCENTILE_CONT函数相当的PostgreSQL？我搜索了，并找不到一个，所以我写了我自己的。
这是我希望能帮助你的解决方案。
我工作的公司希望将Java EE Web应用程序从使用Oracle数据库迁移到使用PostgreSQL。几个存储过程严重依赖于Oracle独特的PERCENTILE_CONT（）函数。这个函数在PostgreSQL中不存在。
我尝试搜索是否有人将该函数移植到PG中无法获得。
$ b $ http://docs.oracle.com/ cd / B19306_01 / server.102 / b14200 / functions110.htm
我决定在PG中编写我自己的函数来模拟Oracle的功能。
我发现了David Fetter的一个数组排序技巧:: b
$ b
http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#General_array_sort
和
这里（为了清楚起见）是David的代码：
CREATE OR REPLACE FUNCTION array_sort（ANYARRAY）
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY（
SELECT $ 1 [si] asfoo
FROM
generate_series（array_lower（$ 1,1），array_upper（$ 1,1））AS s（i）
ORDER BY foo
）;
$$;
所以这里是我写的函数：
创建或替换函数percentile_cont（myarray real []，百分点实数）
RETURNS实数AS
$$
DECLARE
ary_cnt INTEGER;
row_num real;
crn real;
frn real;
calc_result real;
new_array real [];
BEGIN
ry_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];
结束if;
RETURN calc_result;
END;
$$
LANGUAGE'plpgsql'IMMUTABLE;
以下是一些比较测试的结果：
CREATE TABLE testdata
（
intcolumn bigint，
fltcolumn real
）;
以下是测试数据：
insert into testdata（intcolumn，fltcolumn）values（5，5.1345）;
插入到testdata（intcolumn，fltcolumn）值（195,195.1345）;
插入到testdata（intcolumn，fltcolumn）值（1095,1095.1345）;
插入到testdata（intcolumn，fltcolumn）值（5995,5995.1345）;
插入到testdata（intcolumn，fltcolumn）值（15,15.1345）;
插入到testdata（intcolumn，fltcolumn）值（25,25.1345）;
插入到testdata（intcolumn，fltcolumn）值（495,495.1345）;
插入到testdata（intcolumn，fltcolumn）值（35，35.1345）;
插入到testdata（intcolumn，fltcolumn）值（695,695.1345）;
插入到testdata（intcolumn，fltcolumn）值（595,595.1345）;
插入testdata（intcolumn，fltcolumn）的值（35，35.1345）;
插入到testdata（intcolumn，fltcolumn）值（30195，30195.1345）;
插入到testdata（intcolumn，fltcolumn）值（165,165.1345）;
插入到testdata（intcolumn，fltcolumn）值（65，65.1345）;
插入到testdata（intcolumn，fltcolumn）值（955,955.1345）;
插入到testdata（intcolumn，fltcolumn）值（135,135.1345）;
插入到testdata（intcolumn，fltcolumn）值（19195,19195.1345）;
插入到testdata（intcolumn，fltcolumn）值（145,145.1345）;
插入到testdata（intcolumn，fltcolumn）值（85,85.1345）;
插入到testdata（intcolumn，fltcolumn）值（455,455.1345）;
以下是比较结果：
ORACLE结果
ORACLE结果
从组中选择percentile_cont（.25）（按fltcolumn asc排序）myresult
from testdata;
从组中选择percentile_cont（.75）（按fltcolumn asc排序）myresult
from testdata;
myresult
- - - - - - - -
57.6345
myresult
- - - - - - - -
760.1345
POSTGRESQL结果
POSTGRESQL结果
从testdata中选择percentile_cont（array_agg（fltcolumn），0.25）作为myresult
;
从testdata中选择percentile_cont（array_agg（fltcolumn），0.75）作为myresult
;
myresult
real
57.6345
myresult
real
760.135
我希望这可以帮助别人不必重新发明轮子。
享受！
Ray Harris
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.
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
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
这篇关于PostgreSQL相当于Oracle的PERCENTILE_CONT函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
