How to Use PostgreSQL NTILE Function

Introduction

In this article we will learn what the PostgreSQL NTILE function is and how to use it with easy to understand code examples.

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 NTILE function

The PostgreSQL NTILE function is a ranking function solely created to separate data into a such range of number of groups. These ranked groups are also known as buckets.

The NTILE() function will assign a bucket on every group starting from 1 (one). For every group, the NTILE() will assign a bucket number that represents the group to where the row was included.

NTILE() function syntax is as follows:

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

Let’s discuss the above statement part by part.

  1. The buckets represents the amount of ranked teams. It will be variety or associate expression that evaluates to a positive number (greater than 0) for every partition. The buckets should not be nullable.

  2. The PARTITION BY clause will distribute the rows into partitions to which the function is defined.

NOTE: The PARTITION BY clause is optional. if taken out in the statement, the function treats the whole result set as a single partition.

  1. The ORDER BY clause will sort out the rows in each partition to which the function is applied.

NOTE: The ORDER BY clause is optional. However, you should invariably use the ORDER BY clause in order to get an expected result.

It is also worthy to note that if the amount of rows isn’t separable by the buckets, the NTILE() function will return a groups of 2 sizes with the distinction by one. The larger groupings continuously precede the smaller teams within the order as specified by the ORDER BY clause.

The PostgreSQL NTILE() function Example

Now that we have an overview understanding of the NTILE() function, we will use it with some few examples.

First, lets create a table to perform against the NTILE() function.

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);

Now that we have the table let’s use the following statement against the NTILE() function.

NTILE() function over a result set example

In this example we will be distributing the rows of the gamers_stats table into four (4) buckets:


GeSHi Error: GeSHi could not find the language sqwl (using path /nas/content/live/orkbprod/wp-content/plugins/codecolorer/lib/geshi/) (code 2)

The result should resemble something like the following.

      name       |  points   | ntile
-----------------+-----------+-------
 Jackie Chan     |  28000.00 |     1
 Mike SkyScraper | 140000.00 |     1
 Jack Sparrow    | 150000.00 |     2
 Luke SkyWalker  | 160000.00 |     3
 Jet Li          | 250000.00 |     4
(5 ROWS)

NTILE() function over a partition example

In this example we will be using NTILE() function to divide the rows in the gamers_stats table into two partitions and three (3) buckets for each, The syntax will be as follows:

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

The result should look something like this:

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

To verify that the partition is also carried out, we took a screenshot from the PostgreSQL GUI application.

Result from NTILE() function

Conclusion

In this article we explained what the PostgreSQL NTILE function does and how to use it. We hope you were able to follow the examples and are able to apply what you’ve learned your problem at hand. If you still need help with NTILE or any database related topic please don’t hesitate to reach out to us at Object Rocket.

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.