SQL NTILE

Summary: in this tutorial, you will learn how to use the SQL NTILE() function to break a result set into a specified number of buckets.

An Overview of SQL NTILE() function

The SQL NTILE() is a window function that allows you to break the result set into a specified number of approximately equal groups, or buckets. It assigns each group a bucket number starting from one. For each row in a group, the NTILE() function assigns a bucket number representing the group to which the row belongs.

The syntax of the NTILE() function is as follows:

NTILE(buckets) OVER ( 
	PARTITION BY expr1, expr2,...
	ORDER BY expr1 [ASC|DESC], expr2 ...
)
Code language: SQL (Structured Query Language) (sql)

Let’s examine the syntax in detail:

buckets

The number of buckets, which is a literal positive integer number or an expression that evaluates to a positive integer number.

PARTITION BY

The PARITITION BY clause divides the result set returned from the FROM clause into partitions to which the NTILE() function is applied.

ORDER BY

The ORDER BY clause specifies the order of rows in each partition to which the NTILE() is applied.

Notice that if the number of rows is not divisible by buckets, the NTILE() function results in groups of two sizes with the difference by one. The larger groups always come before the smaller group in the order specified by the ORDER BY clause.

In case the total of rows is divisible by buckets, the rows are divided evenly among groups.

The following statement creates a new table named t that stores 10 integers from one to ten:

CREATE TABLE t (
	col INT NOT NULL
);
	
INSERT INTO t(col) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
	
	
SELECT * FROM t;
Code language: SQL (Structured Query Language) (sql)

If you use the NTILE() function to divide ten rows into three groups, you will have the first group with four rows and other two groups with three rows.

SELECT 
	col, 
	NTILE (3) OVER (
		ORDER BY col
	) buckets
FROM 
	t;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

As clearly shown in the output, the first group has four rows while the other groups have three rows.

The following statement uses two instead of three buckets:

SELECT 
	col, 
	NTILE (2) OVER (
		ORDER BY col
	) buckets
FROM 
	t;
Code language: SQL (Structured Query Language) (sql)

Now, we have two groups which have the same number of rows.

SQL NTILE() function examples

See the following employees table from the sample database:

Using SQL NTILE() function over the result set example

The following statement uses the NTILE() function to divide the employees into five buckets based on their salaries:

SELECT
	first_name, 
	last_name, 
	salary,
	NTILE(5) OVER (
		ORDER BY salary DESC
	) salary_group
FROM 
	employees;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Using SQL NTILE() function over partition example

The following statement breaks the employees in each department into two groups:

SELECT
	first_name, 
	last_name, 
	department_name,
	salary,
	NTILE(2) OVER (
		PARTITION BY department_name
		ORDER BY salary
	) salary_group
FROM 
	employees e
INNER JOIN departments d
	ON d.department_id = e.department_id;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

In this example:

• First, the PARTITION BY clause divided the employees by department names into partitions.
• Then, the ORDER BY clause sorted the employees in each partition by salary.
• Finally, the NTILE() function assigned each row in each partition a bucket number. It reset the bucket number whenever the department changes.

In this tutorial, you have learned how to use the SQL NTILE() function to break the result set into a specified number of buckets.