advertisements
_____________________________________________________________________________________________________________________
NTILE is an analytic function. This function can be used to group the rows into different buckets depends on the requirement. Suppose you have huge number of rows or records and you wanted to segregate the records into 3 or 4 groups depends on the salary criteria, then you can use this function. NTILE(4) means the entire group of records will be divided into 4 and each record will assign the corresponding bucket number. Example is shown below.
NTILE function argument is a number. If you specify an non integer constant, oracle will truncate the value into number. Nulls will be considered in the last.
Example:
I have 12 records in my emp table.
SQL> select * from emp order by sal;
EMP_NAME DEPT SAL
------------------------------ ---------- ----------
Jain 10 4000
Rupal 10 5000
Hero 10 5500
Roshan 10 5500
Roshan 30 5500
Kiran 30 5500
John 20 6000
Sam 20 6500
Peter 30 6800
Riju 20 7000
King 30 7600
David 10
12 rows selected.
NTILE(3) will split the total records into 3 buckets and gives the corresponding group number to the record.
SQL> select EMP_NAME, SAL, ntile(3) over (order by sal) as Divisions
from emp order by sal;
EMP_NAME SAL DIVISIONS
------------------------------ ---------- ----------
Jain 4000 1
Rupal 5000 1
Hero 5500 1
Roshan 5500 1
Roshan 5500 2
Kiran 5500 2
John 6000 2
Sam 6500 2
Peter 6800 3
Riju 7000 3
King 7600 3
David 3
12 rows selected.
Assigning division the number in the descending order of salary.
SQL> select EMP_NAME, SAL, ntile(3) over (order by sal desc )as Divisions from emp order by sal;
EMP_NAME SAL DIVISIONS
------------------------------ ---------- ----------
Jain 4000 3
Rupal 5000 3
Roshan 5500 3
Roshan 5500 3
Hero 5500 2
Kiran 5500 2
John 6000 2
Sam 6500 2
Peter 6800 1
Riju 7000 1
King 7600 1
David 1
12 rows selected.
SQL> l
1 select EMP_NAME, SAL,
2 ntile(1) over (order by sal)as Div1,
3 ntile(2) over (order by sal)as Div2,
4 ntile(3) over (order by sal)as Div3,
5 ntile(4) over (order by sal)as Div4,
6 ntile(5) over (order by sal)as Div5,
7 ntile(6) over (order by sal)as Div6,
8 ntile(7) over (order by sal)as Div7,
9 ntile(8) over (order by sal)as Div8,
10 ntile(9) over (order by sal)as Div9,
11 ntile(10) over (order by sal)as Div10,
12 ntile(11) over (order by sal)as Div11,
13 ntile(12) over (order by sal)as Div12
14* from emp order by sal
SQL> /
EMP_NA SAL DIV1 DIV2 DIV3 DIV4 DIV5 DIV6 DIV7 DIV8 DIV9 DIV10 DIV11 DIV12
------ ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
Jain 4000 1 1 1 1 1 1 1 1 1 1 1 1
Rupal 5000 1 1 1 1 1 1 1 1 1 1 1 2
Hero 5500 1 1 1 1 1 2 2 2 2 2 2 3
Roshan 5500 1 1 1 2 2 2 2 2 2 2 3 4
Roshan 5500 1 1 2 2 2 3 3 3 3 3 4 5
Kiran 5500 1 1 2 2 2 3 3 3 3 4 5 6
John 6000 1 2 2 3 3 4 4 4 4 5 6 7
Sam 6500 1 2 2 3 3 4 4 4 5 6 7 8
Peter 6800 1 2 3 3 4 5 5 5 6 7 8 9
Riju 7000 1 2 3 4 4 5 5 6 7 8 9 10
King 7600 1 2 3 4 5 6 6 7 8 9 10 11
David 1 2 3 4 5 6 7 8 9 10 11 12
12 rows selected.
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment