Función analítica NTH_VALUE

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>

1 comentario en “Función analítica NTH_VALUE”

Deja un comentario

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