GreenPlum的百分位数的实现
一、计算公式
二、实现思路
计算百分位数
1、确定数组
如下数组：
| 3710 |
3755 |
3850 |
3880 |
3880 |
3890 |
3920 |
3940 |
3950 |
4050 |
4130 |
4325 |
| 1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
2、确定数组在有序序列中的位置
3、根据公式，计算百分位数所在的位置
$$
Lp = (n +1)p/100
$$
公式描述：
p表示百分数
n表示数组的长度
4、根据公式，计算百分数
$$
R= Array[Floor(Lp)] + (Lp-Floor(Lp))*(Array[Ceil(Lp)]-Array[Floor(Lp)])
$$
公式描述：
Array[N] 表示数组中位置位N的元素
Floor[N] 表示向零取整
Ceil[N] 表示背零取整
Lp 表示百分数所在的位置
计算百分位
1、确定元素在数组中的位置（I）
2、数组的长度表示为，L
$$
R = 100I/（L+1）
$$
三、PSql去实现这个逻辑
计算百分位数
1、准备数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| CREATE TABLE "persent_table" (
"keyword" varchar(255) COLLATE "pg_catalog"."default",
"play_count" float8
);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('lol', 9.2);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('lol', 7.5);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('lol', 7.5);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('lol', 22);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('lol', 50.1);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('lol', 6.2);
INSERT INTO "persent_table" VALUES ('lol', 9.8);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('英雄联盟手游', 223.5);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('英雄联盟手游', 208.7);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('英雄联盟手游', 122.9);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('英雄联盟手游', 68.4);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('英雄联盟手游', 49.6);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('英雄联盟手游', 47.3);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('英雄联盟手游', 43);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('英雄联盟手游', 42.4);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('英雄联盟手游', 41.6);
INSERT INTO "persent_table"("keyword", "play_count") VALUES ('英雄联盟手游', 37);
|
2、确定数组和每一个数组的位置
将数据按照keyworld分组根据play_count的值从小到大排列
1
2
3
4
5
| SELECT
ROW_NUMBER() OVER ( PARTITION BY t1.keyword ORDER BY t1.play_count ASC ) AS seq,
*
FROM
persent_table t1
|
结果：
3、计算百分位数所在的位置，例子中使用50百分位
1
2
3
4
5
6
7
8
9
10
| SELECT
keyword,
0.5 * ( COUNT ( 1 ) + 1 ) AS number_index,
FLOOR (0.5 * ( COUNT ( 1 ) + 1 )) AS min_value,
CEIL (0.5 * ( COUNT ( 1 ) + 1 )) AS max_value
FROM
persent_table
WHERE play_count IS NOT NULL
GROUP BY
keyword
|
结果：
结果字段解释：
number_index: 表示50百分位所对应的数值所在数组中的位置
min_value/max_value:表示50百分位所对应的数值所在的实际数组元素所在的区间
如：
keyword为”lol”所对应的play_count的数组中50百分位数位置为4
keyword为“英雄联盟手游“所对应的play_count的数组中50百分位数位置为5和6之间
4、根据公式，计算百分数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| SELECT
t3.keyword,
(t2.number_index - FLOOR ( t2.number_index )) * (MAX ( t3.play_count ) - MIN ( t3.play_count )) + MIN ( t3.play_count ) AS persent_val
FROM
( SELECT ROW_NUMBER () OVER ( PARTITION BY t1.keyword ORDER BY t1.play_count ASC ) AS seq,* FROM persent_table t1 ) t3
INNER JOIN (
SELECT
keyword,
0.5 * ( COUNT ( 1 ) + 1 ) AS number_index,
FLOOR (0.5 * ( COUNT ( 1 ) + 1 )) AS min_value,
CEIL (0.5 * ( COUNT ( 1 ) + 1 )) AS max_value
FROM
persent_table
WHERE play_count IS NOT NULL
GROUP BY
keyword
) t2 ON t3.keyword = t2.keyword
AND ( seq = max_value OR seq = min_value )
GROUP BY
t3.keyword,
t2.number_index;
|
结果：
和步骤2中的数据对比：
lol的seq = 4的play_count = 9.2
英雄联盟手游 的 5< seq < 6 的play_count 值区间为：47.3 ～ 49.6 用公式验证 47.3 + 0.5*（49.6 - 47.3）= 48.45
计算百分位
1、求分组下的数组长度
1
2
3
4
5
6
7
8
9
| SELECT
keyword,
COUNT ( 1 ) AS count_val
FROM
persent_table
WHERE
play_count IS NOT NULL
GROUP BY
keyword
|
结果：
2、计算每个数组元素所在的位置的百分位
1
2
3
4
5
6
| SELECT
t1.keyword,
100 * (ROW_NUMBER () OVER ( PARTITION BY t1.keyword ORDER BY t1.play_count DESC )) / ( t2.count_val + 1 ) AS persent
FROM
persent_table t1
LEFT JOIN ( SELECT keyword, COUNT ( 1 ) AS count_val FROM persent_table WHERE play_count IS NOT NULL GROUP BY keyword ) t2 ON t1.keyword = t2.keyword
|
结果：
和计算百分位数进行对比：很明显发现lol的位置为4的百分位为50
