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.