Purpose
This function calculates the cumulative distribution of a value within a set of values. It is similar to the CUME_DIST function. You can use this function as an aggregate or analytic function.
Note
- As an aggregate function,
PERCENT_RANKcalculates the percentage position of a value within a set. The return value ranges from (0, 1]. ForNrows of data, if the value ofexpris greater than the value of the second row and less than the value of the third row, the percentage position is equal to2/N. The number of constant parameter expressions must be the same as the number of expressions in theORDER BYclause of the aggregate function, and their types must be compatible. If a specified value is repeated in a grouping sequence, the two identical values are treated as one value. - As an analytic function,
PERCENT_RANKcalculates the percentage rank of each row after sorting by one or more columns. The return value ranges from [0, 1]. When there are duplicate sorted values, they will have the same rank, and the number of rows with the same value will be recorded in the next rank. ThePERCENT_RANKfunction for the first row in any set is0. The formula for calculating the percentage position is: percentage position = rank / maximum rank. For example, see the table below:
| N | Rank | Percentage Position |
|---|---|---|
| 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 name of the column corresponding to the value to query. |
| DESC | ASC | The sorting order. This is an optional parameter.
|
| NULLS { FIRST | LAST } | The position of NULL values in the result set after sorting. This is an optional parameter.
|
| OVER | The window clause for defining the window for calculation. For more information, see Analytic Functions. |
Return type
The function returns a value of the NUMBER data type.
Examples
Assume that the emp_msg table 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
Aggregate function example
Return the percentage position of the value 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
Analytic function example
Group by the deptno column and sort the sal column in descending order, and return the percentage position 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