PostgreSQL – NTILE() Function

In PostgreSQL, the NTILE() function is a powerful tool used to divide ordered rows into a specified number of ranked buckets, which are essentially ranked groups. This function is crucial for data analysis and reporting, allowing users to efficiently distribute rows and analyze data in a structured manner.

Let us get a better understanding of the NTILE Function in PostgreSQL from this article.

NTILE() Function in PostgreSQL

The NTILE() function partitions data into a specified number of buckets. Each bucket contains a roughly equal number of rows. This function is particularly useful for creating quantiles, percentiles, and other ranked groupings.

Syntax

The syntax of the NTILE() looks like below:

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

Parameters

Let’s analyze the above syntax:

• buckets: The number of ranked groups. This can be a positive integer or an expression that evaluates to a positive integer.
• PARTITION BY: An optional clause that distributes rows into partitions.
• ORDER BY: This clause sorts rows within each partition.

PostgreSQL NTILE() Function Examples

Let’s look into some practical examples to understand how the NTILE() function works in PostgreSQL.

Example 1: Distributing Rows into Buckets

First, create a table named ‘sales_stats’ that stores the  sales revenue by employees:

CREATE TABLE sales_stats(
    name VARCHAR(100) NOT NULL,
    year SMALLINT NOT NULL CHECK (year > 0),
    amount DECIMAL(10, 2) CHECK (amount >= 0),
    PRIMARY KEY (name, year)
);
INSERT INTO 
    sales_stats(name, year, amount)
VALUES
    ('Raju kumar', 2018, 120000),
    ('Alibaba', 2018, 110000),
    ('Gabbar Singh', 2018, 150000),
    ('Kadar Khan', 2018, 30000),
    ('Amrish Puri', 2018, 200000),
    ('Raju kumar', 2019, 150000),
    ('Alibaba', 2019, 130000),
    ('Gabbar Singh', 2019, 180000),
    ('Kadar Khan', 2019, 25000),
    ('Amrish Puri', 2019, 270000);

Now, use the NTILE() function to distribute rows into 3 buckets for the year 2019:

Query:

SELECT 
    name,
    amount,
    NTILE(3) OVER(
        ORDER BY amount
    )
FROM
    sales_stats
WHERE
    year = 2019;

Output:

Explanation: The NTILE() function divides the rows into three buckets based on the ‘amount’ column for the year 2019.

Example 2: Dividing Rows into Partitions and Buckets

The below query uses the NTILE() function to divide rows in the ‘sales_stats’ table into two partitions and 3 buckets for each.

Query:

SELECT 
    name,
    amount,
    NTILE(3) OVER(
        PARTITION BY year
        ORDER BY amount
    )
FROM
    sales_stats;

Output:

Explanation: ‘PARTITION BY year' divides the rows into partitions based on the year. The ‘ORDER BY amount' Sorts the rows within each partition by the amount column and the NTILE(3) distributes rows into three buckets within each partition.

Important Points About the PostgreSQL NTILE() Function

• NTILE() allows flexible grouping of data into buckets based on specified criteria.
• The function can partition data using the PARTITION BY clause, allowing for more granular analysis.
• The buckets parameter must be a positive integer or an expression that evaluates to a positive integer.