Este artículo ofrece una descripción general de la función analítica NTH_VALUE, que es similar a las funciones analíticas FIRST_VALUE y LAST_VALUE. 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 NTH_VALUE
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 NTH_VALUE
La descripción básica de la función analítica NTH_VALUE se muestra a continuación.
NTH_VALUE (measure_expr, n) [ FROM { FIRST | LAST } ][ { RESPECT | IGNORE } NULLS ] OVER (analytic_clause)
La measure_expr suele ser una columna, con «n» como el desplazamiento desde el límite de la ventana. Usar el valor predeterminado FROM FIRST significa que estamos contando hasta el enésimo valor desde la parte superior de la ventana. Usar FROM LAST significa que estamos contando desde el final de la ventana.
La falta de una cláusula de partición en la cláusula OVER significa que todo el conjunto de resultados se trata como una sola partición, por lo que obtenemos el enésimo salario para todos los empleados, así como todos los datos originales. La función analítica NTH_VALUE es sensible al orden, por lo que realmente no tiene sentido usarla sin un ORDER BY en la cláusula analítica. Recuerde que tan pronto como tenga un ORDER BY en la cláusula analítica, obtendrá la cláusula de ventana predeterminada de RANGE BETWEEN UNBOUNDED PRECEDING Y CURRENT ROW. Debe asegurarse de que esto es lo que necesita o podría obtener un resultado inesperado como se muestra a continuación.
Queremos mostrar los datos sin procesar de los empleados junto con el tercer salario más bajo y el tercero más alto de la empresa. En ambos casos usamos NTH_VALUE (sal, 3), pero en la primera llamada usamos FROM FIRST para indicar que estamos avanzando desde el inicio del resultado establecido. En la segunda llamada, usamos FROM LAST para indicar que estamos avanzando desde los extremos de la configuración de resultados. No hay una cláusula de partición, por lo que toda la tabla se trata como una sola partición.
SELECT empno, ename, deptno, sal, NTH_VALUE(sal, 3) FROM FIRST OVER (ORDER BY sal) AS third_lowest_sal, NTH_VALUE(sal, 3) FROM LAST OVER (ORDER BY sal) AS third_highest_sal FROM emp; EMPNO ENAME DEPTNO SAL THIRD_LOWEST_SAL THIRD_HIGHEST_SAL ---------- ---------- ---------- ---------- ---------------- ----------------- 7369 SMITH 20 800 7900 JAMES 30 950 7876 ADAMS 20 1100 1100 800 7521 WARD 30 1250 1100 1100 7654 MARTIN 30 1250 1100 1100 7934 MILLER 10 1300 1100 1250 7844 TURNER 30 1500 1100 1250 7499 ALLEN 30 1600 1100 1300 7782 CLARK 10 2450 1100 1500 7698 BLAKE 30 2850 1100 1600 7566 JONES 20 2975 1100 2450 7788 SCOTT 20 3000 1100 2975 7902 FORD 20 3000 1100 2975 7839 KING 10 5000 1100 3000 SQL>
La llamada que usa FROM FIRST casi nos da el resultado que esperamos, pero faltan valores para las dos primeras filas. Por el contrario, la llamada que utiliza FROM LAST parece darnos un LAG de 2, en lugar del tercer salario más alto. En ambos casos no obtenemos el resultado que esperamos debido a la cláusula de ventana predeterminada. Lo que queríamos en este caso era lo siguiente.
SELECT empno, ename, deptno, sal, NTH_VALUE(sal, 3) FROM FIRST OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_lowest_sal, NTH_VALUE(sal, 3) FROM LAST OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_highest_sal FROM emp; EMPNO ENAME DEPTNO SAL THIRD_LOWEST_SAL THIRD_HIGHEST_SAL ---------- ---------- ---------- ---------- ---------------- ----------------- 7369 SMITH 20 800 1100 3000 7900 JAMES 30 950 1100 3000 7876 ADAMS 20 1100 1100 3000 7521 WARD 30 1250 1100 3000 7654 MARTIN 30 1250 1100 3000 7934 MILLER 10 1300 1100 3000 7844 TURNER 30 1500 1100 3000 7499 ALLEN 30 1600 1100 3000 7782 CLARK 10 2450 1100 3000 7698 BLAKE 30 2850 1100 3000 7566 JONES 20 2975 1100 3000 7788 SCOTT 20 3000 1100 3000 7902 FORD 20 3000 1100 3000 7839 KING 10 5000 1100 3000 SQL>
Ahora podemos ver que tanto el tercer salario más bajo como el tercero más alto se muestran según sea necesario.
Agregar la cláusula de partición nos permite limitar la búsqueda dentro de una partición. En el siguiente ejemplo, queremos mostrar el segundo salario más pequeño y el segundo más grande, además de los datos de los empleados, por departamento.
SELECT empno, ename, deptno, sal, NTH_VALUE(sal, 2) FROM FIRST OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_lowest_sal, NTH_VALUE(sal, 2) FROM LAST OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest_sal FROM emp; EMPNO ENAME DEPTNO SAL SECOND_LOWEST_SAL SECOND_HIGHEST_SAL ---------- ---------- ---------- ---------- ----------------- ------------------ 7934 MILLER 10 1300 2450 2450 7782 CLARK 10 2450 2450 2450 7839 KING 10 5000 2450 2450 7369 SMITH 20 800 1100 3000 7876 ADAMS 20 1100 1100 3000 7566 JONES 20 2975 1100 3000 7902 FORD 20 3000 1100 3000 7788 SCOTT 20 3000 1100 3000 7900 JAMES 30 950 1250 1600 7521 WARD 30 1250 1250 1600 7654 MARTIN 30 1250 1250 1600 7844 TURNER 30 1500 1250 1600 7499 ALLEN 30 1600 1250 1600 7698 BLAKE 30 2850 1250 1600 SQL>