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