Introduction to PostgreSQL NTILE() function

The PostgreSQLNTILE() function allows you to divide ordered rows in the partition into a specified number of ranked groups as equal size as possible. These ranked groups are called buckets.

TheNTILE() function assigns each group a bucket number starting from 1. For each row in a group, theNTILE() function assigns a bucket number representing the group to which the row belongs.

The syntax of theNTILE() function is as follows:

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

Let’s examine the syntax in detail:

buckets

Thebuckets represents the number of ranked groups. It can be a number or an expression that evaluates to a positive integer value (greater than 0) for each partition. Thebuckets must not be nullable.

PARTITION BY

ThePARTITION BY clause distributes rows into partitions to which the function is applied.

ThePARTITION BY clause is optional. If you skip it, the function treats the whole result set as a single partition.

ORDER BY

TheORDER BY clause sorts rows in each partition to which the function is applied.

TheORDER BY clause is optional. However, you should always use theORDER BY clause to get an expected result.

Note that if the number of rows is not divisible by thebuckets, theNTILE() function returns groups of two sizes with the difference by one. The bigger groups always come before the smaller groups in the order specified by theORDER BY clause.

PostgreSQL NTILE() function examples

Let’s take some examples of using theNTILE() function.

We’ll use thesales_stats table created in theCUME_DIST() function tutorial to demonstrate theNTILE() function.

SELECTyear,name,amountFROMactual_salesORDER BYyear,name;

1) Using PostgreSQL NTILE() function over a result set example

This example uses theNTILE() function to distribute rows into 3 buckets:

SELECTname,amount,NTILE(3)OVER(ORDER BY amount)FROMsales_statsWHEREyear = 2019;

Here is the output:

2) Using PostgreSQL NTILE() function over a partition example

This example uses theNTILE() function to divide rows in thesales_stats table into two partitions and 3 buckets for each:

SELECTname,amount,NTILE(3)OVER(PARTITION BY yearORDER BY amount)FROMsales_stats;

Here is the result set:

In this tutorial, you have learned how to use the PostgreSQLNTILE() function to distribute ordered rows within a partition into a specified number of ranked groups.

Last updated onApril 11, 2020

On this page

• Introduction to PostgreSQL NTILE() function
• PostgreSQL NTILE() function examples