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