8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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 groups.

SELECT empno,
       ename,
       deptno,
       sal,
       NTILE(5) OVER (ORDER BY sal) AS quintile
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL   QUINTILE
---------- ---------- ---------- ---------- ----------
      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 groups.

SELECT empno,
       ename,
       deptno,
       sal,
       NTILE(2) OVER (PARTITION BY deptno ORDER BY sal) AS group_no
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL   GROUP_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 group.

SELECT empno,
       ename,
       deptno,
       sal,
       NTILE(10) OVER (PARTITION BY deptno ORDER BY sal) AS group_no
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL   GROUP_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 group 2).

SELECT *
FROM   (SELECT empno,
               ename,
               deptno,
               sal,
               NTILE(3) OVER (PARTITION BY deptno ORDER BY sal) AS group_no
        FROM   emp)
WHERE  group_no = 2;

     EMPNO ENAME          DEPTNO        SAL   GROUP_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.

AVG * CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_PROBABILITY
CLUSTER_SET CORR * COUNT * COVAR_POP * COVAR_SAMP *
CUME_DIST DENSE_RANK FEATURE_DETAILS FEATURE_ID FEATURE_SET
FEATURE_VALUE FIRST FIRST_VALUE * LAG LAST
LAST_VALUE * LEAD LISTAGG MATCH_RECOGNIZE MAX *
MEDIAN MIN * NTH_VALUE * NTILE PERCENT_RANK
PERCENTILE_CONT PERCENTILE_DISC PREDICTION PREDICTION_COST PREDICTION_DETAILS
PREDICTION_PROBABILITY PREDICTION_SET RANK RATIO_TO_REPORT REGR_ (Linear Regression) Functions *
ROW_NUMBER STDDEV * STDDEV_POP * STDDEV_SAMP * SUM *
VAR_POP * VAR_SAMP * VARIANCE * String Aggregation Top-N Queries

For more information see:

Hope this helps. Regards Tim...

Back to the Top.