NTH_VALUE

📖 Описание

Функция Oracle SQL NTH_VALUE возвращает значение из n-й строки аналитического окна. Она похожа на FIRST_VALUE и LAST_VALUE, но позволяет выбрать позицию: вторую, третью и так далее.


🧩 Синтаксис

NTH_VALUE(measure_expression, n)
  [FROM FIRST | FROM LAST]
  [RESPECT NULLS | IGNORE NULLS]
  OVER (analytic_clause)

⚙️ Параметры и аргументы

measure_expression — возвращаемое выражение. n должно быть положительным целым числом; при NULL возникает ошибка, а при недостаточном числе строк в окне результат равен NULL.

FROM FIRST начинает отсчёт с начала окна и действует по умолчанию; FROM LAST — с конца. RESPECT NULLS учитывает NULL и действует по умолчанию; IGNORE NULLS пропускает такие значения.

PARTITION BY делит строки на разделы, ORDER BY задаёт порядок, а оконная рамка определяет доступные строки. Возвращается тип исходного выражения.

Оконная рамка Границы
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW От начала раздела до текущей строки и строк с тем же ключом сортировки; рамка по умолчанию
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING От текущей строки и строк с тем же ключом до конца раздела
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING Весь раздел

💻 Данные для примеров

Один раз создайте учебную таблицу в отдельной схеме. Все запросы ниже используют эти девять посылок:

CREATE TABLE demo_parcels (
  parcel_id NUMBER PRIMARY KEY,
  route_id NUMBER,
  weight_kg NUMBER
);
INSERT INTO demo_parcels VALUES (101, 10, 14);
INSERT INTO demo_parcels VALUES (102, 10, 18);
INSERT INTO demo_parcels VALUES (103, 10, 11);
INSERT INTO demo_parcels VALUES (104, 20, 22);
INSERT INTO demo_parcels VALUES (105, 20, 17);
INSERT INTO demo_parcels VALUES (106, 20, 13);
INSERT INTO demo_parcels VALUES (107, 20, 28);
INSERT INTO demo_parcels VALUES (108, 30, 16);
INSERT INTO demo_parcels VALUES (109, 30, 30);
PARCEL_ID ROUTE_ID WEIGHT_KG
101 10 14
102 10 18
103 10 11
104 20 22
105 20 17
106 20 13
107 20 28
108 30 16
109 30 30

Вторая по весу посылка на маршруте

SELECT DISTINCT route_id,
       NTH_VALUE(weight_kg, 2) OVER (
         PARTITION BY route_id ORDER BY weight_kg DESC, parcel_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_weight
FROM demo_parcels
WHERE route_id IN (10, 20)
ORDER BY route_id;
ROUTE_ID SECOND_WEIGHT
10 14
20 22

Полная рамка позволяет получить вторую строку независимо от текущей позиции. Это второе значение по строкам, а не второе различное значение веса: совпадающие веса занимают отдельные позиции.

Окно по умолчанию

SELECT route_id, parcel_id, weight_kg,
       NTH_VALUE(weight_kg, 2) OVER (
         PARTITION BY route_id ORDER BY weight_kg DESC, parcel_id
       ) AS second_weight
FROM demo_parcels
ORDER BY route_id, weight_kg DESC, parcel_id;
ROUTE_ID PARCEL_ID WEIGHT_KG SECOND_WEIGHT
10 102 18 NULL
10 101 14 14
10 103 11 14
20 107 28 NULL
20 104 22 22
20 105 17 22
20 106 13 22
30 109 30 NULL
30 108 16 16

В первой строке каждого раздела второй строки ещё нет в рамке, поэтому результат равен NULL. Выбирайте полное или нарастающее окно в зависимости от задачи; уникальный parcel_id устраняет неоднозначность при одинаковом весе.

Вторая и третья позиции

SELECT DISTINCT route_id,
       NTH_VALUE(weight_kg, 2) OVER (
         PARTITION BY route_id ORDER BY weight_kg DESC, parcel_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_weight,
       NTH_VALUE(weight_kg, 3) OVER (
         PARTITION BY route_id ORDER BY weight_kg DESC, parcel_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS third_weight
FROM demo_parcels
WHERE route_id IN (10, 20)
ORDER BY route_id;
ROUTE_ID SECOND_WEIGHT THIRD_WEIGHT
10 14 11
20 22 17

Вторая по возрастанию позиция во всей таблице

SELECT DISTINCT NTH_VALUE(weight_kg, 2) OVER (
         ORDER BY weight_kg, parcel_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_lowest_weight
FROM demo_parcels;
SECOND_LOWEST_WEIGHT
13

PARTITION BY отсутствует, поэтому все посылки образуют один раздел.

Отсчёт с конца: FROM LAST

SELECT DISTINCT route_id,
       NTH_VALUE(weight_kg, 2) FROM LAST OVER (
         PARTITION BY route_id ORDER BY weight_kg DESC, parcel_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_lowest_weight
FROM demo_parcels
ORDER BY route_id;
ROUTE_ID SECOND_LOWEST_WEIGHT
10 14
20 17
30 30

При сортировке по убыванию вторая строка с конца соответствует второй позиции по возрастанию.

Альтернатива с ROW_NUMBER

SELECT route_id, weight_kg AS second_weight
FROM (
  SELECT route_id, weight_kg,
         ROW_NUMBER() OVER (
           PARTITION BY route_id ORDER BY weight_kg DESC, parcel_id
         ) AS rn
  FROM demo_parcels
)
WHERE rn = 2
ORDER BY route_id;

Этот вариант возвращает по одной строке для маршрутов, где есть хотя бы две посылки.

См. документацию Oracle: NTH_VALUE.