Oracle SQL - Analytic Functions NTILE

Introduction

NTILE groups data by sort order into a variable number of percentile groupings.

NTILE function roughly works by dividing the number of rows retrieved into the chosen number of segments.

Then, the percentile is displayed as the segment that the rows fall into.

To know which salaries where in the top 25%, the next 25%, the next 25%, and the bottom 25%, then the NTILE(4) function is used for that ordering (100%/4 = 25%).

The algorithm for the function distributes the values "evenly."

The analytical function NTILE(4) for current salary in emp would be:


SELECT empno, ename, curr_salary, 
  NTILE(4) OVER(ORDER BY curr_salary desc) nt 
FROM  emp;

The range of salaries is broken up into (max - min)/4 for NTILE(4) and the rows are assigned after ranking.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- w w w. ja v  a 2  s .c  om

SQL> create table emp(
  2  empno        Number(3)  NOT NULL,
  3  ename        VARCHAR2(10 BYTE),
  4  hireDate     DATE,
  5  orig_salary  Number(8,2),
  6  curr_salary  Number(8,2),
  7  region       VARCHAR2(1 BYTE)
  8  );
SQL>
SQL> insert into emp values(1,'Alison',DATE '1996-03-21', 45000, NULL,  'E');
SQL> insert into emp values(2,'James',DATE  '1978-12-12', 23000, 32000, 'W');
SQL> insert into emp values(3,'Celia',DATE  '1982-10-24', NULL,  58000, 'E');
SQL> insert into emp values(4,'Robert',DATE '1984-01-15', 31000, NULL,  'W');
SQL> insert into emp values(5,'Linda',DATE  '1987-07-30', NULL,  53000, 'E');
SQL> insert into emp values(6,'David',DATE  '1990-12-31', 78000, NULL,  'W');
SQL> insert into emp values(7,'Jode',DATE  '1996-09-17', 21000, 29000, 'E');
SQL>
SQL> SELECT empno, ename, curr_salary,
  2    NTILE(4) OVER(ORDER BY curr_salary desc) nt
  3  FROM  emp;

     EMPNO ENAME      CURR_SALARY         NT
---------- ---------- ----------- ----------
         1 Alison                          1
         4 Robert                          1
         6 David                           2
         3 Celia            58000          2
         5 Linda            53000          3
         2 James            32000          3
         7 Jode             29000          4

7 rows selected.

SQL>

Related Topics