Краткий ответ

Для этого применяют оконную функцию LAG. Она возвращает значение из предыдущей строки в заданном порядке, а PARTITION BY ограничивает расчёт текущей группой объекта.

Исторический контекст

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

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

Постановка проблемы

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

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

Подробное решение

LAG получает значение из строки, расположенной раньше текущей в порядке, заданном предложением ORDER BY внутри оконной спецификации. PARTITION BY запускает независимый расчёт для каждой группы, поэтому первая строка каждого объекта не получает значение из предыдущего объекта.

WITH status_log(object_id, event_id, changed_at, status) AS (
    VALUES
        (10, 1, '2024-01-01 09:00', 'new'),
        (10, 2, '2024-01-01 10:00', 'paid'),
        (10, 3, '2024-01-01 11:00', 'shipped'),
        (20, 4, '2024-01-01 09:30', 'new')
)
SELECT
    object_id,
    changed_at,
    status,
    LAG(status) OVER (
        PARTITION BY object_id
        ORDER BY changed_at, event_id
    ) AS previous_status
FROM status_log;

Для первой строки каждой секции LAG возвращает NULL, поскольку предшествующей строки внутри секции нет. Второй аргумент функции может задать смещение, а третий — значение по умолчанию вместо NULL, если предыдущая строка отсутствует.

Порядок должен быть детерминированным: если changed_at не уникален, к нему добавляют event_id или другой уникальный столбец. Функция не изменяет число строк и не объединяет их, в отличие от GROUP BY.

Ситуация из практики

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

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

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

Что кандидаты часто упускают

Что произойдёт, если в оконной спецификации указать ORDER BY, но не указать PARTITION BY?

Все строки будут рассматриваться как одна последовательность. LAG для первой строки общего результата вернёт NULL, а последующие строки могут сравниваться с записями другого объекта. Это корректно только тогда, когда действительно нужна единая глобальная последовательность.
Почему недостаточно сортировать только по времени события?

Если несколько строк имеют одинаковое время, их взаимный порядок может быть не определён. Тогда предыдущая строка для одной из них не имеет однозначного смысла, поэтому добавляют уникальный или гарантированно упорядочивающий ключ.
Как отличить отсутствие предыдущей строки от предыдущего значения, равного NULL?

Обычный результат LAG в обоих случаях будет NULL. Если это различие важно, дополнительно используют ROW_NUMBER для определения первой строки секции либо сохраняют признак наличия предыдущей записи отдельной логикой. Нельзя делать вывод о наличии предыдущей строки только по значению, возвращённому LAG.