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_clauseDescription
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_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT_ID
100AnitaBorg250010
200AlfredAho320010
300BillGates210010
400LinusTorvalds370020
500MichaelDell310020
600NelloCristianini295020
700RasmusLerdorf490020
800SteveJobs260030
900ThomasKyte500030

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

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

DEPARTMENT_IDSECOND HIGHEST
102500
203700

В этом примере 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_IDSECOND HIGHESTTHIRD HIGHEST
1025002100
2037003100

В этом примере мы несколько раз использовали функцию 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 для разделения данных.