Este artículo ofrece una descripción general de la función analítica NTILE. Si es nuevo en las funciones analíticas, probablemente debería leer primero esta introducción a las funciones analíticas.
- Preparar
- Función Analítica NTILE
Preparar
Los ejemplos de este artículo requieren la siguiente tabla.
--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;
Función analítica NTILE
La función analítica NTILE le permite dividir un conjunto de resultados en un número específico de grupos aproximadamente iguales, o cubos, si las filas lo permiten. Si el número de filas del conjunto es menor que el número de depósitos especificados, el número de depósitos se reducirá, por lo que habrá una fila por depósito.
La descripción básica de la función analítica NTILE se muestra a continuación.
NTILE(expr) OVER ([ query_partition_clause ] order_by_clause)
La función analítica NTILE es sensible al orden y produce un error si intenta utilizarla sin un ORDER BY en la cláusula analítica. A diferencia de otras funciones analíticas, no admite la cláusula de ventana. Omitir una cláusula de partición de la cláusula OVER significa que todo el conjunto de resultados se trata como una sola partición. En el siguiente ejemplo, dividimos todo el conjunto de resultados en cinco cubos.
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>
Agregar la cláusula de partición nos permite dividir el contenido de cada partición en cubos. En el siguiente ejemplo, dividimos a los empleados de cada departamento en dos categorías.
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>
Observe lo que sucede si el número de depósitos supera el número de filas por partición.
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>
Podríamos usar NTILE para muestrear una variedad de registros, por ejemplo, dividiendo cada departamento en tres grupos según el salario y devolviendo a esas personas en el grupo intermedio (asignado al grupo 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>
