FIRST_VALUE и LAST_VALUE

Первое значение раздела: поведение FIRST_VALUE

Для анализа жизненного цикла сущностей часто требуется сопоставить каждое промежуточное событие с начальным состоянием. Создадим таблицу истории обращений в поддержку ticket_events и наполним её шестью записями для двух тикетов:

DROP TABLE IF EXISTS ticket_events;
CREATE TABLE ticket_events (
  event_id INTEGER PRIMARY KEY,
  ticket_id INTEGER NOT NULL,
  status_time TEXT NOT NULL,
  status TEXT NOT NULL,
  assigned_agent TEXT NOT NULL
);

INSERT INTO ticket_events (event_id, ticket_id, status_time, status, assigned_agent) VALUES
  (1, 101, '2023-10-01 09:00:00', 'NEW', 'alice'),
  (2, 101, '2023-10-01 10:30:00', 'IN_PROGRESS', 'bob'),
  (3, 101, '2023-10-01 12:00:00', 'RESOLVED', 'bob'),
  (4, 102, '2023-10-02 08:00:00', 'NEW', 'charlie'),
  (5, 102, '2023-10-02 08:00:00', 'ASSIGNED', 'alice'),
  (6, 102, '2023-10-02 11:00:00', 'CLOSED', 'alice');

Таблица содержит следующие строки:

event_id	ticket_id	status_time	status	assigned_agent
1	101	2023-10-01 09:00:00	NEW	alice
2	101	2023-10-01 10:30:00	IN_PROGRESS	bob
3	101	2023-10-01 12:00:00	RESOLVED	bob
4	102	2023-10-02 08:00:00	NEW	charlie
5	102	2023-10-02 08:00:00	ASSIGNED	alice
6	102	2023-10-02 11:00:00	CLOSED	alice

Оконная функция FIRST_VALUE(expr) возвращает значение выражения expr для первой физической строки текущей оконной рамки. Выполним запрос с рамкой по умолчанию:

SELECT 
  ticket_id,
  status_time,
  event_id,
  status,
  FIRST_VALUE(status) OVER (
    PARTITION BY ticket_id 
    ORDER BY status_time, event_id
  ) AS initial_status
FROM ticket_events
ORDER BY ticket_id, status_time, event_id;

Результат выполнения:

ticket_id	status_time	event_id	status	initial_status
101	2023-10-01 09:00:00	1	NEW	NEW
101	2023-10-01 10:30:00	2	IN_PROGRESS	NEW
101	2023-10-01 12:00:00	3	RESOLVED	NEW
102	2023-10-02 08:00:00	4	NEW	NEW
102	2023-10-02 08:00:00	5	ASSIGNED	NEW
102	2023-10-02 11:00:00	6	CLOSED	NEW

Для всех строк каждого тикета столбец initial_status содержит значение NEW. Это происходит потому, что при наличии ORDER BY и отсутствии явного указания границ окна применяется рамка по умолчанию: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Нижняя граница этой рамки — UNBOUNDED PRECEDING. Она всегда указывает на самую первую строку текущего раздела. Первая строка рамки совпадает с началом раздела при обработке любой строки тикета, поэтому FIRST_VALUE успешно возвращает первое значение на протяжении всего раздела.

Ловушка рамки по умолчанию при использовании LAST_VALUE

По аналогии с FIRST_VALUE функция LAST_VALUE(expr) призвана вычислять граничное значение. Ожидание часто состоит в том, что LAST_VALUE вернёт последнее значение всего раздела. Попробуем получить итоговый статус тикета тем же способом, не задавая рамку явно:

SELECT 
  ticket_id,
  status_time,
  event_id,
  status,
  LAST_VALUE(status) OVER (
    PARTITION BY ticket_id 
    ORDER BY status_time, event_id
  ) AS naive_last_status
FROM ticket_events
ORDER BY ticket_id, status_time, event_id;

Результат запроса:

ticket_id	status_time	event_id	status	naive_last_status
101	2023-10-01 09:00:00	1	NEW	NEW
101	2023-10-01 10:30:00	2	IN_PROGRESS	IN_PROGRESS
101	2023-10-01 12:00:00	3	RESOLVED	RESOLVED
102	2023-10-02 08:00:00	4	NEW	NEW
102	2023-10-02 08:00:00	5	ASSIGNED	ASSIGNED
102	2023-10-02 11:00:00	6	CLOSED	CLOSED

Вместо финального статуса (RESOLVED для тикета 101 и CLOSED для тикета 102) столбец naive_last_status в каждой строке просто дублирует значение status текущей строки.

Причина кроется в правилах стандарта SQL. Функция LAST_VALUE возвращает значение для последней строки текущей рамки, а не всего раздела. Неявная рамка по умолчанию RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW заканчивается на текущей строке (точнее, на последней строке группы с равными значениями ключа сортировки). При расчёте для первой строки тикета 101 рамка видит только строки от начала раздела до текущей позиции — то есть только строку 1. Последней строкой в этой рамке оказывается сама строка 1 со значением NEW.

На строке 2 рамка расширяется до строк 1 и 2, и её последней строкой становится строка 2 со значением IN_PROGRESS. Окно ещё не дошло до конца раздела, поэтому LAST_VALUE физически не может увидеть будущие записи.

Симметричная рамка для обоих концов раздела

Чтобы LAST_VALUE возвращала итоговое значение раздела, верхнюю границу рамки необходимо явно расширить до самого конца раздела. Для этого используется конструкция ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Шаблон одновременного расчёта начального и конечного значений раздела выглядит следующим образом:

SELECT 
  ticket_id,
  status_time,
  event_id,
  status,
  FIRST_VALUE(status) OVER (
    PARTITION BY ticket_id 
    ORDER BY status_time, event_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS initial_status,
  LAST_VALUE(status) OVER (
    PARTITION BY ticket_id 
    ORDER BY status_time, event_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS final_status
FROM ticket_events
ORDER BY ticket_id, status_time, event_id;

Результат выполнения:

ticket_id	status_time	event_id	status	initial_status	final_status
101	2023-10-01 09:00:00	1	NEW	NEW	RESOLVED
101	2023-10-01 10:30:00	2	IN_PROGRESS	NEW	RESOLVED
101	2023-10-01 12:00:00	3	RESOLVED	NEW	RESOLVED
102	2023-10-02 08:00:00	4	NEW	NEW	CLOSED
102	2023-10-02 08:00:00	5	ASSIGNED	NEW	CLOSED
102	2023-10-02 11:00:00	6	CLOSED	NEW	CLOSED

Явное определение ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING фиксирует рамку окна равной всему разделу для каждой строки. В этом случае первая строка рамки — это всегда первая строка тикета, а последняя строка рамки — последняя строка тикета по порядку сортировки. Теперь initial_status и final_status стабильно содержат начальное и финальное состояния на протяжении всей истории тикета.

Разрешение одинаковых временных меток и tie-breaker

Обратим внимание на события тикета 102: строки с event_id 4 и 5 имеют абсолютно одинаковое время 2023-10-02 08:00:00. Если в предложении ORDER BY внутри окна указан только столбец status_time, порядок строк с одинаковым временем стандартом не определён.

Посмотрим на запрос без вторичного ключа сортировки:

SELECT 
  ticket_id,
  status_time,
  status,
  FIRST_VALUE(status) OVER (
    PARTITION BY ticket_id 
    ORDER BY status_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS ambiguous_first_status
FROM ticket_events
WHERE ticket_id = 102
ORDER BY ticket_id, status_time;

Результат в текущем запуске:

ticket_id	status_time	status	ambiguous_first_status
102	2023-10-02 08:00:00	NEW	NEW
102	2023-10-02 08:00:00	ASSIGNED	NEW
102	2023-10-02 11:00:00	CLOSED	NEW

Хотя здесь первой строкой оказалась запись со статусом NEW, реляционная СУБД имеет право вернуть строки с одинаковым временем в любом физическом порядке. Если оптимизатор или план выполнения изменятся, первой строкой раздела может оказаться ASSIGNED, и столбец ambiguous_first_status вернёт ASSIGNED для всех строк.

Чтобы устранить неоднозначность, в ORDER BY внутри OVER добавляют уникальный столбец — tie-breaker. В нашей таблице это первичный ключ event_id:

SELECT 
  ticket_id,
  status_time,
  event_id,
  status,
  FIRST_VALUE(status) OVER (
    PARTITION BY ticket_id 
    ORDER BY status_time, event_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS deterministic_first_status,
  LAST_VALUE(status) OVER (
    PARTITION BY ticket_id 
    ORDER BY status_time, event_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS deterministic_final_status
FROM ticket_events
WHERE ticket_id = 102
ORDER BY ticket_id, status_time, event_id;

Результат:

ticket_id	status_time	event_id	status	deterministic_first_status	deterministic_final_status
102	2023-10-02 08:00:00	4	NEW	NEW	CLOSED
102	2023-10-02 08:00:00	5	ASSIGNED	NEW	CLOSED
102	2023-10-02 11:00:00	6	CLOSED	NEW	CLOSED

При равенстве status_time сортировка по event_id гарантирует, что строка 4 всегда предшествует строке 5. Детерминированный порядок внутри окна в сочетании с явной полной рамкой ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING обеспечивает строго воспроизводимый результат вычислений FIRST_VALUE и LAST_VALUE.