PERCENT_RANK
Returns the relative rank of a row in a window partition: the share of rows before it, from 0 to 1.
PostgreSQL 17.5
PERCENT_RANK() OVER ([PARTITION BY ...] [ORDER BY ...])Parameters
- PARTITION BYoptional
- Columns that split rows into partitions, ranks are computed in each one separately
- ORDER BYoptional
- Order of rows that defines the rank
Return value
A floating-point number (double precision) from 0 to 1
Examples
Relative rank by birth date
PostgreSQL 17.5
SELECT member_name,
birthday,
PERCENT_RANK() OVER (
ORDER BY birthday
) AS pct_rank
FROM FamilyMembers;RANK and PERCENT_RANK for prices
PostgreSQL 17.5
SELECT unit_price,
RANK() OVER (
ORDER BY unit_price
) AS price_rank,
ROUND(
PERCENT_RANK() OVER (
ORDER BY unit_price
)::numeric,
2
) AS pct_rank
FROM Payments
ORDER BY unit_price;Details
It is computed as (RANK − 1) / (number of rows in the partition − 1). Equal values get the same rank. For a partition of one row the result is 0.