Oracle의 PERCENTILE_CONT 함수의 PostgreSQL 등가물

Oracle의 PERCENTILE_CONT 함수의 PostgreSQL 등가물

포스트그레를 발견한 사람이 있습니까?Oracle의 PERCENTILE_CONT 기능과 동등한 SQL?검색을 해봤지만, 찾을 수가 없어서 직접 작성했습니다.

여기 제가 당신에게 도움이 되기를 바라는 해결책이 있습니다.

제가 일하는 회사는 Java EE 웹 애플리케이션을 Oracle 데이터베이스를 사용하는 것에서 Postgre를 사용하는 것으로 마이그레이션하려고 했습니다.SQL. Oracle 고유의 PERCENTILE_CONT() 함수를 사용하여 저장하는 프로시저가 많이 의존했습니다.이 함수는 Postgre에 존재하지 않습니다.SQL 입니다.

PG로 기능을 "포트오버"한 사람이 있는지 검색해 보았지만 아무 소용이 없었습니다.

검색을 더 한 끝에 오라클이 이 기능을 구현하는 방법에 대한 의사 코드를 나열한 페이지를 발견했습니다.

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

저는 오라클의 기능을 모방하기 위해 PG 내에 저만의 기능을 쓰기로 결심했습니다.

David Fetter의 배열 정렬 기법을 찾은 곳은 다음과 같습니다.

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

그리고.

배열 요소 정렬

(명확히 하기 위해) 다윗의 코드는 다음과 같습니다.

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
);
$$;

여기 제가 작성한 함수가 있습니다.

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;

다음은 몇 가지 비교 테스트 결과입니다.

CREATE TABLE testdata
(
  intcolumn bigint,
  fltcolumn real
);

테스트 데이터는 다음과 같습니다.

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);

비교 결과는 다음과 같습니다.

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

저는 이것이 바퀴를 다시 만들 필요가 없음으로써 누군가에게 도움이 되길 바랍니다.

즐겨요! 레이 해리스!

Postgre와 함께SQL 9.4는 Ordered-Set Aggregate Functions에 구현된 백분위수를 기본적으로 지원합니다.

percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression) 

연속 백분위수: 순서에서 지정된 분율에 해당하는 값을 반환하며, 필요한 경우 인접한 입력 항목 간에 보간합니다.

percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)

다중 연속 백분위수: 각 비 null 요소가 해당 백분위수에 해당하는 값으로 대체된 상태에서 분수 파라미터 모양과 일치하는 결과 배열을 반환합니다.

자세한 내용은 문서를 참조하십시오. http://www.postgresql.org/docs/current/static/functions-aggregate.html

언급URL : https://stackoverflow.com/questions/14300004/postgresql-equivalent-of-oracles-percentile-cont-function