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.
1 | sudo service postgresql start |
- To verify if the service is running use the following command.
1 | service postgresql status |
- The result should look something like the following:
1 2 3 4 5 6 7 8 9 | ● 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.
- Open Control Panel
- Open Administrative Tools
- Open Services
- Find the PostgreSQL Server service
- Start, Stop or Restart the service
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:
1 | PERCENT_RANK( ) OVER ([partioning] ordering) |
For a more detailed syntax we can use the following.
1 2 3 4 | PERCENT_RANK() OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ... ) |
Let’s discuss the above statement’s part by part.
The
PARTITION BYclause 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.The
ORDER BYclause will be specifying the order of rows in each of the partition to where the function is applied.The
PERCENT_RANK()function will return a result that is greater than 0 and less than or equal to 1.
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.
1 2 3 4 5 6 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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.
1 2 3 4 5 6 7 8 9 10 | SELECT name, points, PERCENT_RANK() OVER ( ORDER BY points ) FROM gamers_stats WHERE YEAR = 2019; |
The result should look something like the following:
1 2 3 4 5 6 7 8 | 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.
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.
1 2 3 4 5 6 7 8 9 | SELECT name, points, PERCENT_RANK() OVER ( PARTITION BY YEAR ORDER BY points ) FROM gamers_stats; |
The result should look something like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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