PostgreSQL is a database server that allows the user to manage their huge pool of data by storing it in the form of tables and accessing that later. It enables the user to fetch data from the database using the SQL queries in the PostgreSQL database. It also allows the user to compare data inside the table by dividing the rows into multiple groups or partitions.

This guide will explain the process of using the NTILE function in PostgreSQL with the help of examples.

PostgreSQL NTILE Function Explained With Example

The NTILE function is used to distribute the rows of the PostgreSQL table into a specified number of groups as equal in size as the table allows. If the data rows are not divisible by the number of groups then the function divides it into different sizes. Larger groups are placed before the smaller groups and the function divides the rows according to size provided by the user.

Syntax

To use the NTILE function in the PostgreSQL database, use the syntax mentioned below:

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

The above code block depicts:

- The code uses the NTILE keyword suggesting the name of the function and buckets as its argument to set the number of groups for the function.
- The buckets parameter determines the number of divisions a table will be partitioned into like 2 means that the table will be divided into 2 parts and so on.
- The ORDER BY clause is used to get the order of the tables using a column from the table and it is mandatory for using the NTILE function.
- The PARTITION BY clause is the optional part of the function which can be used to get groups based on the values of the column.

Example 1: How to Use NTILE Function in PostgreSQL?

To use the NTILE function in PostgreSQL, simply create a table and insert data in it and then use the following query to access data from it:

SELECT * FROM employee;

The following screenshot displays the data from the employee table in PostgreSQL:

Use the following code to divide the table using the NTILE function in the PostgreSQL database:

 SELECT name, gender, salary,
  NTILE(4) 
  OVER (ORDER BY salary) 
 FROM employee
 WHERE salary >= 5000;

The above code selects columns of the table and uses the NTILE function with 4 buckets to keep the data of the table in 4 parts. The code uses a salary column to order the table and it only divides the employees with salary more than 5000:

The above screenshot displays that there are 6 employees earning 5000 or more and the NTILE function has divided the table into 4 groups. The first two groups have 2 rows and the last 2 only contain 1 row in it as the number of rows is not divisible by 4.

Example 2: PARTITION BY Clause in NTILE Function From PostgreSQL

The following code uses the PARTITION BY clause in the NTILE function to make partitions in the employee table:

SELECT name, gender, salary,
  NTILE(4) 
  OVER(PARTITION BY gender
   ORDER BY salary) 
 FROM employee;

The following screenshot displays that the NTILE function divides the table but separately as the PARTITION BY clause has already divided it into 2 parts. The Female has 4 rows so in this case each group contains a single value but Males were 6 in number so it divides the table as it was in the first example:

That’s all about using the NTILE function in the PostgreSQL table with the help of examples.

Conclusion

To use the NTILE function in PostgreSQL, simply create a table and insert data in it to use the NTILE function on it. The NTILE function is used to divide the rows of the table in the form of buckets which will be mentioned in its arguments. Each bucket contains the number of rows from the PostgreSQL table and buckets with greater rows are placed above others. This guide has explained the process of using the NTILE function in the PostgreSQL table with the help of examples.