GBase 8a集群百分位函数percentile_cont和percentile_disc介绍

百分位函数，指一组数据中特定百分比位置的值，最典型的就是中位数(50%)。其中percentile_cont为连续百分位，如果指定位置没有值，则会根据前后位置的值线性计算值，也就是返回值不一定是原来存在的值。percentile_disc则为不连续百分位，如果指定位置没有值，则会根据百分位计算一个最近的位置，并返回该值。本文介绍GBase 8a里这些函数的语法和使用样例。

环境

版本

9.5.3.28.18

数据

gbase> select version();
+-------------------------------+
| version()                     |
+-------------------------------+
| 9.5.3.28.18.r1_patch.650ab97f |
+-------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select name,id from t1 order by name,id;
+-------+------+
| name  | id   |
+-------+------+
| Name0 |    3 |
| Name0 |    6 |
| Name0 |    9 |
| Name1 |    1 |
| Name1 |    4 |
| Name1 |    7 |
| Name1 |   10 |
| Name2 |    2 |
| Name2 |    5 |
| Name2 |    8 |
+-------+------+
10 rows in set (Elapsed: 00:00:00.03)

percentile_cont

语法

PERCENTILE_CONT ( numeric_literal )
    WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
    OVER ( [ <partition_by_clause> ] )

参数

numeric_literal

要计算的百分位数。 该值必须介于和 1.0. 之间0.0。

WITHIN GROUP(Order by)

指定要排序的数值列表，并计算百分位数。 仅允许一个 order_by_expression 。 默认的排序顺序为升序。

OVER （ <partition_by_clause> ）

将子句生成的 FROM 结果集划分为应用百分位函数的分区。

执行样例

gbase> select name,id,percentile_cont(0.5)within  group(order by id ) over(partition by name)contId from t1 order by name,id;
+-------+------+--------+
| name  | id   | contId |
+-------+------+--------+
| Name0 |    3 |      6 |
| Name0 |    6 |      6 |
| Name0 |    9 |      6 |
| Name1 |    1 |    5.5 |
| Name1 |    4 |    5.5 |
| Name1 |    7 |    5.5 |
| Name1 |   10 |    5.5 |
| Name2 |    2 |      5 |
| Name2 |    5 |      5 |
| Name2 |    8 |      5 |
+-------+------+--------+
10 rows in set (Elapsed: 00:00:00.08)

备注

当位置命中时，比如Name0，一共3个值(对应0，0.5，1)，0.5正好对应第二个，返回6

当位置未命中时，比如Name1，一共4个值（0，1/3,2/3,1)，0.5在1/3和2/3之间，做线性内插计算(V=值,P=位置)

V1+(V2-V1)/((P-P1)/(P2-P1))

4+（7-4)/((0.5-1/3)/(2/3-1/3)) = 5.5

• 2/3相比1/3的百分位差距为2/3-1/3=1/3
• 0.5相比1/3的百分位差距为0.5-1/3=1/6
• 0,5的位置占差距位置的百分比 1/6 / 1/3 = 1/2, 也就是1/3和2/3的中间
• 1/3和2/3对应的值差距为 7-4=3
• 0.5位置对应的插值为 3*1/2=1.5
• 加上基数1/3的值4， 4+1.5=5.5

percentile_disc

语法

PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )  
    OVER ( [ <partition_by_clause> ] ) 

参数

numeric_literal

要计算的百分位数。 该值必须介于 0.0 和 1.0 之间。

WITHIN GROUP(Order by)

指定要排序的数值列表，并计算百分位数。 仅允许一个 order_by_expression 。 默认的排序顺序为升序。

OVER （ <partition_by_clause> ）

将子句生成的 FROM 结果集划分为应用百分位函数的分区。

执行样例

gbase> select name,id,percentile_disc(0.5)within  group(order by id ) over(partition by name)discId from t1 order by name,id;
+-------+------+--------+
| name  | id   | discId |
+-------+------+--------+
| Name0 |    3 |      6 |
| Name0 |    6 |      6 |
| Name0 |    9 |      6 |
| Name1 |    1 |      4 |
| Name1 |    4 |      4 |
| Name1 |    7 |      4 |
| Name1 |   10 |      4 |
| Name2 |    2 |      5 |
| Name2 |    5 |      5 |
| Name2 |    8 |      5 |
+-------+------+--------+
10 rows in set (Elapsed: 00:00:00.08)

备注

当位置命中时，比如Name0，一共3个值(对应0，0.5，1)，0.5正好对应第二个，返回6

当位置未命中时，比如Name1，一共4个值（0，1/3,2/3,1)，0.5与前后位置的距离1/3和2/3都是1/6相同，选择前面的。返回4

1/2-1/3=1/6
2/3-1/2=1/6

位置稍微靠后一点，比如0.500000001，则会因为离2/3更仅，返回其位置值7

gbase> select name,id,percentile_disc(0.500000001)within  group(order by id ) over(partition by name)discId from t1 order by name,id;
+-------+------+--------+
| name  | id   | discId |
+-------+------+--------+
| Name0 |    3 |      6 |
| Name0 |    6 |      6 |
| Name0 |    9 |      6 |
| Name1 |    1 |      7 |
| Name1 |    4 |      7 |
| Name1 |    7 |      7 |
| Name1 |   10 |      7 |
| Name2 |    2 |      5 |
| Name2 |    5 |      5 |
| Name2 |    8 |      5 |
+-------+------+--------+
10 rows in set (Elapsed: 00:00:00.09)