NTH_VALUE ФУНКЦИЯ

В этом учебном пособии вы узнаете, как использовать 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:

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

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 оператор:

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

DEPARTMENT_ID SECOND HIGHEST THIRD HIGHEST
10 2500 2100
20 3700 3100

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

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

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

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

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

SECOND LOWEST
2500

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

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