The Oracle PL/SQL PERCENT_RANK function is part of Analytic functions, and is used to calculate the relative rank of a row within a result set.
PERCENT_RANK function returns the rank of each row in a result set as a percentage of the total number of rows. The function takes two arguments: an expression and an optional ORDER BY clause. The expression is used to determine the ranking, while the ORDER BY clause specifies the order in which the rows are sorted.
Syntax
Here’s the syntax for using the PERCENT_RANK function in PL/SQL:
PERCENT_RANK([ORDER BY expression(s)]) WITHIN GROUP (ORDER BY expression(s))
The ORDER BY expression(s) is optional. If it’s not specified, the function will rank the rows based on the order they were retrieved from the table.
Example
Let’s take an example to understand the usage of PERCENT_RANK in PL/SQL. Suppose we have a table named “employees” with the following data:
| ID | Name | Salary |
|---|---|---|
| 1 | John | 5000 |
| 2 | Mike | 4000 |
| 3 | Sarah | 6000 |
| 4 | Kim | 7000 |
| 5 | Samantha | 5500 |
We can use the following query to find the PERCENT_RANK of each employee based on their salary:
SELECT Name, Salary, PERCENT_RANK() OVER (ORDER BY Salary) AS Percent_Rank FROM employees;
The output of the above query will be:
| Name | Salary | Percent_Rank |
|---|---|---|
| Mike | 4000 | 0.00 |
| John | 5000 | 0.25 |
| Samantha | 5500 | 0.50 |
| Sarah | 6000 | 0.75 |
| Kim | 7000 | 1.00 |
As you can see from the output, the PERCENT_RANK function has calculated the relative rank of each employee based on their salary. Mike has the lowest salary, so his percent rank is 0.00. Kim has the highest salary, so her percent rank is 1.00. The other employees have percent ranks between 0.25 and 0.75.
In conclusion, the PERCENT_RANK function in PL/SQL is a useful tool for calculating the relative rank of rows within a result set. It can be used to sort data based on a specific expression and provide a percentage value that represents the row’s rank within the result set.