How to Use PostgreSQL PERCENT_RANK Function

Introduction

In this article we will explain the PostgreSQL PERCENT_RANK Function and demonstrate how to use it in code.

Prerequisites

  • Ensure that PostgreSQL server is properly installed, configured and running on the background.

For Linux and Windows systems you can download PostgreSQL here

  • To start PostgreSQL server use a LINUX machine use the following command.
sudo service postgresql start
  • To verify if the service is running use the following command.
service postgresql status
  • The result should look something like the following:
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
   Active: active (exited) since Thu 2019-08-01 14:51:20 PST; 36min ago
  Process: 1230 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 1230 (code=exited, status=0/SUCCESS)

Aug 01 14:51:20 user-UX330UAK systemd[1]: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd[1]: Started PostgreSQL RDBMS.
lines 1-8/8 (END)
  • To start, stop and restart PostgreSQL server in a Windows machine do the following instruction.
  1. Open Control Panel
  2. Open Administrative Tools
  3. Open Services
  4. Find the PostgreSQL Server service
  5. Start, Stop or Restart the service

Restarting PostgreSQL server in a Windows Machine

What is PERCENT_RANK function

The PERCEN_RANK() function is somewhat the same with the CUME_DIST() function. The PERCENT_RANK() function will evaluate the comparative standing of a value within a group of values.

Below is the syntax of the PERCENT_RANK() function:

PERCENT_RANK(  ) OVER ([partioning] ordering)

For a more detailed syntax we can use the following.

PERCENT_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

Let’s discuss the above statement’s part by part.

  1. The PARTITION BY clause will be dividing the rows into multiple partitions to where the PERCENT_RANK() function is applied. Whenever we remove this clause, the function will treat the whole result set as a single partition.

  2. The ORDER BY clause will be specifying the order of rows in each of the partition to where the function is applied.

  3. The PERCENT_RANK() function will return a result that is greater than 0 and less than or equal to 1.

0 < PERCENT_RANK() <= 1

The first value will be receiving a rank of zero. Tie values measure to identical additive distribution value.

PostgreSQL PERCENT_RANK() example

Before we proceed in making examples, we will be creating a table that we can use in our examples.

We will be creating a table named gamers_stats, to do this use the following syntax.

CREATE TABLE gamers_stats(
    name VARCHAR(100) NOT NULL,
    YEAR SMALLINT NOT NULL CHECK (YEAR > 0),
    points DECIMAL(10,2) CHECK (points >= 0),
    PRIMARY KEY (name,YEAR)
);

After creating the above gamers_stats table, we will now insert some rows.

INSERT INTO
    gamers_stats(name, YEAR, points)
VALUES
    ('Luke SkyWalker',2018,180000),
    ('Mike SkyScraper',2018,170000),
    ('Jack Sparrow',2018,140000),
    ('Jackie Chan',2018,300000),
    ('Jet li',2018,300000),
    ('Luke SkyWalker',2019,160000),
    ('Mike SkyScraper',2019,140000),
    ('Jack Sparrow',2019,150000),
    ('Jackie Chan',2019,28000),
    ('Jet Li',2019,250000);

PERCENT_RANK() function over a result set example

The below statement will be using the PERCENT_RANK() function to calculate the points percentile of each gamers in 2019.

SELECT
    name,
   points,
    PERCENT_RANK() OVER (
        ORDER BY points
    )
FROM
    gamers_stats
WHERE
    YEAR = 2019;

The result should look something like the following:

  name       |  points   | percent_rank
-----------------+-----------+--------------
 Jackie Chan     |  28000.00 |            0
 Mike SkyScraper | 140000.00 |         0.25
 Jack Sparrow    | 150000.00 |          0.5
 Luke SkyWalker  | 160000.00 |         0.75
 Jet Li          | 250000.00 |            1
(5 ROWS)

Let’s verify using the PostgreSQL GUI application pgAdmin4.

result from the PERCENT_RANK() function

PERCENT_RANK() function over a partition example

The below statement will be using the PERCENT_RANK() function to calculate the points percentile by the gamers in both 2018 and 2019.

SELECT
    name,
   points,
    PERCENT_RANK() OVER (
      PARTITION BY YEAR
        ORDER BY points
    )
FROM
    gamers_stats;

The result should look something like this:

      name       | YEAR |  points   | percent_rank
-----------------+------+-----------+--------------
 Jack Sparrow    | 2018 | 140000.00 |            0
 Mike SkyScraper | 2018 | 170000.00 |         0.25
 Luke SkyWalker  | 2018 | 180000.00 |          0.5
 Jackie Chan     | 2018 | 300000.00 |         0.75
 Jet li          | 2018 | 300000.00 |         0.75
 Jackie Chan     | 2019 |  28000.00 |            0
 Mike SkyScraper | 2019 | 140000.00 |         0.25
 Jack Sparrow    | 2019 | 150000.00 |          0.5
 Luke SkyWalker  | 2019 | 160000.00 |         0.75
 Jet Li          | 2019 | 250000.00 |            1
(10 ROWS)

Conclusion

In this article we explored the PostgreSQL PERCENT_RANK function. We touched on what it does and gave a concrete code example of its usage. If you still need help with PostgreSQL PERCENT_RANK or have any other database issues please don’t hesitate to email us at Object Rocket because handling databases is what we do best. Let us help!

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.