NTILE Function in Oracle SQL – PLSQL

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

Tagged , , , , , , , , , , , . Bookmark the permalink.