Oracle NTILE

Summary: in this tutorial, you will learn how to use the Oracle NTILE() function to divide an ordered result set into a number of buckets and assign an appropriate bucket number to each row.

Introduction to Oracle NTILE() function

Oracle NTILE() function is an analytical function that divides an ordered result set into a number of and assigns an appropriate bucket number to each row.

The following illustrates the syntax of the NTILE() function:

NTILE(expression) OVER ( 
    [query_partition_clause]
    order_by_clause 
)
	Code language: SQL (Structured Query Language) (sql)

In this syntax:

expression

The expression is any valid expression evaluated to a positive integer.

The buck number is from 1 to the result of expression e.g., N. The number of rows in buckets can differ by at most 1. The remainder of the number of rows divided by buckets is allocated to each bucket, starting from the first bucket.

Suppose you have a result set that consists of 10 rows and you want to divide this result set into 4 buckets. First, 4 buckets will 2 rows are created. The remainder is 2 (10/4). These 2 buckets will be distributed to first and second buckets. As the result, the first and second buckets will have 2 + 1 = 3 rows. The last two buckets will have 2 rows.

The following picture illustrates the logic:

If the result of the expression is greater than the number of rows, then a number of buckets equal to the number of rows will be filled, and the remaining buckets will be empty.

For example, if you have five rows and six buckets, each row will be assigned a bucket number from 1 to 6. The 7th bucket will be empty.

Notice that the expression cannot contain any subquery or analytic function.

partition_by_clause

The PARTITION BY clause divides the result sets into partitions to which the NTILE function is applied:

PARTITION BY expression1 [,expression2,..]
Code language: SQL (Structured Query Language) (sql)

order_by_clause

The order_by_clause clause specifies the order of rows in each partition to which the NTILE() is applied:

ORDER BY expression1 [,expression2,...] 
[ASC | DESC ] 
[NULLS FIRST | LAST]Code language: SQL (Structured Query Language) (sql)

Oracle NTILE() function examples

We will use the salesman_performance view for the demonstration:

CREATE OR REPLACE VIEW salesman_performance (
	salesman_id, 
	year, 
	sales
) AS
SELECT 
	salesman_id, 
	EXTRACT(YEAR FROM order_date), 
	SUM(quantity*unit_price)
FROM 
	orders
INNER JOIN order_items USING (order_id)
WHERE 
	salesman_id IS NOT NULL
GROUP BY 
	salesman_id, 
	EXTRACT(YEAR FROM order_date);Code language: SQL (Structured Query Language) (sql)

Using Oracle NTILE() function example

The following statement divides into 4 buckets the values in the sales column of the salesman_performance view from the year of 2017.

SELECT 
	salesman_id, 
	sales,
	NTILE(4) OVER(
		ORDER BY sales DESC
	) quartile
FROM 
	salesman_performance
WHERE 
	year = 2017;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example, the sales column has 9 values so the first bucket receives 3 rows. Other 3 buckets receive 2 rows.

Using Oracle NTILE() function with PARTITION BY clause example

The following statement divides into 4 buckets the values in the sales column of the salesman_performance view in the year of 2016 and 2017:

SELECT 
	salesman_id, 
	sales,
	year,
	NTILE(4) OVER(
		PARTITION BY year
		ORDER BY sales DESC
	) quartile
FROM 
	salesman_performance
WHERE
	year = 2016 OR year = 2017;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

In this tutorial, you have learned how to use the Oracle NTILE() function to divide an ordered result set into a number of buckets and assign an appropriate bucket number to each row.