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;
Этот вариант возвращает по одной строке для маршрутов, где есть хотя бы две посылки.