NTH_VALUE in PostgreSQL: the n-th Window Value and Second-Highest per Group

Оконные функции в SQL часто используют, когда нужно добавить к каждой строке значение из соседних строк, но не схлопывать результат через GROUP BY.

Например:

• показать рядом с каждым заказом второй по величине заказ пользователя;
• найти третье событие в истории заказа;
• добавить к каждой строке второй статус в цепочке изменений;
• взять второй максимальный чек внутри страны;
• показать вторую зарплату в рейтинге отдела.

Для таких задач есть функция:

NTH_VALUE(expr, n)

Она возвращает значение expr из n-й строки окна.

На словах всё просто:

NTH_VALUE(amount, 2)

означает:

возьми amount из второй строки окна

Но на практике у NTH_VALUE есть та же ловушка, что и у LAST_VALUE: функция смотрит не просто на всю группу, а на текущий оконный фрейм.

Если фрейм задан неявно, можно получить NULL или значение не из той строки, которую вы ожидали.

Что делает NTH_VALUE простыми словами

NTH_VALUE(expr, n) берёт значение из n-й строки окна.

Например:

NTH_VALUE(amount, 2)

берёт amount из второй строки.

NTH_VALUE(status, 3)

берёт status из третьей строки.

NTH_VALUE(created_at, 1)

берёт created_at из первой строки.

Нумерация начинается с 1, а не с 0.

То есть:

NTH_VALUE(x, 1) = FIRST_VALUE(x)

Если n-й строки в доступном фрейме нет, функция возвращает NULL.

Например, если в группе у пользователя только один заказ, а вы просите второй:

NTH_VALUE(amount, 2)

результатом будет NULL.

Базовый пример

Допустим, есть таблица orders:

id  | user_id | amount | created_at
----+---------+--------+---------------------
101 | 1       | 5000   | 2026-06-01 10:00:00
102 | 1       | 3000   | 2026-06-02 12:00:00
103 | 1       | 1000   | 2026-06-03 09:00:00
201 | 2       | 7000   | 2026-06-01 11:00:00
202 | 2       | 2000   | 2026-06-04 14:00:00

Хотим рядом с каждым заказом показать второй по величине заказ пользователя.

SELECT
  id,
  user_id,
  amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY user_id
    ORDER BY amount DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_highest_amount
FROM orders
ORDER BY user_id, amount DESC;

Результат:

id  | user_id | amount | second_highest_amount
----+---------+--------+----------------------
101 | 1       | 5000   | 3000
102 | 1       | 3000   | 3000
103 | 1       | 1000   | 3000
201 | 2       | 7000   | 2000
202 | 2       | 2000   | 2000

Что здесь происходит:

PARTITION BY user_id

разбивает данные на отдельные окна по пользователям.

ORDER BY amount DESC

сортирует заказы пользователя от самого большого к самому маленькому.

NTH_VALUE(amount, 2)

берёт сумму из второй строки этого отсортированного окна.

А явный фрейм:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

говорит:

смотри на всю партицию целиком

Именно этот фрейм здесь критически важен.

Почему NTH_VALUE — оконная функция, а не агрегат

NTH_VALUE не схлопывает строки.

Он добавляет к каждой строке значение из другой строки окна.

Например, запрос:

SELECT
  id,
  user_id,
  amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY user_id
    ORDER BY amount DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_highest_amount
FROM orders;

оставляет все заказы в результате.

Он не делает одну строку на пользователя. Он просто добавляет к каждому заказу колонку second_highest_amount.

Если нужна одна строка на пользователя, можно потом использовать DISTINCT, подзапрос или другой подход. Но сама оконная функция строки не схлопывает.

Можно запомнить так:

GROUP BY      -- уменьшает количество строк
NTH_VALUE     -- сохраняет строки и добавляет значение из окна

Главная ловушка: оконный фрейм

Самая частая ошибка — написать NTH_VALUE без явного фрейма.

Например:

SELECT
  id,
  user_id,
  amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY user_id
    ORDER BY amount DESC
  ) AS second_highest_amount
FROM orders;

На первый взгляд кажется, что всё правильно.

Есть группа:

PARTITION BY user_id

Есть сортировка:

ORDER BY amount DESC

Есть запрос второго значения:

NTH_VALUE(amount, 2)

Но результат может быть неожиданным.

Для первой строки пользователя NTH_VALUE(amount, 2) вернёт NULL.

Почему?

Потому что при наличии ORDER BY оконный фрейм по умолчанию обычно заканчивается на текущей строке.

Упрощённо его можно представить так:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

То есть:

от начала окна до текущей строки

А не:

от начала окна до конца окна

Как выглядит ошибка на данных

Возьмём заказы пользователя 1, отсортированные по сумме:

amount
------
5000
3000
1000

Если фрейм заканчивается на текущей строке, то для каждой строки доступная рамка такая:

current row 5000 -> frame: 5000
current row 3000 -> frame: 5000, 3000
current row 1000 -> frame: 5000, 3000, 1000

Теперь применяем:

NTH_VALUE(amount, 2)

Результат:

amount | frame              | NTH_VALUE(amount, 2)
-------+--------------------+---------------------
5000   | 5000               | NULL
3000   | 5000, 3000         | 3000
1000   | 5000, 3000, 1000   | 3000

На первой строке во фрейме нет второй строки, поэтому результат NULL.

Функция не ошиблась. Она честно смотрит на текущий фрейм. Просто фрейм не тот, который вы ожидали.

Правильный фрейм для n-го значения всей группы

Если вам нужно n-е значение всей партиции, почти всегда пишите фрейм явно:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Полный запрос:

SELECT
  id,
  user_id,
  amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY user_id
    ORDER BY amount DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_highest_amount
FROM orders;

Теперь фрейм для каждой строки пользователя одинаковый:

5000, 3000, 1000

И NTH_VALUE(amount, 2) везде вернёт 3000.

Главное правило:

Если используете NTH_VALUE для значения по всей группе, явно задавайте фрейм до UNBOUNDED FOLLOWING.

Почему FIRST_VALUE кажется проще

NTH_VALUE похож на FIRST_VALUE и LAST_VALUE.

NTH_VALUE(amount, 1)

по смыслу совпадает с:

FIRST_VALUE(amount)

FIRST_VALUE часто работает ожидаемо даже с фреймом по умолчанию.

Почему?

Потому что фрейм по умолчанию начинается с первой строки:

UNBOUNDED PRECEDING

Даже если он заканчивается на текущей строке, первая строка всё равно доступна.

А вот NTH_VALUE(amount, 2), NTH_VALUE(amount, 3) и LAST_VALUE(amount) зависят от правой границы фрейма.

Если правая граница — текущая строка, нужной n-й строки может ещё не быть во фрейме.

Поэтому:

FIRST_VALUE часто выглядит безопасно
LAST_VALUE и NTH_VALUE требуют явного фрейма

Пример: второй заказ пользователя по времени

Теперь возьмём не сумму, а хронологию.

Нужно рядом с каждым заказом показать сумму второго заказа пользователя.

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

Если у пользователя заказы:

id  | amount | created_at
----+--------+---------------------
101 | 1500   | 2026-06-01 10:00:00
102 | 2300   | 2026-06-03 12:00:00
103 | 900    | 2026-06-05 09:00:00

то second_order_amount будет 2300 для всех трёх строк.

Здесь важно, что мы добавили тай-брейкер:

ORDER BY created_at, id

Если два заказа созданы в одну и ту же секунду, id делает порядок стабильным.

Детерминированный порядок: зачем нужен тай-брейкер

NTH_VALUE основан на позиции.

А значит, порядок должен быть стабильным.

Если две строки имеют одинаковое значение в ORDER BY, база не обязана каждый раз располагать их одинаково.

Например:

id  | user_id | amount | created_at
----+---------+--------+---------------------
101 | 1       | 1500   | 2026-06-01 10:00:00
102 | 1       | 2300   | 2026-06-01 10:00:00

Если написать:

ORDER BY created_at

то непонятно, какой заказ будет первым, а какой вторым.

Для NTH_VALUE это критично: «второе значение» может стать разным между запусками.

Лучше писать:

ORDER BY created_at, id

или:

ORDER BY amount DESC, id

Главное правило:

Если смысл функции основан на позиции строки, ORDER BY должен однозначно упорядочивать строки.

Вторая строка и второе уникальное значение — разные задачи

Есть важная аналитическая ловушка.

Допустим, у пользователя такие суммы заказов:

amount
------
5000
5000
3000
1000

Если написать:

NTH_VALUE(amount, 2) OVER (
  PARTITION BY user_id
  ORDER BY amount DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

результат будет:

5000

Почему?

Потому что вторая строка после сортировки — это тоже 5000.

Но если бизнес спрашивает:

второй уникальный уровень суммы

то ожидаемый результат — 3000.

Это другая задача.

NTH_VALUE берёт n-ю строку. Он не ищет n-е уникальное значение.

Можно запомнить так:

NTH_VALUE(amount, 2) -- вторая строка
DENSE_RANK() = 2     -- второй уникальный уровень

Второй уникальный максимум через DENSE_RANK

Если нужен второй уникальный максимум, используйте DENSE_RANK.

SELECT
  user_id,
  amount AS second_highest_distinct_amount
FROM (
  SELECT
    user_id,
    amount,
    DENSE_RANK() OVER (
      PARTITION BY user_id
      ORDER BY amount DESC
    ) AS rnk
  FROM orders
) s
WHERE rnk = 2;

Для данных:

5000
5000
3000
1000

DENSE_RANK даст:

amount | rnk
-------+----
5000   | 1
5000   | 1
3000   | 2
1000   | 3

И запрос вернёт 3000.

Если строк с суммой 3000 несколько, они все получат rnk = 2.

Это обычно правильно для задач с «местами» и ничьими.

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

Если нужно получить одну строку на пользователя со вторым по величине заказом, можно использовать DISTINCT.

SELECT DISTINCT
  user_id,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY user_id
    ORDER BY amount DESC, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_highest_amount
FROM orders;

Но здесь нужно помнить:

• это вторая физическая строка после сортировки;
• если у пользователя меньше двух заказов, результат будет NULL;
• если есть одинаковые суммы, тай-брейкер id определит, какая строка считается второй.

Иногда понятнее использовать ROW_NUMBER в подзапросе:

WITH ranked_orders AS (
  SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY amount DESC, id
    ) AS rn
  FROM orders
)
SELECT
  user_id,
  amount AS second_highest_amount
FROM ranked_orders
WHERE rn = 2;

Такой вариант возвращает только пользователей, у которых действительно есть вторая строка.

Если нужно сохранить пользователей без второго заказа и показать NULL, потребуется LEFT JOIN с таблицей пользователей.

Третье событие в воронке

NTH_VALUE полезен не только для денег.

Например, есть таблица событий:

user_id | event_name | created_at
--------+------------+---------------------
1       | visit      | 2026-06-01 10:00:00
1       | signup     | 2026-06-01 10:05:00
1       | purchase   | 2026-06-01 10:20:00
2       | visit      | 2026-06-02 11:00:00
2       | signup     | 2026-06-02 11:10:00

Хотим рядом с каждым событием показать третье событие пользователя.

SELECT
  user_id,
  event_name,
  created_at,
  NTH_VALUE(event_name, 3) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS third_event
FROM events
ORDER BY user_id, created_at;

Результат:

user_id | event_name | third_event
--------+------------+------------
1       | visit      | purchase
1       | signup     | purchase
1       | purchase   | purchase
2       | visit      | NULL
2       | signup     | NULL

У пользователя 1 есть третье событие — purchase.

У пользователя 2 только два события, поэтому третьего нет и результат NULL.

NTH_VALUE и NULL

По умолчанию PostgreSQL учитывает NULL.

Это значит, что если n-я строка содержит NULL, функция вернёт NULL.

Например:

user_id | status
--------+---------
1       | created
1       | NULL
1       | paid

Запрос:

SELECT
  user_id,
  status,
  NTH_VALUE(status, 2) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_status
FROM order_status_history;

вернёт NULL как второй статус, потому что во второй строке действительно NULL.

PostgreSQL не пропускает NULL внутри NTH_VALUE автоматически.

Если нужно взять второе непустое значение, надо заранее отфильтровать пустые значения или использовать другой подход.

Второе непустое значение в PostgreSQL

Допустим, нужно получить второй непустой статус пользователя.

Данные:

user_id | status  | created_at
--------+---------+---------------------
1       | created | 2026-06-01 10:00:00
1       | NULL    | 2026-06-01 10:05:00
1       | paid    | 2026-06-01 10:10:00
1       | shipped | 2026-06-02 09:00:00

Если учитывать NULL, второй статус будет NULL.

Но если нужно второе непустое значение, можно сначала убрать NULL, а потом пронумеровать строки.

WITH non_null_statuses AS (
  SELECT
    user_id,
    status,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at
    ) AS rn
  FROM order_status_history
  WHERE status IS NOT NULL
)
SELECT
  user_id,
  status AS second_non_null_status
FROM non_null_statuses
WHERE rn = 2;

Результат:

user_id | second_non_null_status
--------+-----------------------
1       | paid

Это надёжный и понятный способ.

FROM LAST: как взять n-е значение с конца

В стандарте SQL есть идея считать не с начала, а с конца окна.

Например:

второе значение с конца

Но в PostgreSQL стандартная форма FROM LAST для NTH_VALUE не поддерживается как обычный рабочий синтаксис.

Поэтому в PostgreSQL обычно делают проще: меняют направление сортировки.

Например, нужно взять второй самый маленький заказ пользователя.

Можно отсортировать суммы по возрастанию и взять вторую строку:

SELECT
  id,
  user_id,
  amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY user_id
    ORDER BY amount ASC, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_lowest_amount
FROM orders;

А если нужно второе значение с конца по времени, сортировку тоже можно развернуть.

Например, второй последний заказ пользователя:

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

Главная мысль:

В PostgreSQL для подсчёта с конца обычно разворачивают ORDER BY.

Почему NTH_VALUE не всегда лучший инструмент

NTH_VALUE хорош, когда нужно добавить n-е значение окна к каждой строке.

Но если нужна только одна строка на группу, иногда проще использовать ROW_NUMBER.

Например, второй по величине заказ каждого пользователя:

WITH ranked_orders AS (
  SELECT
    user_id,
    id,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY amount DESC, id
    ) AS rn
  FROM orders
)
SELECT
  user_id,
  id,
  amount
FROM ranked_orders
WHERE rn = 2;

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

А NTH_VALUE полезнее, когда нужно оставить все строки и рядом показать n-е значение.

Например:

SELECT
  id,
  user_id,
  amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY user_id
    ORDER BY amount DESC, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_highest_amount
FROM orders;

Здесь все заказы остаются, и к каждому добавляется значение второго по величине заказа пользователя.

NTH_VALUE против LEAD и LAG

NTH_VALUE иногда путают с LEAD и LAG.

LAG берёт значение из предыдущей строки относительно текущей.

LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at)

LEAD берёт значение из следующей строки относительно текущей.

LEAD(amount) OVER (PARTITION BY user_id ORDER BY created_at)

А NTH_VALUE берёт значение из фиксированной позиции внутри фрейма.

NTH_VALUE(amount, 2) OVER (...)

Разница:

LAG / LEAD  -- значение относительно текущей строки
NTH_VALUE   -- значение из фиксированной позиции окна

Пример:

• LAG(amount) для третьего заказа берёт сумму второго заказа;
• NTH_VALUE(amount, 2) для любой строки пользователя берёт сумму второго заказа пользователя, если фрейм охватывает всю партицию.

NTH_VALUE против FIRST_VALUE и LAST_VALUE

NTH_VALUE — более общий вариант.

NTH_VALUE(amount, 1)

это первый элемент.

NTH_VALUE(amount, 2)

это второй элемент.

NTH_VALUE(amount, 3)

это третий элемент.

Для первого значения обычно проще использовать:

FIRST_VALUE(amount)

Для последнего — можно использовать:

LAST_VALUE(amount)

но с явным фреймом.

А для второго, третьего, пятого значения уже нужен NTH_VALUE.

NTH_VALUE в PostgreSQL

В PostgreSQL NTH_VALUE используется как оконная функция:

NTH_VALUE(expression, n) OVER (...)

Пример:

SELECT
  id,
  user_id,
  amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY user_id
    ORDER BY amount DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_highest_amount
FROM orders;

Что важно для PostgreSQL:

• n считается с 1;
• если n-й строки во фрейме нет, результат NULL;
• NULL учитываются как обычные значения;
• стандартные опции IGNORE NULLS и FROM LAST не стоит использовать как рабочий PostgreSQL-синтаксис;
• для подсчёта с конца обычно меняют направление ORDER BY;
• для пропуска NULL обычно фильтруют данные заранее.

NTH_VALUE в MySQL

В MySQL 8+ есть оконная функция NTH_VALUE.

Пример:

SELECT
  id,
  user_id,
  amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY user_id
    ORDER BY amount DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_highest_amount
FROM orders;

Ловушка с фреймом такая же: если не расширить фрейм до конца партиции, можно получить NULL или значение не из той части окна.

В MySQL 5.7 оконных функций нет, поэтому такие задачи приходится решать через подзапросы, переменные или переносить логику в приложение.

Дополнительные опции вроде пропуска NULL или подсчёта с конца лучше проверять по конкретной версии MySQL. На практике часто проще и понятнее использовать ROW_NUMBER в подзапросе.

NTH_VALUE в ClickHouse

В ClickHouse для таких задач часто используют не NTH_VALUE, а другие приёмы:

• row_number() в подзапросе;
• groupArray;
• сортировку массива;
• arrayElement.

Например, второй по величине заказ пользователя можно получить через массив:

SELECT
  user_id,
  arrayElement(arrayReverseSort(groupArray(amount)), 2) AS second_highest_amount
FROM orders
GROUP BY user_id;

Идея:

1. groupArray(amount) собирает суммы в массив;
2. arrayReverseSort(...) сортирует массив по убыванию;
3. arrayElement(..., 2) берёт второй элемент.

Но поведение на пустых массивах, коротких массивах и NULL нужно проверять под вашу версию и настройки ClickHouse.

Часто более явно читается подход через row_number():

WITH ranked_orders AS (
  SELECT
    user_id,
    amount,
    row_number() OVER (
      PARTITION BY user_id
      ORDER BY amount DESC
    ) AS rn
  FROM orders
)
SELECT
  user_id,
  amount AS second_highest_amount
FROM ranked_orders
WHERE rn = 2;

Практические шаблоны

Второй по величине заказ пользователя рядом с каждой строкой

SELECT
  id,
  user_id,
  amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY user_id
    ORDER BY amount DESC, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_highest_amount
FROM orders;

Второй заказ пользователя по времени

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

Третье событие пользователя

SELECT
  user_id,
  event_name,
  created_at,
  NTH_VALUE(event_name, 3) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS third_event
FROM events;

Второй последний заказ через обратную сортировку

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

Второй уникальный максимум через DENSE_RANK

SELECT
  user_id,
  amount AS second_highest_distinct_amount
FROM (
  SELECT
    user_id,
    amount,
    DENSE_RANK() OVER (
      PARTITION BY user_id
      ORDER BY amount DESC
    ) AS rnk
  FROM orders
) s
WHERE rnk = 2;

Вторая физическая строка через ROW_NUMBER

WITH ranked_orders AS (
  SELECT
    user_id,
    id,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY amount DESC, id
    ) AS rn
  FROM orders
)
SELECT
  user_id,
  id,
  amount
FROM ranked_orders
WHERE rn = 2;

Второе непустое значение

WITH non_null_statuses AS (
  SELECT
    user_id,
    status,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at
    ) AS rn
  FROM order_status_history
  WHERE status IS NOT NULL
)
SELECT
  user_id,
  status AS second_non_null_status
FROM non_null_statuses
WHERE rn = 2;

NTH_VALUE через именованное окно

SELECT
  id,
  user_id,
  amount,
  NTH_VALUE(amount, 2) OVER w AS second_highest_amount,
  NTH_VALUE(amount, 3) OVER w AS third_highest_amount
FROM orders
WINDOW w AS (
  PARTITION BY user_id
  ORDER BY amount DESC, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

Частые ошибки

Забыли широкий фрейм

Плохо:

NTH_VALUE(amount, 2) OVER (
  PARTITION BY user_id
  ORDER BY amount DESC
)

Лучше:

NTH_VALUE(amount, 2) OVER (
  PARTITION BY user_id
  ORDER BY amount DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Не добавили тай-брейкер

Нестабильно:

ORDER BY created_at

Лучше:

ORDER BY created_at, id

Перепутали вторую строку и второе уникальное значение

Вторая строка:

NTH_VALUE(amount, 2)

Второй уникальный уровень:

DENSE_RANK() = 2

Ожидали, что NULL будут пропущены

PostgreSQL не пропускает NULL автоматически в NTH_VALUE.

Если нужно второе непустое значение, фильтруйте NULL заранее.

Что важно запомнить

NTH_VALUE(expr, n) возвращает значение expr из n-й строки оконного фрейма.

Пример:

NTH_VALUE(amount, 2) OVER (
  PARTITION BY user_id
  ORDER BY amount DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Главные правила:

• n считается с 1;
• NTH_VALUE(x, 1) совпадает по смыслу с FIRST_VALUE(x);
• функция смотрит на оконный фрейм, а не просто на всю партицию;
• если n-й строки во фрейме нет, результат NULL;
• при ORDER BY фрейм по умолчанию часто заканчивается на текущей строке;
• для n-го значения всей группы нужен фрейм до UNBOUNDED FOLLOWING;
• порядок должен быть детерминированным;
• при одинаковых значениях добавляйте тай-брейкер;
• вторая строка и второе уникальное значение — разные задачи;
• для второго уникального значения используйте DENSE_RANK;
• для одной строки на группу часто проще использовать ROW_NUMBER;
• для пропуска NULL в PostgreSQL обычно нужен предварительный фильтр.

Короткий вывод

NTH_VALUE нужен, когда вы хотите взять значение из конкретной позиции окна.

Например:

NTH_VALUE(amount, 2)

берёт значение из второй строки.

Но главный смысл функции раскрывается только вместе с окном:

NTH_VALUE(amount, 2) OVER (
  PARTITION BY user_id
  ORDER BY amount DESC, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Здесь мы говорим:

для каждого пользователя
отсортируй заказы по сумме от большей к меньшей
возьми amount из второй строки
смотри на всю партицию целиком

Главная мысль:

NTH_VALUE точен только тогда, когда вы явно контролируете сортировку и фрейм.

Если нужен n-й элемент всей группы — пишите широкий фрейм. Если нужно n-е уникальное значение — используйте DENSE_RANK. Если нужна одна строка на группу — часто проще взять ROW_NUMBER.

И всегда помните: для функции, основанной на позиции строки, нестабильный ORDER BY означает нестабильный бизнес-результат.