NTILE for distribution in PostgreSQL

Introduction

NTILE helps split data into equal parts or groups. It shows which group each row belongs to.

You want to divide sales data into 4 equal groups to see top sellers.

You need to split students' scores into 3 groups: low, medium, high.

You want to assign customers into 5 groups for targeted marketing.

You want to rank employees into quartiles based on performance.

Syntax

PostgreSQL

NTILE(number_of_groups) OVER (ORDER BY column_name)

number_of_groups is how many parts you want to split the data into.

The ORDER BY inside OVER() decides how rows are sorted before splitting.

Examples

This splits students into 4 groups based on their scores, highest first.

PostgreSQL

SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile FROM students;

This divides products into 3 groups by sales, lowest to highest.

PostgreSQL

SELECT product, sales, NTILE(3) OVER (ORDER BY sales) AS tertile FROM sales_data;

Sample Program

This example creates a sales table, inserts 5 products with amounts, then splits them into 2 groups (halves) by amount.

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  product VARCHAR(20),
  amount INT
);

INSERT INTO sales (product, amount) VALUES
('A', 100),
('B', 200),
('C', 300),
('D', 400),
('E', 500);

SELECT product, amount, NTILE(2) OVER (ORDER BY amount) AS half
FROM sales
ORDER BY amount;

Important Notes

If the number of rows can't be divided evenly, some groups will have one more row.

NTILE always assigns group numbers starting from 1 up to the number of groups.

Summary

NTILE splits rows into equal groups based on order.

Use it to create quartiles, tertiles, or any group count.

Groups are numbered starting at 1.