本文介绍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) |
Post Views: 846