PERCENT_RANK
Returns the relative rank of a row in a window partition, that is, the share of rows that come before it.
MySQL 8.1
PERCENT_RANK() OVER ([PARTITION BY ...] [ORDER BY ...])Parameters
- PARTITION BYoptional
- Columns that split rows into partitions, the share is computed in each one separately
- ORDER BYoptional
- Order of rows that defines the place; without it all rows get 0
Return value
A floating-point number (DOUBLE) from 0 to 1
Examples
Share by birth date
MySQL 8.1
SELECT member_name,
birthday,
RANK() OVER (
ORDER BY birthday
) AS birthday_rank,
PERCENT_RANK() OVER (
ORDER BY birthday
) AS birthday_percent_rank
FROM FamilyMembers;Salary position in a department
MySQL 8.1
SELECT department_id,
name,
salary,
ROUND(
PERCENT_RANK() OVER (
PARTITION BY department_id
ORDER BY salary
),
2
) AS salary_percent_rank
FROM employees;Details
The value is calculated as (RANK() - 1) / (number of rows in the partition - 1): the first row gets 0, the last one gets 1. Rows with equal ORDER BY values get the same share, like the same place in RANK. In a partition of one row the result is 0.