NTILE of Analytic Functions in Oracle SQL
NTILE
NTILE function is assigned/divide the order set of rows into number of buckets. Expression is positive constant integer value.
Note:
If expression value is greater than no of rows in table then remaining bucket is empty.
Syntax:
NTITLE (expr) over ( query partition | order by clause)
Examples of NTITLE function:
Divide the employees according to salary column in buckets
SELECT last_name, salary, NTILE(3) OVER (ORDER BY salary ) AS quartile
FROM employees
WHERE department_id = 100
ORDER BY salary, quartile;
LAST_NAME SALARY QUARTILE ---------- ---------- ---------- Popp 6900 1 Sciarra 7700 1 Urman 7800 2 Chen 8200 2 Faviet 9000 3 Greenberg 12008 3
Use partition claused divided catagory by department
SELECT employee_id,
department_id,
salary,
NTILE(3) OVER (PARTITION BY department_id ORDER BY salary) AS bucket_no
FROM employees order by department_id;
EMPLOYEE_ID DEPARTMENT_ID SALARY BUCKET_NO
----------- ------------- ---------- ----------
200 10 4400 1
202 20 6000 1
201 20 13000 2
119 30 2500 1
118 30 2600 1
117 30 2800 2
116 30 2900 2
115 30 3100 3
114 30 11000 3
203 40 6500 1
132 50 2100 1