FIRST_VALUE and LAST_VALUE in PostgreSQL: First and Last per Partition and the Frame Trap

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

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

Или есть заказы, и мы хотим рядом с каждым заказом показать первый и последний заказ этого пользователя.

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

FIRST_VALUE()
LAST_VALUE()

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

LAST_VALUE берёт значение из последней строки окна.

На словах всё звучит просто. Но у LAST_VALUE есть очень частая ловушка: без явного оконного фрейма он может вернуть не последнее значение группы, а значение текущей строки.

Разберём спокойно.

Что делают FIRST_VALUE и LAST_VALUE

FIRST_VALUE(expression) возвращает значение expression из первой строки окна.

LAST_VALUE(expression) возвращает значение expression из последней строки окна.

Например, есть сотрудники:

id | dept | name | salary
---+------+------+--------
1  | eng  | Anna | 250000
2  | eng  | Bob  | 180000
3  | eng  | Kate | 170000
4  | hr   | Tom  | 160000
5  | hr   | Max  | 120000

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

SELECT
  id,
  dept,
  name,
  salary,
  FIRST_VALUE(name) OVER (
    PARTITION BY dept
    ORDER BY salary DESC
  ) AS top_earner
FROM employees;

Результат:

id | dept | name | salary | top_earner
---+------+------+--------+-----------
1  | eng  | Anna | 250000 | Anna
2  | eng  | Bob  | 180000 | Anna
3  | eng  | Kate | 170000 | Anna
4  | hr   | Tom  | 160000 | Tom
5  | hr   | Max  | 120000 | Tom

Что произошло?

Для каждого отдела PostgreSQL сделал отдельное окно:

PARTITION BY dept

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

ORDER BY salary DESC

А потом FIRST_VALUE(name) взял имя из первой строки этого отсортированного окна.

Для отдела eng первая строка — Anna. Для отдела hr первая строка — Tom.

Базовый синтаксис

Общий вид:

FIRST_VALUE(column) OVER (
  PARTITION BY group_column
  ORDER BY sort_column
)

и:

LAST_VALUE(column) OVER (
  PARTITION BY group_column
  ORDER BY sort_column
)

Где:

• column — значение, которое нужно взять из первой или последней строки;
• PARTITION BY — независимые группы;
• ORDER BY — порядок строк внутри каждой группы.

Без ORDER BY понятия «первый» и «последний» почти теряют смысл.

Например:

FIRST_VALUE(name) OVER (PARTITION BY dept)

Такой запрос технически может выполниться, но результат будет ненадёжным: если нет сортировки, база не обязана считать какую-то строку «первой» по вашему бизнес-смыслу.

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

Для FIRST_VALUE и LAST_VALUE почти всегда нужен явный ORDER BY.

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

Важно понимать: FIRST_VALUE — это не агрегат вроде MIN или MAX в обычном GROUP BY.

Обычный агрегат уменьшает количество строк.

Например:

SELECT
  dept,
  MAX(salary) AS max_salary
FROM employees
GROUP BY dept;

Результат:

dept | max_salary
-----+-----------
eng  | 250000
hr   | 160000

Осталась одна строка на отдел.

А оконная функция сохраняет все строки:

SELECT
  id,
  dept,
  name,
  salary,
  FIRST_VALUE(name) OVER (
    PARTITION BY dept
    ORDER BY salary DESC
  ) AS top_earner
FROM employees;

Результат всё ещё содержит каждого сотрудника, просто рядом появилась дополнительная колонка top_earner.

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

GROUP BY        -- схлопывает строки
Оконная функция -- добавляет контекст к каждой строке

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

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

id  | user_id | amount | created_at
----+---------+--------+---------------------
101 | 1       | 1500   | 2026-06-01 10:00:00
102 | 1       | 2300   | 2026-06-03 12:00:00
103 | 1       | 900    | 2026-06-05 09:00:00
201 | 2       | 700    | 2026-06-02 11:00:00
202 | 2       | 1200   | 2026-06-04 14:00:00

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

SELECT
  id,
  user_id,
  amount,
  created_at,
  FIRST_VALUE(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at
  ) AS first_order_amount
FROM orders
ORDER BY user_id, created_at;

Результат:

id  | user_id | amount | created_at          | first_order_amount
----+---------+--------+---------------------+-------------------
101 | 1       | 1500   | 2026-06-01 10:00:00 | 1500
102 | 1       | 2300   | 2026-06-03 12:00:00 | 1500
103 | 1       | 900    | 2026-06-05 09:00:00 | 1500
201 | 2       | 700    | 2026-06-02 11:00:00 | 700
202 | 2       | 1200   | 2026-06-04 14:00:00 | 700

Для пользователя 1 первый заказ — 101, сумма 1500.

Для пользователя 2 первый заказ — 201, сумма 700.

Все строки остались на месте, но каждая получила контекст: «каким был первый заказ пользователя».

Главная ловушка LAST_VALUE

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

Наивно можно написать так:

SELECT
  id,
  user_id,
  amount,
  created_at,
  LAST_VALUE(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at
  ) AS last_order_amount
FROM orders
ORDER BY user_id, created_at;

Кажется, что LAST_VALUE(amount) должен вернуть сумму последнего заказа пользователя.

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

id  | user_id | amount | created_at          | last_order_amount
----+---------+--------+---------------------+------------------
101 | 1       | 1500   | 2026-06-01 10:00:00 | 1500
102 | 1       | 2300   | 2026-06-03 12:00:00 | 2300
103 | 1       | 900    | 2026-06-05 09:00:00 | 900
201 | 2       | 700    | 2026-06-02 11:00:00 | 700
202 | 2       | 1200   | 2026-06-04 14:00:00 | 1200

LAST_VALUE вернул значение текущей строки.

Почему так?

Потому что дело не только в PARTITION BY и ORDER BY.

Есть ещё одна важная часть окна — фрейм.

Что такое оконный фрейм

Оконная функция работает не всегда по всей партиции.

Она работает по фрейму — части строк внутри окна, доступной для текущей строки.

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

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

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

То есть:

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

Для первой строки фрейм содержит только первую строку.

Для второй строки — первую и вторую.

Для третьей — первую, вторую и третью.

Теперь становится понятно, почему LAST_VALUE возвращает текущую строку.

Если окно заканчивается на текущей строке, то последняя строка внутри фрейма — это и есть текущая строка.

Пример для пользователя 1:

created_at          | amount | frame of current row       | LAST_VALUE
--------------------+--------+----------------------------+-----------
2026-06-01 10:00:00 | 1500   | 1500                       | 1500
2026-06-03 12:00:00 | 2300   | 1500, 2300                 | 2300
2026-06-05 09:00:00 | 900    | 1500, 2300, 900            | 900

LAST_VALUE честно берёт последнее значение фрейма. Просто фрейм не такой, как вы ожидали.

Как правильно использовать LAST_VALUE

Чтобы LAST_VALUE увидел последнюю строку всей партиции, нужно явно расширить фрейм до конца окна.

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

Результат:

id  | user_id | amount | created_at          | last_order_amount
----+---------+--------+---------------------+------------------
101 | 1       | 1500   | 2026-06-01 10:00:00 | 900
102 | 1       | 2300   | 2026-06-03 12:00:00 | 900
103 | 1       | 900    | 2026-06-05 09:00:00 | 900
201 | 2       | 700    | 2026-06-02 11:00:00 | 1200
202 | 2       | 1200   | 2026-06-04 14:00:00 | 1200

Теперь всё правильно.

Фрейм:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

означает:

бери строки от начала партиции до конца партиции.

Теперь LAST_VALUE действительно видит последний заказ пользователя.

Почему FIRST_VALUE обычно меньше страдает

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

Почему?

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

UNBOUNDED PRECEDING

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

Пример:

frame of row 1:  1500
frame of row 2:  1500, 2300
frame of row 3:  1500, 2300, 900

FIRST_VALUE везде видит 1500.

А вот LAST_VALUE смотрит на правый край фрейма, и правый край по умолчанию — текущая строка.

Поэтому главный практический совет:

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

Первый и последний заказ в одном запросе

Часто нужны оба значения: первый и последний заказ пользователя.

Можно написать так:

SELECT
  id,
  user_id,
  amount,
  created_at,
  FIRST_VALUE(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_order_amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_amount
FROM orders
ORDER BY user_id, created_at;

Результат:

id  | user_id | amount | first_order_amount | last_order_amount
----+---------+--------+--------------------+------------------
101 | 1       | 1500   | 1500               | 900
102 | 1       | 2300   | 1500               | 900
103 | 1       | 900    | 1500               | 900
201 | 2       | 700    | 700                | 1200
202 | 2       | 1200   | 700                | 1200

Так мы сохранили все заказы, но добавили к каждой строке контекст:

• сумма первого заказа пользователя;
• сумма последнего заказа пользователя.

WINDOW: чтобы не дублировать окно

Если окно длинное, его можно вынести в отдельное определение через WINDOW.

SELECT
  id,
  user_id,
  amount,
  created_at,
  FIRST_VALUE(amount) OVER w AS first_order_amount,
  LAST_VALUE(amount) OVER w AS last_order_amount
FROM orders
WINDOW w AS (
  PARTITION BY user_id
  ORDER BY created_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY user_id, created_at;

Это удобнее:

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

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

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

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

• сумма первого заказа;
• сумма последнего заказа.

Один вариант — использовать DISTINCT.

SELECT DISTINCT
  user_id,
  FIRST_VALUE(amount) OVER w AS first_order_amount,
  LAST_VALUE(amount) OVER w AS last_order_amount
FROM orders
WINDOW w AS (
  PARTITION BY user_id
  ORDER BY created_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

Почему это работает?

Для всех строк одного пользователя first_order_amount и last_order_amount одинаковые.

Поэтому DISTINCT оставит одну строку на пользователя.

Но это не всегда самый лучший способ для больших запросов. Иногда понятнее использовать DISTINCT ON или подзапросы.

FIRST_VALUE и LAST_VALUE с несколькими колонками

FIRST_VALUE возвращает одно выражение.

Например:

FIRST_VALUE(amount)

вернёт сумму.

FIRST_VALUE(id)

вернёт id.

FIRST_VALUE(created_at)

вернёт дату.

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

SELECT
  id,
  user_id,
  amount,
  created_at,
  FIRST_VALUE(id) OVER w AS first_order_id,
  FIRST_VALUE(amount) OVER w AS first_order_amount,
  FIRST_VALUE(created_at) OVER w AS first_order_at
FROM orders
WINDOW w AS (
  PARTITION BY user_id
  ORDER BY created_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

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

Если в одном месте сортировка будет по created_at, а в другом по amount, результат станет логически сломанным.

Добавляйте тай-брейкер в 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

база не обязана каждый раз ставить 101 раньше 102.

Для стабильного результата добавьте уникальный тай-брейкер:

ORDER BY created_at, id

Пример:

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

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

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

Пример: первый и последний статус заказа

Оконные функции особенно полезны для событийных и аудитных таблиц.

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

order_id | status     | changed_at
---------+------------+---------------------
1        | created    | 2026-06-01 10:00:00
1        | paid       | 2026-06-01 10:05:00
1        | shipped    | 2026-06-02 09:00:00
2        | created    | 2026-06-03 12:00:00
2        | cancelled  | 2026-06-03 12:10:00

Нужно рядом с каждым событием показать начальный и текущий финальный статус заказа.

SELECT
  order_id,
  status,
  changed_at,
  FIRST_VALUE(status) OVER w AS first_status,
  LAST_VALUE(status) OVER w AS last_status
FROM order_status_history
WINDOW w AS (
  PARTITION BY order_id
  ORDER BY changed_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY order_id, changed_at;

Результат:

order_id | status    | first_status | last_status
---------+-----------+--------------+------------
1        | created   | created      | shipped
1        | paid      | created      | shipped
1        | shipped   | created      | shipped
2        | created   | created      | cancelled
2        | cancelled | created      | cancelled

Так мы не теряем историю, но добавляем к каждой строке общий контекст по заказу.

Пример: изменение зарплаты сотрудника

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

employee_id | salary | changed_at
------------+--------+---------------------
1           | 100000 | 2025-01-01
1           | 130000 | 2025-07-01
1           | 160000 | 2026-01-01
2           | 90000  | 2025-03-01
2           | 110000 | 2026-02-01

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

SELECT
  employee_id,
  salary,
  changed_at,
  FIRST_VALUE(salary) OVER w AS initial_salary,
  LAST_VALUE(salary) OVER w AS current_salary
FROM salary_history
WINDOW w AS (
  PARTITION BY employee_id
  ORDER BY changed_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY employee_id, changed_at;

Результат:

employee_id | salary | initial_salary | current_salary
------------+--------+----------------+---------------
1           | 100000 | 100000         | 160000
1           | 130000 | 100000         | 160000
1           | 160000 | 100000         | 160000
2           | 90000  | 90000          | 110000
2           | 110000 | 90000          | 110000

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

LAST_VALUE при сортировке DESC

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

Например:

ORDER BY created_at

означает от старых строк к новым.

Тогда:

• FIRST_VALUE — самый ранний заказ;
• LAST_VALUE — самый поздний заказ.

А если написать:

ORDER BY created_at DESC

порядок будет от новых к старым.

Тогда:

• FIRST_VALUE — самый поздний заказ;
• LAST_VALUE — самый ранний заказ.

Пример:

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

Здесь FIRST_VALUE возвращает последний по времени заказ, потому что сортировка обратная.

Это нормальный приём. Главное — читать функцию вместе с ORDER BY.

Когда вместо LAST_VALUE проще использовать FIRST_VALUE с DESC

Иногда, чтобы избежать путаницы с LAST_VALUE, можно перевернуть сортировку и использовать FIRST_VALUE.

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

Вариант через LAST_VALUE:

LAST_VALUE(amount) OVER (
  PARTITION BY user_id
  ORDER BY created_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Вариант через FIRST_VALUE и обратную сортировку:

FIRST_VALUE(amount) OVER (
  PARTITION BY user_id
  ORDER BY created_at DESC
)

Второй вариант часто проще читать:

отсортируй заказы от новых к старым и возьми первый.

Но если в одном запросе нужны и первый, и последний заказ в одной хронологической логике, лучше использовать общий фрейм и WINDOW.

LAST_VALUE против MAX

Иногда люди используют LAST_VALUE, когда на самом деле нужен обычный MAX.

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

SELECT
  dept,
  MAX(salary) AS max_salary
FROM employees
GROUP BY dept;

Это проще, чем окно.

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

SELECT
  id,
  dept,
  name,
  salary,
  MAX(salary) OVER (PARTITION BY dept) AS max_salary_in_dept
FROM employees;

А FIRST_VALUE(name) нужен, когда нужно значение из строки, которая стоит первой по сортировке.

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

SELECT
  id,
  dept,
  name,
  salary,
  FIRST_VALUE(name) OVER (
    PARTITION BY dept
    ORDER BY salary DESC, id
  ) AS top_earner
FROM employees;

То есть:

• MAX(salary) возвращает максимальное значение зарплаты;
• FIRST_VALUE(name) ORDER BY salary DESC возвращает имя из строки с максимальной зарплатой.

Это разные задачи.

Когда использовать DISTINCT ON в PostgreSQL

Если вам нужна одна строка на группу, в PostgreSQL часто удобен DISTINCT ON.

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

SELECT DISTINCT ON (user_id)
  user_id,
  id AS first_order_id,
  amount AS first_order_amount,
  created_at AS first_order_at
FROM orders
ORDER BY user_id, created_at, id;

Этот запрос возвращает одну строку на user_id.

DISTINCT ON (user_id) оставляет первую строку в каждой группе user_id согласно ORDER BY.

Если нужен последний заказ:

SELECT DISTINCT ON (user_id)
  user_id,
  id AS last_order_id,
  amount AS last_order_amount,
  created_at AS last_order_at
FROM orders
ORDER BY user_id, created_at DESC, id DESC;

Когда выбирать что?

Нужна одна строка на группу         -- DISTINCT ON или GROUP BY
Нужно сохранить все исходные строки -- оконные функции

NTH_VALUE: взять N-ю строку

Рядом с FIRST_VALUE и LAST_VALUE есть функция:

NTH_VALUE(expression, n)

Она берёт значение из N-й строки окна.

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

SELECT
  id,
  user_id,
  amount,
  created_at,
  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;

Как и LAST_VALUE, NTH_VALUE тоже зависит от фрейма.

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

Поэтому для таких задач также лучше явно задавать:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

ROWS и RANGE: почему в примерах лучше ROWS

В примерах выше мы используем:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

ROWS задаёт фрейм по физическим строкам.

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

Для новичков и большинства практических задач с FIRST_VALUE / LAST_VALUE проще и предсказуемее использовать ROWS.

Особенно если вы добавляете уникальный тай-брейкер:

ORDER BY created_at, id

Тогда порядок строк становится однозначным, а фрейм через ROWS читается прямо:

от первой строки партиции до последней строки партиции.

Частая ошибка на ревью

Если вы видите такой код:

LAST_VALUE(score) OVER (
  PARTITION BY team_id
  ORDER BY score DESC
) AS lowest_score

это почти наверняка ошибка.

Без явного фрейма LAST_VALUE будет возвращать значение текущей строки или вести себя не так, как ожидает автор.

Правильнее:

LAST_VALUE(score) OVER (
  PARTITION BY team_id
  ORDER BY score DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS lowest_score

Или можно использовать FIRST_VALUE с обратной сортировкой:

FIRST_VALUE(score) OVER (
  PARTITION BY team_id
  ORDER BY score ASC
) AS lowest_score

Оба варианта имеют смысл. Главное — чтобы намерение было очевидно.

FIRST_VALUE и LAST_VALUE в MySQL

В MySQL 8+ есть оконные функции FIRST_VALUE и LAST_VALUE.

Синтаксис похож:

SELECT
  team_id,
  score,
  LAST_VALUE(score) OVER (
    PARTITION BY team_id
    ORDER BY score DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_score
FROM scores;

Ловушка с фреймом такая же: для LAST_VALUE по всей группе нужно явно указать правую границу:

UNBOUNDED FOLLOWING

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

FIRST_VALUE и LAST_VALUE в ClickHouse

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

Для некоторых задач в ClickHouse есть более короткие агрегатные функции.

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

SELECT
  dept,
  argMax(name, salary) AS top_earner
FROM employees
GROUP BY dept;

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

SELECT
  dept,
  argMin(name, salary) AS lowest_earner
FROM employees
GROUP BY dept;

Но это агрегаты: они возвращают одну строку на группу.

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

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

Лидер отдела по зарплате рядом с каждым сотрудником

SELECT
  id,
  dept,
  name,
  salary,
  FIRST_VALUE(name) OVER (
    PARTITION BY dept
    ORDER BY salary DESC, id
  ) AS top_earner
FROM employees;

Первый заказ пользователя рядом с каждым заказом

SELECT
  id,
  user_id,
  amount,
  created_at,
  FIRST_VALUE(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at, id
  ) AS first_order_amount
FROM orders;

Последний заказ пользователя через LAST_VALUE

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

Последний заказ пользователя через FIRST_VALUE и DESC

SELECT
  id,
  user_id,
  amount,
  created_at,
  FIRST_VALUE(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at DESC, id DESC
  ) AS last_order_amount
FROM orders;

Первый и последний заказ через WINDOW

SELECT
  id,
  user_id,
  amount,
  created_at,
  FIRST_VALUE(amount) OVER w AS first_order_amount,
  LAST_VALUE(amount) OVER w AS last_order_amount
FROM orders
WINDOW w AS (
  PARTITION BY user_id
  ORDER BY created_at, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

Начальный и финальный статус заказа

SELECT
  order_id,
  status,
  changed_at,
  FIRST_VALUE(status) OVER w AS first_status,
  LAST_VALUE(status) OVER w AS last_status
FROM order_status_history
WINDOW w AS (
  PARTITION BY order_id
  ORDER BY changed_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

Одна строка на пользователя через DISTINCT ON

SELECT DISTINCT ON (user_id)
  user_id,
  id AS first_order_id,
  amount AS first_order_amount,
  created_at AS first_order_at
FROM orders
ORDER BY user_id, created_at, id;

Вторая строка окна через 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;

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

FIRST_VALUE и LAST_VALUE берут значение из первой или последней строки окна.

Пример:

FIRST_VALUE(name) OVER (
  PARTITION BY dept
  ORDER BY salary DESC
)

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

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

• функции работают через OVER (...);
• PARTITION BY задаёт независимые группы;
• ORDER BY определяет, что значит «первый» и «последний»;
• без ORDER BY результат почти всегда бессмысленен;
• FIRST_VALUE обычно работает ожидаемо с фреймом по умолчанию;
• LAST_VALUE часто возвращает текущую строку, если не указать фрейм;
• для последнего значения всей группы пишите ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING;
• если есть одинаковые значения сортировки, добавляйте тай-брейкер;
• если нужна одна строка на группу, иногда проще использовать DISTINCT ON, GROUP BY, MIN или MAX;
• если нужно сохранить все строки и добавить контекст — используйте оконные функции.

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

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

Например:

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

покажет первый заказ пользователя.

А для последнего значения важно не забыть фрейм:

LAST_VALUE(amount) OVER (
  PARTITION BY user_id
  ORDER BY created_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

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

PARTITION BY отвечает, внутри какой группы смотрим. ORDER BY отвечает, что считается первым и последним. Фрейм отвечает, какая часть группы доступна функции.

Если в запросе есть LAST_VALUE, но нет явного фрейма до UNBOUNDED FOLLOWING, это повод остановиться и проверить логику. Ошибка не упадёт исключением — она просто вернёт правдоподобное, но неверное значение текущей строки.