Función analítica NTILE

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>

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *