Oracle/PLSQL: función NTH_VALUE

Los datos expuestos en este blog, son solo de índole informativo. Por favor realiza siempre una copia de seguridad antes de realizar cualquier cambio en tu proyecto.

Oracle/PLSQL: función NTH_VALUE

Este tutorial de Oracle explica cómo usar Oracle/PLSQL NTH_VALUE Funciones con sintaxis y ejemplos.

describir

La función Oracle/PLSQL NTH_VALUE devuelve el valor n en un conjunto ordenado de valores de la ventana de análisis. Es similar a las funciones FIRST_VALUE y LAST_VALUE, excepto que NTH_VALUE le permite buscar una ubicación específica en la ventana de análisis, como el segundo, tercer o cuarto valor.

sintaxis

La sintaxis de la función NTH_VALUE en Oracle/PLSQL es:

NTH_VALUE (measure_column, n)
 [FROM FIRST | FROM LAST]
 [RESPECT NULLS | IGNORE NULLS]
 OVER ([query_partition_clause] [order_by_clause [windowing_clause]])

parámetro o parámetro

column_column La columna o expresión que desea devolver. n Es el valor n de la columna_medida en la ventana de análisis que desea devolver. desde el principio | desde el último opcional. Determina si el cálculo comienza desde la primera fila de la ventana de análisis o desde la última fila de la ventana de análisis. Si se omite este parámetro, el valor predeterminado es DESDE PRIMERO a partir de la primera línea. Respetar nulos | ignorar nulos opcional. Determina si incluir o ignorar valores NULL en los cálculos de la ventana de análisis. Si se omite este parámetro, el valor predeterminado es RESPECT NULLS, que incluye valores NULL. cláusula_partición_consulta Opcional. Se utiliza para dividir los resultados en grupos basados ​​en una o más expresiones. order_by_clause opcional. Se utiliza para ordenar los datos dentro de cada partición. cláusula_de_ventana

Electivo. Determina qué líneas en la ventana de análisis evaluar, es importante usar la cláusula de ventana correcta, de lo contrario, puede obtener resultados inesperados. Puede ser un valor como:

cláusula_de_ventana describir
El rango entre la línea anterior ilimitada y la línea actual La última línea de la ventana cambia con la línea actual (predeterminado)
El rango entre la línea actual y sin límites a continuación La primera fila de la ventana cambia con la fila actual
Rango entre anverso ilimitado y reverso ilimitado Todas las líneas se incluyen en la ventana, independientemente de la línea actual

devolver los bienes

La función NTH_VALUE devuelve el valor n en un conjunto ordenado de valores de la ventana de análisis.
La función NTH_VALUE devuelve NULL si hay menos de n filas en la ventana de fuente de datos.
Si el parámetro n es NULL, la función NTH_VALUE devuelve un error.

aplicar para

La función NTH_VALUE está disponible en las siguientes versiones de Oracle/PLSQL:

  • Oracle 12c, Oracle 11g Versión 2

Ejemplo de DDL/DML

Si desea seguir este tutorial, obtenga DDL para crear las tablas y DML para completar los datos. ¡Entonces pruebe los ejemplos en su propia base de datos!

ejemplo

Veamos algunos ejemplos de la función NTH_VALUE de Oracle y exploremos cómo usar la función NTH_VALUE en Oracle/PLSQL.

Segundo valor más alto de Dept_id

En este ejemplo NTH_VALUE, tenemos una tabla llamada empleados con los siguientes datos:

EMPLOYEE_ID  LAST_NAME  FIRST_NAME  SALARY  DEPT_ID
-----------  ---------  ----------  ------  -------
       1000  Jackson    Joe         2000    10
       2000  Smith      Jane        3500    10
       3000  Ferguson   Samantha    1900    10
       4000  Reynolds   Allen       4000    20
       5000  Anderson   Paige       3250    20
       6000  Johnson    Derek       2750    20
       7000  Nelson     Sarah       5000    30
       8000  Burke      Russell     1500    30

Ahora demostremos cómo funciona la función NTH_VALUE seleccionando datos de la tabla de empleados. Para devolver el segundo salario más alto para dept_id y dept_id 10 y 20, ingrese la siguiente instrucción SQL en Oracle:

SELECT DISTINCT dept_id, NTH_VALUE(salary,2)
 OVER (PARTITION BY dept_id ORDER BY salary DESC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "SECOND HIGHEST"
FROM employees
WHERE dept_id in (10,20)
ORDER BY dept_id;

Estos son los resultados que deberías ver:

DEPT_ID  SECOND HIGHEST
-------  --------------
     10            2000
     20            3250

En este ejemplo, NTH_VALUE devuelve el segundo valor de salario especificado por NTH_VALUE(salary,2)La ventana de análisis dividirá los resultados por dept_id y ordenará los datos por salario en el orden descendente especificado PARTITION BY dept_id ORDER BY salary DESC. Esta RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING windowing_clause se usa para garantizar que todas las filas se incluyan independientemente de la fila actual. Si no incluye esta cláusula_ventana en este ejemplo, obtendrá algunos resultados inesperados.

Segundo y tercer salario más alto para Dept_id

Ahora le mostraremos cómo usar la función NTH_VALUE para devolver el segundo y tercer salario más alto para dept_id 10 y 20.

Según la misma tabla de empleados, ingrese la siguiente instrucción SQL:

SELECT DISTINCT dept_id, NTH_VALUE(salary,2)
 OVER (PARTITION BY dept_id ORDER BY salary DESC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "SECOND HIGHEST",
 NTH_VALUE(salary,3)
 OVER (PARTITION BY dept_id ORDER BY salary DESC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "THIRD HIGHEST"
FROM employees
WHERE dept_id in (10,20)
ORDER BY dept_id;

Estos son los resultados que deberías ver:

DEPT_ID  SECOND HIGHEST  THIRD HIGHEST
-------  --------------  -------------
     10            2000           1900
     20            3250           2750

En este ejemplo, usamos la función NTH_VALUE varias veces para encontrar diferentes valores enésimos en la tabla de empleados.

Segundo salario más bajo para todos los empleados

Finalmente, usemos la función NTH_VALUE para devolver el segundo salario más bajo en toda la tabla de empleados. En este ejemplo, no necesitamos query_partition_clause.

Con base en los datos de la tabla de empleados, ingrese la siguiente instrucción SELECT:

SELECT DISTINCT NTH_VALUE(salary,2)
 OVER (ORDER BY salary ASC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "SECOND LOWEST"
FROM employees;

Estos son los resultados que deberías ver:

SECOND LOWEST
-------------
         1900

En este ejemplo, usamos la función NTH_VALUE para encontrar el segundo salario más bajo en la tabla de empleados, por lo que ordenar el salario en orden ascendente ordena correctamente los datos en la ventana de análisis (como se especifica ORDER BY salary ASC).

Y como queremos que la tabla tenga el segundo salario más bajo (no basado en dept_id), no necesitamos incluir query_partition_clause para particionar los datos.

(Visited 5 times, 1 visits today)