NTILE

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

NTILE is an analytic function. It divides an ordered dataset into a number of buckets indicated by expr and assigns the appropriate bucket number to each row. The buckets are numbered 1 through expr, and expr must resolve to a positive constant for each partition.

The number of rows in the buckets can differ by at most 1. The remainder values (the remainder of number of rows divided by buckets) are distributed 1 per bucket, starting with bucket 1.

If expr is greater than the number of rows, a number of buckets equal to the number of rows will be filled, and the remaining buckets will be empty.

Example

The following example divides the values in the SAL column into 4 buckets. The SAL column has 14 values, so the two extra values (the remainder of 14 / 4) are allocated to buckets 1 and 2, which therefore have one more value than buckets 3 or 4.

SELECT ename, sal, NTILE(4) OVER (ORDER BY sal DESC) AS quartile
   FROM emp;

ENAME             SAL   QUARTILE
---------- ---------- ----------
KING             5000          1
SCOTT            3000          1
FORD             3000          1
JONES            2975          1
BLAKE            2850          2
CLARK            2450          2
ALLEN            1600          2
TURNER           1500          2
MILLER           1300          3
WARD             1250          3
MARTIN           1250          3
ADAMS            1100          4
JAMES             950          4
SMITH             800          4