Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Functions, 63 of 121
For information on syntax and semantics, see "Analytic Functions".
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.
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
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|