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>
