Fetching useful insights from the data available on the database created for the company is the sole purpose of data collection. Getting the list of all the employees from the database with their rankings according to their performances can be useful for decision-makers. Furthermore, ranking employees from all the departments of the company with their records can be helpful in making decisions about their future. For this purpose, the PERCENT_RANK function can be used in Postgres.

This guide will explain the use of PERCENT_RANK in PostgreSQL.

How to Use PERCENT_RANK in PostgreSQL?

The PERCENT_RANK is an analytic function which is kind of a cumulative distributive function in the PostgreSQL database. It is used to calculate the rank of each row available on the table by evaluating the relative standing of their value. Each row gets a specific rank which is always greater than 0 and less than 1 according to the number of rows.

Syntax

Use the following code as the syntax to use the PERCENT_RANK function in the PostgreSQL database:

PERCENT_RANK()   OVER (
  [PARTITION BY partition_exp, ... ]
  ORDER BY   sort_exp [DESC | ASC], ...
   );

The above code snippet:

- The PERCENT_RANK function is declared with No parameters assigned to it in its small parentheses.
- After that, use the OVER clause that contains two parameters such as PARTITION BY and ORDER BY clauses.
- PARTITION BY clause is an optional clause to create table partitions.
- The ORDER BY clause is a mandatory clause as it implements the formula for the PERCENT_RANK function in the query.

The following line explains that the value of the PERCENT_RANK function is always less than 0 and greater than 1. However, the first row of the table contains 0, and the last row has the value 0 in the percent_rank column:

0 < VALUE <= 1

Example 1: PERCENT_RANK in PostgreSQL

Use the following SELECT command to get data from the orders table:

SELECT * FROM orders;

Running the above code will display the data from the orders table:

Execute the following code to use PERCENT_RANK in the PostgreSQL tables:

SELECT
  id,
  customer_id,
  product_name,
  price,
 percent_rank() OVER (ORDER BY price DESC) AS percent_rank
 FROM orders;

The above code selects the list of columns from the orders table and applies the PERCENT_RANK function. The PERCENT_RANK function uses an OVER clause on the price column and displays its rank in descending order:

Example 2: PERCENT_RANK With PARTITION BY Clause

Use the given command to fetch the “students” table records:

SELECT * FROM students;

Executing the above code will display the data from the “students” table:

Use the following query to apply the PERCENT_RANK function with PARTITION BY clause in PostgreSQL:

SELECT
  student_id,
  student_name,
  class_name,
  score,
  percent_rank() OVER (PARTITION BY   class_name ORDER BY score ASC) AS percent_rank
 FROM
  students;

The above code selects columns from the “students” table to apply the PERCENT_RANK function using PARTITION BY and ORDER BY clauses. PARTITION BY clause is used to create a partition using class_name and ORDER BY clause is applied to the score column. As the above table is partitioned by class and each class has only two rows so the rank has been assigned 0 and 1 for each row:

That’s all about using PERCENT_RANK in PostgreSQL.

Conclusion

To use the PERCENT_RANK function in the PostgreSQL database, simply start the query with the PERCENT_RANK keyword with Null parameters. It has an OVER clause containing PARTITION BY and ORDER BY clauses inside it to apply rank on the table. ORDER BY clause is a mandatory clause but the PARTITION BY clause is an optional one. This guide has explained the use of PERCENT_RANK in PostgreSQL with and without PARTITION BY examples.