【PostgreSQL】函数之百分位数&中位数：percentile_cont()

搜索了很久，实在没找到pg内有关求百分数的内置函数，后来在stackoverflow中找到了创建percentile_cont函数的代码：



一、创建percentile_cont函数

• 创建array_sort () <用于数组排序>

CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL AS $$   -- 返回数组类型

-- array_lower()返回数组指定维度下界，array_upper()返回数组指定维度上界，generate_series(start, stop)生成一个数值序列，从start 到 stop
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
);
$$;

• 创建percentile_cont()

--real单精度浮点数
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); --array_length返回数组长度，1表示维度
  row_num = 1 + ( percentile * ( ary_cnt - 1 )); -- percent*行数
  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;

二、使用percentile_cont函数
使用array_agg对字段进行数组转换

select percentile_cont(array_agg(col), 0.5)) 
from (
    select 2 as col
    union all
    select 3 as col
    union all
    select 6 as col
) as a
结果：3