PostgreSQL NTILE Window Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL NTILE() function to assign a tile number to each row within a partition in a result set.

Getting Started with the PostgreSQL NTILE() Window Function #

The NTILE() function divides the rows of a result set into roughly equal-sized groups or tiles. It assigns each row a tile number based on the distribution.

The NTILE() function can be helpful for data segmentation and percentile calculations.

Syntax #

Here’s the syntax for the NTILE() function:

NTILE(num_tiles) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

num_tiles: The number of tiles or groups you want to divide the result set into.
PARTITION BY: This optional clause divides the result set into partitions. If you omit the clause, the PARTITION BY treats the whole result set as a single partition.
ORDER BY: This specifies the columns by which you want to sort the rows within each partition.

PostgreSQL NTILE Window Function Examples #

Let’s explore examples of using the NTILE() function with the products table, which contains information about products, to understand how it works.

Basic NTILE Window Function Example #

The following SELECT statement uses the NTILE() function to divide the products into three roughly equal-sized groups based on their safety stock, which is a measure of the minimum quantity of a product that should be kept in stock to ensure a continuous supply:

SELECT
  product_name,
  safety_stock,
  NTILE(3) OVER (
    ORDER BY
      safety_stock
  ) AS safety_stock_tile
FROM
  products
WHERE
  safety_stock > 0;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       product_name        | safety_stock | safety_stock_tile
---------------------------+--------------+-------------------
 Samsung Galaxy S24        |           10 |                 1
 Sony Xperia 1 VI          |           10 |                 1
 LG G3 OLED                |           10 |                 1
 Apple iPad Pro 12.9       |           15 |                 1
 Samsung Galaxy Watch 6    |           15 |                 1
 Samsung Galaxy Z Fold 5   |           20 |                 1
 Apple Watch Series 9      |           20 |                 2
 Apple iPhone 15           |           20 |                 2
 LG OLED TV C3             |           20 |                 2
 Huawei Mate 60            |           30 |                 2
 Samsung Galaxy Buds Pro 2 |           30 |                 2
 Sony Bravia XR A95K       |           30 |                 3
 Samsung QN900C Neo QLED   |           40 |                 3
 Apple AirPods Pro 3       |           45 |                 3
 Apple iPhone 15 Pro Max   |           50 |                 3
 Samsung Galaxy Tab S9     |           60 |                 3Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this output, the safety_stock_tile column contains the tile number assigned to each product based on its safety stock.

Using PARTITION BY Clause #

The following statement uses the NTILE() function to divide the result set into partitions for each category and then apply the NTILE() function within each.

SELECT
  category_id,
  product_name,
  safety_stock,
  NTILE(3) OVER (
    PARTITION BY
      category_id
    ORDER BY
      safety_stock
  ) AS safety_stock_tile
FROM
  products
WHERE
  safety_stock > 0;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_id |       product_name        | safety_stock | safety_stock_tile
-------------+---------------------------+--------------+-------------------
           3 | Samsung Galaxy S24        |           10 |                 1
           3 | Sony Xperia 1 VI          |           10 |                 1
           3 | Samsung Galaxy Z Fold 5   |           20 |                 2
           3 | Apple iPhone 15           |           20 |                 2
           3 | Huawei Mate 60            |           30 |                 3
           3 | Apple iPhone 15 Pro Max   |           50 |                 3
           4 | Apple iPad Pro 12.9       |           15 |                 1
           4 | Samsung Galaxy Tab S9     |           60 |                 2
           5 | Samsung Galaxy Buds Pro 2 |           30 |                 1
           5 | Apple AirPods Pro 3       |           45 |                 2
           6 | Samsung Galaxy Watch 6    |           15 |                 1
           6 | Apple Watch Series 9      |           20 |                 2
           8 | LG G3 OLED                |           10 |                 1
           8 | LG OLED TV C3             |           20 |                 1
           8 | Sony Bravia XR A95K       |           30 |                 2
           8 | Samsung QN900C Neo QLED   |           40 |                 3Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this output, the safety_stock_tile column contains the tile number assigned to each product’s safety stock within each category.

You can retrieve the category name from the categories table by joining the products table with the categories table:

SELECT 
    category_name,
    product_name, 
    safety_stock, 
    NTILE(3) OVER (
          PARTITION BY category_name
          ORDER BY safety_stock
    ) AS safety_stock_tile
FROM 
    products
JOIN categories USING (category_id)
WHERE
   safety_stock > 0;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_name |       product_name        | safety_stock | safety_stock_tile
---------------+---------------------------+--------------+-------------------
 Accessories   | Samsung Galaxy Buds Pro 2 |           30 |                 1
 Accessories   | Apple AirPods Pro 3       |           45 |                 2
 Smartphones   | Samsung Galaxy S24        |           10 |                 1
 Smartphones   | Sony Xperia 1 VI          |           10 |                 1
 Smartphones   | Samsung Galaxy Z Fold 5   |           20 |                 2
 Smartphones   | Apple iPhone 15           |           20 |                 2
 Smartphones   | Huawei Mate 60            |           30 |                 3
 Smartphones   | Apple iPhone 15 Pro Max   |           50 |                 3
 Tablets       | Apple iPad Pro 12.9       |           15 |                 1
 Tablets       | Samsung Galaxy Tab S9     |           60 |                 2
 Televisions   | LG G3 OLED                |           10 |                 1
 Televisions   | LG OLED TV C3             |           20 |                 1
 Televisions   | Sony Bravia XR A95K       |           30 |                 2
 Televisions   | Samsung QN900C Neo QLED   |           40 |                 3
 Wearables     | Samsung Galaxy Watch 6    |           15 |                 1
 Wearables     | Apple Watch Series 9      |           20 |                 2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

Use the NTILE() function to divide a result set into quantiles, quartiles, or equal-sized groups.

Quiz #

Was this tutorial helpful ?