percentile

功能描述

percentile算子支持时序表/非时序表的分位数计算，用于返回数据排序后位于第N个百分位数的值。

在排序的基础上percentile_cont提供了按照分位数确定最近邻元素，并按照分位数比率插值输出的功能。percentile_disc则不进行插值，直接输出原始数据。

例如：

CREATE TIMESERIES TABLE weather(time timestamp TSTIME, city text TSTAG,province text TSTAG, temperature float) ;

有 5 条时序数据，如下所示：

time	00:00:00	00:01:00	00:15:00	00:36:00	00:45:00
value	10.5	20.2	30.7	40.1	50.9

已有的取值分别对应0、0.25、0.5、0.75与1的分位数。

现计算各分位数的取值，计算结果如下：

使用插值时，使用聚合函数名：percentile_cont

Percentile_cont	0	0.5	0.8	1
Value	10.5	30.7	42.26	50.9

不使用插值时，使用聚合函数名：percentile_disc

Percentile_disc	0	0.5	0.8	1
Value	10.5	30.7	40.1	50.9

这两个算子的差异如下：

percentile_disc为不连续的百分位：返回第一个在排序位置中等于或超过指定分数的输入值。
percentile_cont为连续的百分位：返回一个值对应于排序中指定的分数，如果需要，插入到临近的输入项之间。

在上述示例中，当分位数为0.8时，不存在与之对应的原始值，使用percentile_cont需要对结果进行插值，插值方式应与PostgreSQL一致。

插值算法：

(hi-lo)*((row_num-1)*percentile – floor((row_num-1)*percentile))

上述示例中，hi=50.9，lo=40.1，row_num=5，percentile=0.8，故插值结果为10.8*0.2+40.1=42.26。

使用percentile_disc时不会插值，而是跳过指定行数据后返回第一个值：

skip_row_num = ceil(row_num * percentile) - 1

在该计算值大于1的情况下才会跳过排序后的行，以满足在percentile=0下不做跳过返回第一个值。

percentile_disc和percentile_cont均支持ARRAY作为输入，单次处理多个百分位并返回数组结果。该功能相较于多次执行percentile算子，仅需要一次排序就可以得到所有输出，既方便用户使用又减少了重复扫描排序的资源消耗。

语法格式

-- 插值
percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY double precision ) → double precision
percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY interval ) → interval

-- 插值ARRAY
percentile_cont ( fractions double precision[] ) WITHIN GROUP ( ORDER BY double precision ) → double precision[]
percentile_cont ( fractions double precision[] ) WITHIN GROUP ( ORDER BY interval ) → interval[]

-- 非插值
percentile_disc ( fraction double precision ) WITHIN GROUP ( ORDER BY anyelement ) → anyelement

-- 非插值ARRAY
percentile_disc ( fractions double precision[] ) WITHIN GROUP ( ORDER BY anyelement ) → anyarray

参数说明

参数名	类型	属性	释义
fractions	FLOAT8OID	必选	分位数的数值表示，如：0.5表示中位数
sort_expression	FLOAT8OID 或 INTERVALOID	必选	需要排序并获取分位数的列，对于需要插值场景列类型仅支持数值和时间间隔。
sort_expression	Anyelement	必选	需要排序并获取分位数的列，对于不需要插值的场景，列类型支持timestamp、timestamptz、date、time、timetz、numeric、float4、float8。

约束

需要插值的percentile_cont仅支持可排序的Float类型与Interval类型。
不需要插值的percentile_disc支持可排序的类型，如：timestamp、timestamptz、date、time、timetz、numeric、float4、float8。
顺序聚合函数需要指定WITHIN GROUP (ORDER BY sort_expression)。

示例

Uqbar=# SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY value) AS median
FROM float_data;
 median
--------
   30.7
(1 row)

Uqbar=# SELECT percentile_cont(0.8) WITHIN GROUP (ORDER BY value)
FROM float_data;
 percentile_cont
-----------------
           42.26
(1 row)

Uqbar=# SELECT percentile_disc(1.5) WITHIN GROUP (ORDER BY value) FROM float_data;
ERROR:  percentile value 1.5 is not between 0 and 1

Uqbar=# SELECT percentile_disc(-1) WITHIN GROUP (ORDER BY value) FROM float_data;
ERROR:  percentile value -1 is not between 0 and 1

Uqbar=# SELECT percentile_cont(ARRAY[0,0.5,0.8,1]) WITHIN GROUP (ORDER BY duration) AS median FROM interval_data;
                         median
--------------------------------------------------------
 {"1 day 06:00:00","2 days","2 days 09:36:00","3 days"}
(1 row)

Uqbar=# SELECT percentile_disc(ARRAY[0,0.5,0.8,1]) WITHIN GROUP (ORDER BY duration) AS median FROM interval_data;
                         median
--------------------------------------------------------
 {"1 day 06:00:00","2 days","2 days 06:00:00","3 days"}
(1 row)