南大通用GBase 8a窗口函数percent_rank使用样例

本文介绍GBase 8a的窗口函数percent_rank, 其与Rank 基本一样,除了返回的是相对的百分比,而不是排名。

参考

GBase 8a窗口函数使用样例索引
GBase 8a窗口函数Rank和Dense_rank使用样例

语法

PERCENT_RANK() OVER([PARTITION BY col_name1,col_name2,…] ORDER BY col_name1 [ASC/DESC], col_name2 [ASC/DESC],…)

说明

计算由 ORDER BY 子句定义,在返回的查询中某一行相对于其它行的(小数)位置。它返回介于 0 和 1 之间的小数值。
该函数的使用场景及使用限制与 RANK()函数完全相同。

样例

无PARTITION的percent_rank

RANK返回的是位置顺序号,而percent_rank返回的是0-1之间的小数位置。

select t2.*,rank() over(order by val) v from t2;select t2.*,percent_rank() over(order by val) v from t2;
+------+------+------+---+
| id | type | val | v |
+------+------+------+---+
| 1 | 1 | 111 | 1 |
| 2 | 1 | 122 | 2 |
| 3 | 1 | 133 | 3 |
| 4 | 2 | 222 | 4 |
| 5 | 2 | 233 | 5 |
| 6 | 2 | 244 | 6 |
| 7 | 3 | 333 | 7 |
+------+------+------+---+
7 rows in set (Elapsed: 00:00:00.01)
+------+------+------+-------------------+
| id | type | val | v |
+------+------+------+-------------------+
| 1 | 1 | 111 | 0 |
| 2 | 1 | 122 | 0.166666666666667 |
| 3 | 1 | 133 | 0.333333333333333 |
| 4 | 2 | 222 | 0.5 |
| 5 | 2 | 233 | 0.666666666666667 |
| 6 | 2 | 244 | 0.833333333333333 |
| 7 | 3 | 333 | 1 |
+------+------+------+-------------------+
7 rows in set (Elapsed: 00:00:00.03)

带PARTITION的percent_rank

每个分组内,返回相对位置的0-1之间的小数位置

select t2.*,rank() over(partition by type order by val) v from t2;select t2.*,percent_rank() over(partition by type order by val) v from t2;
+------+------+------+---+
| id | type | val | v |
+------+------+------+---+
| 4 | 2 | 222 | 1 |
| 5 | 2 | 233 | 2 |
| 6 | 2 | 244 | 3 |
| 1 | 1 | 111 | 1 |
| 2 | 1 | 122 | 2 |
| 3 | 1 | 133 | 3 |
| 7 | 3 | 333 | 1 |
+------+------+------+---+
7 rows in set (Elapsed: 00:00:00.05)
+------+------+------+-----+
| id | type | val | v |
+------+------+------+-----+
| 4 | 2 | 222 | 0 |
| 5 | 2 | 233 | 0.5 |
| 6 | 2 | 244 | 1 |
| 1 | 1 | 111 | 0 |
| 2 | 1 | 122 | 0.5 |
| 3 | 1 | 133 | 1 |
| 7 | 3 | 333 | 0 |
+------+------+------+-----+
7 rows in set (Elapsed: 00:00:00.05)