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.4
PERCENT_RANK()Return value
A floating-point number (DOUBLE) from 0 to 1
Examples
Share by birth date
MySQL 8.4
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.4
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, and without ORDER BY it is 0 for every row of the partition.