NTH_VALUE — n-е значение в окне и подводный камень рамки

NTH_VALUE(value, n) — оконная функция, возвращающая значение выражения из n-й строки рамки окна. Полезна, когда нужно достать «второй заказ», «третий по величине платёж» и подобное в разрезе пользователя. Есть в PostgreSQL, SQLite (3.25+) и MySQL 8.0+; в более старых MySQL отсутствует. Главный нюанс — рамка окна по умолчанию обрезает данные до текущей строки, из-за чего функция часто неожиданно возвращает NULL.

Синтаксис

NTH_VALUE(value, n) OVER ( PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )

Примеры использования

Пример 1: Сумма второго заказа пользователя (полная рамка)

SELECT
  user_id, order_id, amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY user_id ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_order_amount
FROM orders;

Результат:

user_id | order_id | amount | second_order_amount
--------+----------+--------+--------------------
     17 |     1001 |    299 |                1990
     17 |     1002 |   1990 |                1990

Пример 2: Тот же запрос без явной рамки — NULL

SELECT
  user_id, amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY user_id ORDER BY created_at
  ) AS nth_default_frame
FROM orders;

Результат:

user_id | amount | nth_default_frame
--------+--------+------------------
     17 |    299 |            (null)
     17 |   1990 |              1990

Пример 3: Третий по величине платёж

SELECT DISTINCT
  NTH_VALUE(amount, 3) OVER (
    ORDER BY amount DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS third_largest
FROM payments;

Результат:

third_largest
-------------
         1990
Порешать SQL-задачи в тренажёре →

Типичная ошибка

Главный подвох — рамка окна. По умолчанию она равна RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, поэтому NTH_VALUE(x, n) отдаёт NULL, пока текущая строка не дошла до n-й. Чтобы получить n-е значение по всему разделу, явно указывай ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Функция есть в PostgreSQL, SQLite (3.25+) и MySQL 8.0+; PostgreSQL не поддерживает модификаторы IGNORE NULLS и FROM LAST.

Где спрашивают на собеседовании

Спрашивают в блоке оконных функций рядом с FIRST_VALUE/LAST_VALUE и LAG/LEAD. Любимый trick-вопрос — почему вернулся NULL (правильный ответ: из-за рамки окна по умолчанию).

FAQ: частые вопросы про NTH_VALUE

Почему NTH_VALUE возвращает NULL?

Почти всегда причина — рамка окна. По умолчанию она заканчивается на текущей строке, поэтому для строк до n-й нужного значения в рамке ещё нет и функция даёт NULL. Добавь ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, чтобы рамка охватывала весь раздел.

Поддерживается ли NTH_VALUE в SQLite и MySQL?

Да, в SQLite начиная с версии 3.25 и в MySQL начиная с 8.0 — вместе с остальными оконными функциями. В MySQL 5.7 и более старых оконных функций нет вовсе, поэтому NTH_VALUE там недоступен. PostgreSQL поддерживает её давно.

Чем NTH_VALUE отличается от FIRST_VALUE и LAST_VALUE?

NTH_VALUE — обобщение: она берёт значение из произвольной n-й строки рамки, тогда как FIRST_VALUE и LAST_VALUE фиксированы на первой и последней. NTH_VALUE(x, 1) эквивалентна FIRST_VALUE(x). Все три одинаково чувствительны к границам рамки окна.

Задачи в тренажёре по теме

Вопросы с собеседований по теме

Смежные темы SQL

Оконные функции · Ранжирование

Другие функции SQL

ROW_NUMBER · RANK · DENSE_RANK · LAG · LEAD · NTILE

Открыть SQL-тренажёр (565 задач) →