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