How to Use PostgreSQL NTILE Function
In this article we will learn what the PostgreSQL NTILE function is and how to use it with easy to understand code examples.
- 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.
- To verify if the service is running use the following command.
- The result should look something like the following:
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: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd: 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 NTILE function
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.
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:
[PARTITION BY partition_expression, ... ]
[ORDER BY sort_expression [ASC | DESC], ...]
Let’s discuss the above statement part by part.
bucketsrepresents 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
bucketsshould not be nullable.
PARTITION BYclause will distribute the rows into partitions to which the function is defined.
PARTITION BY clause is optional. if taken out in the statement, the function treats the whole result set as a single partition.
ORDER BYclause will sort out the rows in each partition to which the function is applied.
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
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.
gamers_stats(name, YEAR, points)
Now that we have the table let’s use the following statement against the
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.
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
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:
PARTITION BY YEAR
ORDER BY points
The result should look something like this:
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
To verify that the partition is also carried out, we took a screenshot from the PostgreSQL GUI application.
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