Purpose
This function calculates the cumulative distribution of a value in 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 of a value in a set. The return value is in the range (0, 1]. If there areNrows of data, and the value ofexpris greater than the value of the second row and less than the value of the third row, the percentage is2/N. The constant parameter expression and the expressions in theORDER BYclause of the aggregate function must match in position. Therefore, the number of parameters must be the same, and their types must be compatible. If a specified value is repeated in the grouping sequence, the two identical values are treated as one value. - As an analytic function,
PERCENT_RANKcalculates the percentage rank of each row in a column or a combination of columns. The return value is in the range [0, 1]. When there are duplicate sorted values, the same rank is assigned to the rows with the same values, and the number of rows with the same value is recorded in the next rank. ThePERCENT_RANKfunction for the first row in any set is0. The formula for calculating the percentage is: percentage = rank / maximum rank. For more information, see the following table:
| N | 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 | Specifies the value to query. |
| expr_col | Specifies the column name corresponding to the value to query. |
| DESC | ASC | Specifies the sorting order. This is an optional parameter.
|
| NULLS { FIRST | LAST } | Specifies the position of NULL values in expr_col after sorting. This is an optional parameter.
|
| OVER | Specifies the window for calculation using the OVER clause. For more information, see Analytic function description. |
Return type
The return type is NUMBER.
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
Example of an aggregate function
Return the percentage 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
Example of an analytic function
Group the data by the deptno column, sort the data in descending order of the sal column, and return the 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