NTH_VALUE ФУНКЦИЯ — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать Oracle/PLSQL функцию NTH_VALUE с синтаксисом и примерами.

Описание

Oracle/PLSQL функция NTH_VALUE возвращает n-ое значение в упорядоченном наборе значений из аналитического окна. Она похожа на функции FIRST_VALUE и LAST_VALUE, за исключением того, что NTH_VALUE позволяет найти определенную позицию в аналитическом окне, например, 2-е, 3-е или 4-е значение.

Синтаксис

Синтаксис Oracle/PLSQL функции NTH_VALUE:

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

Параметры или аргументы

measure_column Колонка или выражение, которое вы хотите вернуть.

n Это n-е значение measure_column в аналитическом окне, которое вы хотите вернуть.

FROMFIRST|FROMLAST Необязательный. Он определяет, начинается ли вычисление в первой строке аналитического окна или в последней строке аналитического окна. Если этот параметр опущен, по умолчанию используется значение FROM FIRST, которое начинается с первой строки.

RESPECT NULLS | IGNORE NULLS Необязательный. Он определяет, включены ли значения NULL или игнорируются в аналитическом окне. Если этот параметр опущен, значением по умолчанию является RESPECT NULLS, который включает значения NULL.

query_partition_clause Необязательный. Он используется для разделения результатов на группы на основе одного или нескольких выражений.

order_by_clause Необязательный. Он используется для упорядочивания данных в каждом разделе.

windowing_clause Необязательный. Он определяет строки в аналитическом окне для оценки, и важно, чтобы вы использовали правильное окно windowing_clause, или вы можете получить неожиданные результаты. Это может быть значение, такое как:

windowing_clause	Description
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	Последняя строка в окне изменяется с изменением текущей строки (по умолчанию)
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING	Первая строка в окне изменяется с изменением текущей строки
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	Все строки включены в окно независимо от текущей строки

Функция NTH_VALUE возвращает n-е значение в упорядоченном наборе значений из аналитического окна.
Если в окне источника данных меньше n строк, функция NTH_VALUE вернет NULL.
Если параметр n равен NULL, функция NTH_VALUE возвращает ошибку.

DDL/DML для примеров

Если вы хотите следовать этому руководству, используйте DDL для создания таблицы employees и DML для заполнения данных. Затем попробуйте примеры в вашей собственной базе данных!

Получить DDL/DML

Пример

Рассмотрим некоторые примеры функций Oracle NTH_VALUE и рассмотрим, как использовать функцию NTH_VALUE в Oracle/PLSQL.

Второе самое высокое значение DEPARTMENT_ID

В этом примере у нас есть таблица employees со следующими данными:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT_ID
100	Anita	Borg	2500	10
200	Alfred	Aho	3200	10
300	Bill	Gates	2100	10
400	Linus	Torvalds	3700	20
500	Michael	Dell	3100	20
600	Nello	Cristianini	2950	20
700	Rasmus	Lerdorf	4900	20
800	Steve	Jobs	2600	30
900	Thomas	Kyte	5000	30

Теперь продемонстрируем, как работает функция NTH_VALUE, выбрав данные из таблицы employees. Чтобы вернуть DEPARTMENT_ID и вторую самую высокую зарплату для DEPARTMENT_ID 10 и 20, введите следующий SQL-оператор в Oracle:

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

SELECT DISTINCT department_id, NTH_VALUE(salary,2)

OVER (PARTITION BY department_id ORDER BY salary DESC

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

AS "SECOND HIGHEST"

FROM employees

WHERE department_id in (10,20)

ORDER BY department_id;

Вот результаты, которые вы должны получить:

DEPARTMENT_ID	SECOND HIGHEST
10	2500
20	3700

В этом примере NTH_VALUE возвращает второе значение зарплаты, указанное в NTH_VALUE (salary, 2). Аналитическое окно будет делить результаты на department_id и упорядочить данные по зарплате в порядке убывания, как указано PARTITION BY department_id ORDER BY salary DESC. windowing_clause = RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING используется для обеспечения включения всех строк независимо от текущей строки. Если вы не указали параметр windowing_clause в этом примере, вы получите неожиданные результаты.

Вторая и третья самые высокие зарплаты от department_id

Теперь рассмотрим, как использовать функцию NTH_VALUE для возврата 2-й и 3-й самых высоких зарплат для department_id 10 и 20.

На основе той же таблицы employees выполните следующий SQL оператор:

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

SELECT DISTINCT department_id, NTH_VALUE(salary,2)

OVER (PARTITION BY department_id ORDER BY salary DESC

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

AS "SECOND HIGHEST",

NTH_VALUE(salary,3)

OVER (PARTITION BY department_id ORDER BY salary DESC

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

AS "THIRD HIGHEST"

FROM employees

WHERE department_id in (10,20)

ORDER BY department_id;

Вот результаты, которые вы должны получить:

DEPARTMENT_ID	SECOND HIGHEST	THIRD HIGHEST
10	2500	2100
20	3700	3100

В этом примере мы несколько раз использовали функцию NTH_VALUE, чтобы найти разные n-ые значения в таблице employees.

Вторая минимальная зарплата для всех сотрудников

Наконец, воспользуемся функцией NTH_VALUE, чтобы вернуть вторую самую низкую зарплату во всей таблице employees. В этом примере нам не потребуется query_partition_clause.

На основе данных в таблице employee выполните следующий оператор SELECT:

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

SELECT DISTINCT NTH_VALUE(salary,2)

OVER (ORDER BY salary ASC

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

AS "SECOND LOWEST"

FROM employees;

Вот результат, которые вы должны получить:

SECOND LOWEST
2500

В этом примере мы использовали функцию NTH_VALUE, чтобы найти вторую самую низкую зарплату в таблице employees и, таким образом, отсортировали salary (зарплату) в порядке возрастания, чтобы правильно упорядочить данные в аналитическом окне (как указано ORDER BY salary ASC).

И поскольку нам нужна вторая самая низкая зарплата для таблицы (не основанная на department_id), нам не нужно было включать query_partition_clause для разделения данных.