Question: How does SQL ntile
function work in Oracle?
Answer: Oracle notes that the
ntile function allows easy calculation of tertiles,
quartiles, deciles and other common summary "buckets".
Here is an example of using the ntile function to
compute four buckets in Oracle SQL.
SELECT
*
FROM
EMP
MODEL
PARTITION BY
(
NTILE(4)
OVER (ORDER BY DBMS_RANDOM.VALUE) N
)
DIMENSION BY
(
EMPNO
)
MEASURES
(
ENAME ENAME1,
SAL
SAL1,
ENAME ENAME2,
SAL SAL2
)
(
ENAME2[ANY]=
LAST_VALUE(ENAME1)OVER
(ORDER BY SAL1 RANGE BETWEEN
UNBOUNDED PRECEDING AND 100 PRECEDING),
SAL2[ANY]=
LAST_VALUE(SAL1)OVER
(ORDER BY SAL1 RANGE BETWEEN
UNBOUNDED PRECEDING AND 100 PRECEDING)
);
N EMPNO ENAME1
SAL1 ENAME2
SAL2
-------- ---------- ---------- ---------- ----------
1 7782 CLARK
2450
1 7566 JONES
2975 CLARK
2450
1 7902 FORD
3000 CLARK
2450
1 7839 KING
5000 FORD
3000
2 7369 SMITH
800
2 7900 JAMES
950 SMITH
800
2 7521 WARD
1250 JAMES
950
2 7934 MILLER
1300 JAMES
950
3 7654 MARTIN
1250
3 7499 ALLEN
1600 MARTIN
1250
3 7788 SCOTT
3000 ALLEN
1600
4 7876 ADAMS
1100
4 7844 TURNER
1500 ADAMS
1100
4 7698 BLAKE
2850 TURNER
1500
The table is partitioned in four
random groups. The employee number is chosen as the
dimension. For each employee, the best paid employee 100
below his salary is selected using the LAST_VALUE function.
The function NTILE is used to
initialize the partition. The functions LAST_VALUE are used
in the model rules to assign values to the measures SAL1 and
SAL2.
