PostgreSQL percent_rank() Function

The PostgreSQL percent_rank() function returns the relative rank within the partition where the current row is located, that is (rank() - 1) / (number of rows partition - 1).

`percent_rank()` Syntax

Here is the syntax of the PostgreSQL percent_rank() function:

percent_rank()
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

Parameters

partition_column_list: List of columns for partitioning.
partition_column_list: List of columns for sorting.

Return value

The PostgreSQL percent_rank() function returns a number between 0 and 1 (inclusive), representing the relative rank within the partition where the current row is located.

This is the formula how to calculate it:

`(rank of the row - 1) / (number of rows partition - 1)`

`percent_rank()` Examples

Preparing Data

Use the following CREATE TABLE statement to create a table named student_grade to store grades of students:

CREATE TABLE student_grade (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class CHAR(1) NOT NULL,
  subject VARCHAR(20) NOT NULL,
  grade INT NOT NULL
);

This student_grade table has 5 columns as following:

id - The row ID, primary key.
name - The name of a student.
class - The class a student is in.
subject - The name of a subject.
grade - The grades for a subject and a student.

Insert some rows into the student_grade table using the following INSERT statement:

INSERT INTO student_grade
  (name, class, subject, grade)
VALUES
  ('Tim', 'A', 'Math', 9),
  ('Tom', 'A', 'Math', 7),
  ('Jim', 'A', 'Math', 8),
  ('Tim', 'A', 'English', 7),
  ('Tom', 'A', 'English', 8),
  ('Jim', 'A', 'English', 7),
  ('Lucy', 'B', 'Math', 8),
  ('Jody', 'B', 'Math', 6),
  ('Susy', 'B', 'Math', 9),
  ('Lucy', 'B', 'English', 6),
  ('Jody', 'B', 'English', 7),
  ('Susy', 'B', 'English', 8);

Use the following SELECT statement to show all rows of this table:

SELECT * FROM student_grade;

 id | name | class | subject | grade
----+------+-------+---------+-------
  1 | Tim  | A     | Math    |     9
  2 | Tom  | A     | Math    |     7
  3 | Jim  | A     | Math    |     8
  4 | Tim  | A     | English |     7
  5 | Tom  | A     | English |     8
  6 | Jim  | A     | English |     7
  7 | Lucy | B     | Math    |     8
  8 | Jody | B     | Math    |     6
  9 | Susy | B     | Math    |     9
 10 | Lucy | B     | English |     6
 11 | Jody | B     | English |     7
 12 | Susy | B     | English |     8
(12 rows)

Example 1

To show the relative ranking of each student in each subject, sorting by grade in descending order, use the following statement:

SELECT *,
  rank() OVER (
    PARTITION BY subject
    ORDER BY grade DESC
  ),
  percent_rank() OVER (
    PARTITION BY subject
    ORDER BY grade DESC
  )
FROM student_grade;

 id | name | class | subject | grade | rank | percent_rank
----+------+-------+---------+-------+------+--------------
 12 | Susy | B     | English |     8 |    1 |            0
  5 | Tom  | A     | English |     8 |    1 |            0
 11 | Jody | B     | English |     7 |    3 |          0.4
  4 | Tim  | A     | English |     7 |    3 |          0.4
  6 | Jim  | A     | English |     7 |    3 |          0.4
 10 | Lucy | B     | English |     6 |    6 |            1
  1 | Tim  | A     | Math    |     9 |    1 |            0
  9 | Susy | B     | Math    |     9 |    1 |            0
  7 | Lucy | B     | Math    |     8 |    3 |          0.4
  3 | Jim  | A     | Math    |     8 |    3 |          0.4
  2 | Tom  | A     | Math    |     7 |    5 |          0.8
  8 | Jody | B     | Math    |     6 |    6 |            1
(12 rows)

Note that the window function in the SQL statement above:

percent_rank() OVER (
  PARTITION BY subject
  ORDER BY grade DESC
)

In the OVER clause,

The PARTITION BY subject partitions all rows by subject
The ORDER BY grade sorts all rows within each partition in ascending order by grade.
The PERCENT_RANK() returns the relative rank of each row within its associated partition.

You can also see the difference between percent_rank() and rank() from above.

Example 2

To show the relative ranking of each student in each class, sorting by total grade in descending order, use the following statement:

SELECT t.*,
  percent_rank() OVER (
    PARTITION BY class
    ORDER BY t.sum_grade DESC
  )
FROM (
    SELECT class,
      name,
      sum(grade) sum_grade
    FROM student_grade
    GROUP BY class, name
  ) t;

 class | name | sum_grade | percent_rank
-------+------+-----------+--------------
 A     | Tim  |        16 |            0
 A     | Jim  |        15 |          0.5
 A     | Tom  |        15 |          0.5
 B     | Susy |        17 |            0
 B     | Lucy |        14 |          0.5
 B     | Jody |        13 |            1
(6 rows)

Notice this subquery in the above statement:

SELECT class,
  name,
  sum(grade) sum_grade
FROM student_grade
GROUP BY class, name

This subquery uses the GROUP BY clause and the sum() function sums up each student’s total grade by class and student.

 class | name | sum_grade
-------+------+-----------
 A     | Tim  |        16
 A     | Jim  |        15
 A     | Tom  |        15
 B     | Jody |        13
 B     | Lucy |        14
 B     | Susy |        17
(6 rows)

The main statement is partitioned by class within this subquery, then sorted by total grade in descending order within each partition, and gets the relative rank of each row within its associated partition using percent_rank().

percent_rank() Syntax