syntax and example of NTILE function in oracle sql

The LEAD function in Oracle SQL / PLSQL is an analytical function and helps us to query more than one row at a time without having self join on the table.
The LEAD function returns the value from the rows ahead in the table.

Syntax for the LEAD Function in Oracle SQL / PLSQL is:
SELECT column(s)
,LEAD (expression [,offset,default]) OVER ([PARTITION BY column(s) ORDER BY column(s)]
FROM table_name(s);

Note:

‘expression’ is an expression that can contain other built in Oracle SQL functions, but cannot contain any Oracle SQL analytica function.
‘offset’ is an optional value. It is a physical offset from the current records in a table, if omitted the default is 1.
‘default’ is also optional. It is the value that would be returned if the offset goes out of bounds of the table, if omitted default will be NULL.

Example 1:

Using LEAD function to without PARTITION BY
Suppose we have a table named ‘orders as shown below:

ORDER_DATE	PRODUCT_ID	Quantity
25/01/2012	1001	10
26/01/2012	1001	20
27/01/2012	1002	28
28/01/2012	1002	30
29/01/2012	1001	32
30/01/2012	1003	45

If we write our query for LEAD without PARTITION BY as:

SELECT product_id
       ,order_date
       ,LEAD (order_date,1) OVER (ORDER BY order_date) AS Next_Order_Date
FROM orders;

We will get the following output:

PRODUCT_ID	ORDER_DATE	NEXT_ORDER_DATE
1001	1/25/2012	1/26/2012
1001	1/26/2012	1/27/2012
1002	1/27/2012	1/28/2012
1002	1/28/2012	1/29/2012
1001	1/29/2012	1/30/2012
1003	1/30/2012

Let’s analyze the fetched records for understanding the LEAD Analytical function without PARTITION BY:

1^{st, 2nd,}3^rd, 4^th and 5^th record tells us the product_id, order_date and also the next_order_date when a product was ordered, it doesn’t matter what product was order, what matters here is that on order was placed on a date.
The 6^th records tell us that after 1/30/2012 no product was ordered and hence NULL was returned as next_order_date.

Example 2:

Using LEAD function to with PARTITION BY

Suppose we have a table named ‘orders as shown below:

ORDER_DATE	PRODUCT_ID	Quantity
25/01/2012	1001	10
26/01/2012	1001	20
27/01/2012	1002	28
28/01/2012	1002	30
29/01/2012	1001	32
30/01/2012	1003	45

If we write our query for LEAD with PARTITION BY as:

SELECT product_id
       ,order_date
       ,LEAD (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS Next_Order_Date
FROM orders;

We will get the following output:

PRODUCT_ID	ORDER_DATE	NEXT_ORDER_DATE
1001	1/25/2012	1/26/2012
1001	1/26/2012	1/29/2012
1001	1/29/2012
1002	1/27/2012	1/28/2012
1002	1/28/2012
1003	1/30/2012

Let’s analyze the fetched records for understanding the LEAD Analytical function with PARTITION BY:

1^st, 2^nd and 3^rd record tells us for the product with product_id = 1001 an order was placed on 01/25/2012 and next order was placed on 01/26/2012 and then the next order was placed on 01/29/2012, it doesn’t matter when the product was ordered, what matters here is that on order was for a particular product was placed.
4^th and 5^th record tells us that an order for product with product_id = 1002 was placed on 01/27/2012 and the next order for the same was placed on 01/28/2012.
The 6^th records tell us that after 1/30/2012 product with product_id = 1003 was not ordered and hence NULL was returned as next_order_date.

The NTILE function in Oracle SQL / PLSQL divides an ordered set of data or a record into number of buckets indicated by expression and assigns the appropriate bucket number to each record. The buckets are numbered 1 upto expression. The expression value must resolve to a positive constant for each partition.

Syntax for the NTILE Function in Oracle SQL / PLSQL is:
SELECT column(s)
,NTILE (expression) OVER ([partition by column(s)] ORDER BY column(s))
FROM table_name(s);

Example 1:

Using NTILE function to without PARTITION BY:

Suppose we have a table named ‘employee’ as shown below:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10
107	Emp G	12000	Sales	10
108	Emp H	12000	Sales	10

If we write our query for NTILE without PARTITION BY as:

SELECT employee_name
       ,salary
       ,department
       ,NTILE(4) OVER (ORDER BY salary) AS Quartile
FROM employee;

We will get the following output:

Employee_Name	Salary	Department	Quartile
Emp A	10000	Sales	1
Emp H	12000	Sales	1
Emp G	12000	Sales	2
Emp B	20000	IT	2
Emp F	20000	Sales	3
Emp C	28000	IT	3
Emp D	30000	Support	4
Emp E	32000	Sales	4

Example 2:

Using NTILE function to with PARTITION BY:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10
107	Emp G	12000	Sales	10
108	Emp H	12000	Sales	10

If we write our query for NTILE with PARTITION BY as:

SELECT employee_name
       ,salary
       ,department
       ,NTILE(4) OVER (PARTITION BY department ORDER BY salary) AS Quartile
FROM employee;

We will get the following output:

Employee_Name	Salary	Department	Quartile
Emp B	20000	IT	1
Emp C	28000	IT	2
Emp A	10000	Sales	1
Emp H	12000	Sales	1
Emp G	12000	Sales	2
Emp F	20000	Sales	3
Emp E	32000	Sales	4
Emp D	30000	Support	1

LEAD Function in Oracle SQL – PLSQL

NTILE Function in Oracle SQL – PLSQL