8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
NTILE Analytic Function
This article gives an overview of the NTILE analytic function. If you are new to analytic functions you should probably read this introduction to analytic functions first.
Related articles.
Setup
The examples in this article require the following table.
--DROP TABLE emp PURGE;
CREATE TABLE emp (
empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
NTILE Analytic Function
The NTILE analytic function allows you to break a result set into a specified number of approximately equal groups, or buckets, rows permitting. If the number of rows in the set is smaller than the number of buckets specified, the number of buckets will be reduced so there is one row per bucket.
The basic description for the NTILE analytic function is shown below. The analytic clause is described in more detail here.
NTILE(expr) OVER ([ query_partition_clause ] order_by_clause)
The NTILE analytic function is order-sensitive and produces an error if you attempt to use it without an ORDER BY in the analytic clause. Unlike some other analytic functions, it doesn't support the windowing clause. Omitting a partitioning clause from the OVER clause means the whole result set is treated as a single partition. In the following example we divide the whole result set into five buckets.
SELECT empno,
ename,
deptno,
sal,
NTILE(5) OVER (ORDER BY sal) AS bucket_no
FROM emp;
EMPNO ENAME DEPTNO SAL BUCKET_NO
---------- ---------- ---------- ---------- ----------
7369 SMITH 20 800 1
7900 JAMES 30 950 1
7876 ADAMS 20 1100 1
7521 WARD 30 1250 2
7654 MARTIN 30 1250 2
7934 MILLER 10 1300 2
7844 TURNER 30 1500 3
7499 ALLEN 30 1600 3
7782 CLARK 10 2450 3
7698 BLAKE 30 2850 4
7566 JONES 20 2975 4
7788 SCOTT 20 3000 4
7902 FORD 20 3000 5
7839 KING 10 5000 5
SQL>
Adding the partitioning clause allows us to divide the contents of each partition into buckets. In the following example we divide the employees in each department into two buckets.
SELECT empno,
ename,
deptno,
sal,
NTILE(2) OVER (PARTITION BY deptno ORDER BY sal) AS bucket_no
FROM emp;
EMPNO ENAME DEPTNO SAL BUCKET_NO
---------- ---------- ---------- ---------- ----------
7934 MILLER 10 1300 1
7782 CLARK 10 2450 1
7839 KING 10 5000 2
7369 SMITH 20 800 1
7876 ADAMS 20 1100 1
7566 JONES 20 2975 1
7788 SCOTT 20 3000 2
7902 FORD 20 3000 2
7900 JAMES 30 950 1
7654 MARTIN 30 1250 1
7521 WARD 30 1250 1
7844 TURNER 30 1500 2
7499 ALLEN 30 1600 2
7698 BLAKE 30 2850 2
SQL>
Notice what happens if the number of buckets is in excess of the number of rows per partition.
SELECT empno,
ename,
deptno,
sal,
NTILE(10) OVER (PARTITION BY deptno ORDER BY sal) AS bucket_no
FROM emp;
EMPNO ENAME DEPTNO SAL BUCKET_NO
---------- ---------- ---------- ---------- ----------
7934 MILLER 10 1300 1
7782 CLARK 10 2450 2
7839 KING 10 5000 3
7369 SMITH 20 800 1
7876 ADAMS 20 1100 2
7566 JONES 20 2975 3
7788 SCOTT 20 3000 4
7902 FORD 20 3000 5
7900 JAMES 30 950 1
7654 MARTIN 30 1250 2
7521 WARD 30 1250 3
7844 TURNER 30 1500 4
7499 ALLEN 30 1600 5
7698 BLAKE 30 2850 6
SQL>
We could use NTILE to sample a spread of records, for example dividing each department into three buckets based on salary, and returning those people in the middle bucket (assigned to bucket 2).
SELECT *
FROM (SELECT empno,
ename,
deptno,
sal,
NTILE(3) OVER (PARTITION BY deptno ORDER BY sal) AS bucket_no
FROM emp)
WHERE bucket_no = 2;
EMPNO ENAME DEPTNO SAL BUCKET_NO
---------- ---------- ---------- ---------- ----------
7782 CLARK 10 2450 2
7566 JONES 20 2975 2
7788 SCOTT 20 3000 2
7521 WARD 30 1250 2
7844 TURNER 30 1500 2
SQL>
Quick Links
The "*" indicates the function supports the full analytic syntax, including the windowing clause.
For more information see:
Hope this helps. Regards Tim...