The PERCENT_RANK function is similar to the CUME_DIST (cumulative distribution) function. The return value ranges from 0 to 1. The PERCENT_RANK function of the first row in a set is 0. The return value is NUMBER.
Syntax
PERCENT_RANK( ) OVER ([query_partition_clause] order_by_clause)
Parameters
| Parameter | Description |
|---|---|
| OVER | Uses the OVER clause to define a window for calculation. |
Return type
The numeric data type is returned.
Examples
Categorize students to four levels based on scores to determine the awards for the students. To create the course_rank table and insert data into the table, execute the following statements:
CREATE TABLE course_rank
(
name VARCHAR(8),
id NUMBER
);
INSERT INTO course_rank VALUES('Linda',1);
INSERT INTO course_rank VALUES('Tan',2);
INSERT INTO course_rank VALUES('Tom',3);
INSERT INTO course_rank VALUES('John',4);
INSERT INTO course_rank VALUES('Mery',5);
COMMIT;
Execute the following statement:
SELECT name, id ,percent_rank() OVER (ORDER BY id) AS pr1 FROM course_rank;
The following query result is returned:
+-------+------+------+
| NAME | ID | PR1 |
+-------+------+------+
| Linda | 1 | 0 |
| Tan | 2 | .25 |
| Tom | 3 | .5 |
| John | 4 | .75 |
| Mery | 5 | 1 |
+-------+------+------+