NTILE() Function in Oracle

Oracle NTILE()

In Oracle, NTILE() is known as an "ANALYTIC" Function to compute an aggregate value based on a group of rows. These functions are not similar to aggregate functions since they return multiple rows for each group and this group of rows is called a window; that's why these functions are also called "WINDOW" functions.  These functions are also described by the name "RANKING" Function in SQL Server.

NTILE()

To find a rank for the given result set we use some functions like ROW-NUMBER(), RANK() and so on. The NTILE() method is one of them. It is a cool function that divides the query results into groups and places numbers into them or we can say divides the records into a specified number of groups that will be numbered one by one. It can create a tile, a percentile, a decile or anything else.

The following is the syntax for the NTILE Function in Oracle:

SELECT column_name

,NTILE (Number_Expression)

OVER ([partition_Clause]

ORDER BY <Order_Clause>)

FROM table_name;

Number_Expression: a numeric value that decides the number of groups to be created.
 
Partition_Clause: divides the result given by the FROM Clause with the help of the OVER method.
 
Order_Clause: creates an ordered list that will be further divided into a group.
 
Now I will share the use of NTILE() with PARTITION BY and without PARTITION BY.

1. NTILE() with PARTITION BY

Let us use a table named Customer to understand this example:

Query

SELECT Cust_id,

  Cust_name,

  Item,

  Price,

  NTILE(4) OVER (ORDER BY Price) AS Quartile

FROM Customer;
 
According to the preceding query we will select the column names Cust_id, Cust_name, Item and Price where the NTILE value = 4 and ordered by Price. So, after executing the preceding query we get the following output:
 
Output



2. NTILE() without PARTITION BY

Here we are assuming a new and a big table called Order, so that the groups can be seen clearly:

Query

SELECT Supplier_Name,

       Customer_Name,

       Item_Supplied,

       Price,

NTILE(3) OVER (PARTITION BY Supplier_Name

               ORDER BY Price) AS [NTILE]

FROM Order;

According to this QUERY we are selecting the Supplier Name, Customer Name and Items that are supplied and Price from the table ORDER where the NTILE value = 3 (in other words division into three groups) that are partitioned by Supplier name and further ordered by Price. After execution of the query the result is as in the following:
 
Output