Purpose
PERCENT_RANK() calculates the cumulative distribution of a value in a group of values. It is similar to the CUME_DIST function. You can use it as an aggregate or analytic function.
Note
- As an aggregate function, the
PERCENT_RANKfunction calculates the rank percentage of a number in a set. The return value is in the range of (0,1]. If you haveNrows of data and the value ofexpris greater than the value of the second row and less than the value of the third row, the rank percentage is equal to2/N. The constant argument expressions match the expressions in theORDER BYaggregate clause by position. Therefore, the number of arguments must be the same and their types must be compatible. If the specified value is duplicate with a value in the ordered group, the two identical values are treated as one value. - As an analytic function, the
PERCENT_RANKfunction calculates the rank percentage of a row in a column or a grouped column and then sorts the rows. The return value is in the range of (0,1]. Rows with the same PERCENT_RANK values receive the same rank and are considered tied rows. OceanBase Database adds the number of tied rows to the tied rank to calculate the next rank. The value of thePERCENT_RANKfunction is0for the first row in any set. The formula for calculating the rank percentage is: Rank percentage = Sequence number/Largest sequence number. The following table shows an example.
| N | No. | Rank percentage |
|---|---|---|
| A | 0 | 0 |
| B | 1 | 0.25 |
| C | 2 | 0.5 |
| D | 3 | 0.75 |
| E | 4 | 1 |
Syntax
/*Aggregate syntax*/
PERCENT_RANK(expr [, expr ...]) WITHIN GROUP
( ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]
[,expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]]...
)
/*Analytic syntax*/
PERCENT_RANK( ) OVER ([query_partition_clause] order_by_clause)
Parameters
| Parameter | Description |
|---|---|
| expr | The value to query. |
| expr_col | The column name of the value to query. |
| DESC | ASC | (Optional) The sorting method of the list.
|
| NULLS { FIRST | LAST } | (Optional) The position of NULL in expr_col after sorting.
|
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Return type
The return type is NUMBER.
Examples
The table emp_msg has been created.
obclient> SELECT * FROM emp_msg;
+--------+--------+------+------+
| DEPTNO | ENAME | SAL | MGR |
+--------+--------+------+------+
| 10 | CLARK | 2750 | 7839 |
| 10 | KING | 5300 | NULL |
| 10 | MILLER | 1600 | 7782 |
| 20 | ADAMS | 1400 | 7788 |
| 20 | FORD | 3300 | 7566 |
| 20 | JONES | 3275 | 7839 |
| 20 | SCOTT | 3300 | 7566 |
| 20 | SMITH | 1100 | 7902 |
| 30 | ALLEN | 1900 | 7698 |
| 30 | BLAKE | 3150 | 7839 |
| 30 | JAMES | 1250 | 7698 |
| 30 | MARTIN | 1550 | 7698 |
| 30 | TURNER | 1800 | 7698 |
| 30 | WARD | 1550 | 7698 |
| 30 | SCLARK | 1750 | 7839 |
+--------+--------+------+------+
15 rows in set
Example of an aggregate function
Return the rank percentage of 3300 in the sal column.
obclient> SELECT PERCENT_RANK(3300) WITHIN GROUP (ORDER BY sal) FROM emp_msg;
+-------------------------------------------+
| PERCENT_RANK(3300)WITHINGROUP(ORDERBYSAL) |
+-------------------------------------------+
| .8 |
+-------------------------------------------+
1 row in set
Example of an analytic function
Group the data records in the table by the deptno column and sort the data records by the sal column in descending order. Return the rank percentage of each value in the sal column.
obclient> SELECT deptno,ename,sal,
PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS pr1
FROM emp_msg;
+--------+--------+------+-------------------------------------------+
| DEPTNO | ENAME | SAL | PR1 |
+--------+--------+------+-------------------------------------------+
| 10 | KING | 5300 | 0 |
| 10 | CLARK | 2750 | .5 |
| 10 | MILLER | 1600 | 1 |
| 20 | FORD | 3300 | 0 |
| 20 | SCOTT | 3300 | 0 |
| 20 | JONES | 3275 | .5 |
| 20 | ADAMS | 1400 | .75 |
| 20 | SMITH | 1100 | 1 |
| 30 | BLAKE | 3150 | 0 |
| 30 | ALLEN | 1900 | .1666666666666666666666666666666666666667 |
| 30 | TURNER | 1800 | .3333333333333333333333333333333333333333 |
| 30 | SCLARK | 1750 | .5 |
| 30 | MARTIN | 1550 | .6666666666666666666666666666666666666667 |
| 30 | WARD | 1550 | .6666666666666666666666666666666666666667 |
| 30 | JAMES | 1250 | 1 |
+--------+--------+------+-------------------------------------------+
15 rows in set