The PERCENT_RANK() function is a window function in SQL that calculates the relative rank of each row within a result set as a percentage. It provides a way to understand the distribution of row values in relation to the other rows in the dataset.
The formula for PERCENT_RANK() is:
PERCENT_RANK() = (RANK() - 1) / (N - 1)
Where:
- RANK() is the rank of the current row.
- N is the total number of rows in the result set.
The PERCENT_RANK() function returns a value ranging from 0 to 1, inclusive. The lowest rank returns 0, while the highest rank returns just below 1 (which translates to the percentage of rows below that rank).
Syntax
The syntax for using PERCENT_RANK() is as follows:
PERCENT_RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression)
- PARTITION BY (optional): Divides the result set into partitions to which the function is applied. If omitted, the entire result set is treated as a single partition.
- ORDER BY: Defines the logical order of the rows in each partition.
Example
Suppose you have a table named Employees with a column Salary. To calculate the percent rank of each employee's salary, you would write a query like this:
SELECT
EmployeeID,
Salary,
PERCENT_RANK() OVER (ORDER BY Salary) AS SalaryPercentRank
FROM
Employees;
Explanation of Example
1. SELECT Statement: You are selecting the EmployeeID, Salary, and the calculated SalaryPercentRank.
2. PERCENT_RANK(): The function calculates the percent rank for each employee based on their salary.
3. ORDER BY Salary: The rows are ordered by the Salary column to determine the ranking.
Result Interpretation
- The output will show each employee's ID, their salary, and their percent rank:
- If an employee ranks lowest in salary, their SalaryPercentRank will be 0.
- If an employee ranks highest, their SalaryPercentRank will be close to 1, indicating their position relative to all other salaries.
Notes
- Remember that PERCENT_RANK() will return the same value for rows with the same rank.
- If you partition the data (using PARTITION BY), the percent rank will be calculated within those partitions.
This powerful function helps in statistical analysis and understanding the distribution of values in your datasets.