PERCENT_RANK |V3.2.4|OceanBase Database| docs|Distributed Database

PERCENT_RANK

Last Updated：2023-10-27 09:57:43 Updated

Purpose

You can call this function to calculate 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_RANK function calculates the rank percentage of a number in a set. The return value is in the range of (0,1]. If you have N rows of data and the value of expr is greater than the value of the second row and less than the value of the third row, the rank percentage is equal to 2/N. The constant argument expressions match the expressions in the ORDER BY aggregate 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_RANK function 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 the PERCENT_RANK function is 0 for 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	Sequence number	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. `ASC` specifies to sort in ascending order. It is the default value. `DESC` specifies to sort in descending order.
NULLS { FIRST LAST }	(Optional) The position of `NULL` in `expr_col` after sorting. `NULLS FIRST` indicates that nulls are sorted before non-null values. `NULLS LAST` indicates that nulls are sorted after non-null values.
OVER	You can use the `OVER` clause to define a window over the data on which the function operates. For more information about analytic functions, see Analytic functions.

Return types

This function returns data of the NUMBER data type.

Examples

Assume that you have created the table emp_msg.

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 example

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

Analytic example

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