NTILE groups data by putting data into a number of percentile groups.
For example, to know the salaries in each 25%(top 25%, middle 25%, next 25% and bottom 25%).
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- w ww . j a v a 2 s . co m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> select empno, ename, sal, NTILE(4) over (order by sal)
2 from emp;
EMPNO ENAME SAL NTILE(4)OVER(ORDERBYSAL)
---------- ---------- ---------- ------------------------
1 SMITH 800 1
3 WARD 1250 1
5 MARTIN 1250 1
10 TURNER 1500 2
11 ADAMS 1500 2
2 ALLEN 1600 2
7 CLARK 2850 3
6 BLAKE 2850 3
4 JONES 2975 3
9 KING 3000 4
8 SCOTT 3000 4
11 rows selected.
SQL>
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from w ww .j av a 2 s .com
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> select empno,
2 ename,
3 sal,
4 NTILE(2) over (order by sal) n2,
5 NTILE(3) over (order by sal) n3,
6 NTILE(4) over (order by sal) n4
7 from emp;
EMPNO ENAME SAL N2 N3 N4
---------- ---------- ---------- ---------- ---------- ----------
1 SMITH 800 1 1 1
3 WARD 1250 1 1 1
5 MARTIN 1250 1 1 1
10 TURNER 1500 1 1 2
11 ADAMS 1500 1 2 2
2 ALLEN 1600 1 2 2
7 CLARK 2850 2 2 3
6 BLAKE 2850 2 2 3
4 JONES 2975 2 3 3
9 KING 3000 2 3 4
8 SCOTT 3000 2 3 4
11 rows selected.
SQL>
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- w w w.j a v a 2 s .co m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', NULL, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', NULL, 20);
SQL> select empno,
2 ename,
3 sal,
4 NTILE(2) over (order by sal) n2,
5 NTILE(3) over (order by sal) n3,
6 NTILE(4) over (order by sal) n4
7 from emp;
EMPNO ENAME SAL N2 N3 N4
---------- ---------- ---------- ---------- ---------- ----------
3 WARD 1250 1 1 1
5 MARTIN 1250 1 1 1
10 TURNER 1500 1 1 1
2 ALLEN 1600 1 1 2
6 BLAKE 2850 1 2 2
7 CLARK 2850 1 2 2
4 JONES 2975 2 2 3
8 SCOTT 3000 2 2 3
9 KING 3000 2 3 3
1 SMITH 2 3 4
11 ADAMS 2 3 4
11 rows selected.
SQL>
The null values are calculated as a value for the NTILE and
put to the beginning if NULLS FIRST or to the ending if NULLS LAST.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- w w w . j ava 2s .c o m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', NULL, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', NULL, 20);
SQL> select empno,
2 ename,
3 sal,
4 NTILE(2) over (order by sal NULLS LAST) n2,
5 NTILE(3) over (order by sal NULLS LAST) n3,
6 NTILE(4) over (order by sal NULLS LAST) n4
7 from emp;
EMPNO ENAME SAL N2 N3 N4
---------- ---------- ---------- ---------- ---------- ----------
3 WARD 1250 1 1 1
5 MARTIN 1250 1 1 1
10 TURNER 1500 1 1 1
2 ALLEN 1600 1 1 2
6 BLAKE 2850 1 2 2
7 CLARK 2850 1 2 2
4 JONES 2975 2 2 3
8 SCOTT 3000 2 2 3
9 KING 3000 2 3 3
1 SMITH 2 3 4
11 ADAMS 2 3 4
11 rows selected.
SQL>
SQL> select empno,
2 ename,
3 sal,
4 NTILE(2) over (order by sal NULLS FIRST) n2,
5 NTILE(3) over (order by sal NULLS FIRST) n3,
6 NTILE(4) over (order by sal NULLS FIRST) n4
7 from emp;
EMPNO ENAME SAL N2 N3 N4
---------- ---------- ---------- ---------- ---------- ----------
1 SMITH 1 1 1
11 ADAMS 1 1 1
3 WARD 1250 1 1 1
5 MARTIN 1250 1 1 2
10 TURNER 1500 1 2 2
2 ALLEN 1600 1 2 2
7 CLARK 2850 2 2 3
6 BLAKE 2850 2 2 3
4 JONES 2975 2 3 3
9 KING 3000 2 3 4
8 SCOTT 3000 2 3 4
11 rows selected.
SQL>