Oracle 11gR2 NTILE Analytic Function Usage Syntax

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.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google