AS-OF JOIN: выбор исторического значения

Поиск исторического состояния на момент события

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

Создадим таблицы заказов orders и журнала цен price_history:

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  order_at TEXT NOT NULL
);

CREATE TABLE price_history (
  version_id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  effective_at TEXT NOT NULL,
  unit_price INTEGER NOT NULL
);

INSERT INTO orders (order_id, product_id, order_at) VALUES
  (1, 100, '2024-01-10 10:00:00'),
  (2, 100, '2024-01-01 09:00:00'),
  (3, 200, '2024-01-15 12:00:00');

INSERT INTO price_history (version_id, product_id, effective_at, unit_price) VALUES
  (1, 100, '2024-01-05 00:00:00', 50),
  (2, 100, '2024-01-10 10:00:00', 70),
  (3, 100, '2024-01-10 10:00:00', 80),
  (4, 100, '2024-01-12 00:00:00', 95),
  (5, 200, '2024-01-14 08:00:00', 150);

Таблица orders содержит три строки:

order_id	product_id	order_at
1	100	2024-01-10 10:00:00
2	100	2024-01-01 09:00:00
3	200	2024-01-15 12:00:00

Таблица price_history хранит смену цен во времени:

version_id	product_id	effective_at	unit_price
1	100	2024-01-05 00:00:00	50
2	100	2024-01-10 10:00:00	70
3	100	2024-01-10 10:00:00	80
4	100	2024-01-12 00:00:00	95
5	200	2024-01-14 08:00:00	150

Временные метки хранятся в строковом формате ISO-8601 UTC (YYYY-MM-DD HH:MM:SS), поэтому операторы <, <=, > и >= сравнивают даты в хронологическом порядке.

Посмотрим на заказ 1: он оформлен 2024-01-10 10:00:00 на товар 100. В истории цен для товара 100 есть четыре записи. Запись 4 с датой 2024-01-12 00:00:00 относится к будущему по отношению к заказу. Использовать её нельзя: в момент оформления заказа этой цены ещё не существовало. Подходят только записи, у которых effective_at <= order_at: версии 1, 2 и 3. Среди этих подходящих кандидатов актуальной является самая свежая запись на момент заказа.

Такая операция сопоставления события с последней по времени версией справочника, не превышающей время события, называется AS-OF JOIN (буквально: соединение по состоянию «на момент времени»). В SQLite нет специального оператора ASOF JOIN, поэтому этот механизм реализуется стандартными конструкциями языка. При этом гранулярность результата должна строго соответствовать левой таблице: на каждый order_id в выходе должна остаться ровно одна строка.

Реализация через LEFT JOIN и ранжирование ROW_NUMBER()

Реализация AS-OF JOIN состоит из трёх последовательных шагов.

Сначала соединяем заказы с историей через LEFT JOIN. В условии соединения указываем равенство бизнес-ключа товара и неэквивалентное условие времени: ON o.product_id = p.product_id AND p.effective_at <= o.order_at. Строки из будущего отсекаются сразу в предикате соединения.

Выполним это соединение и посмотрим на сформированные строки-кандидаты:

SELECT 
  o.order_id,
  o.product_id,
  o.order_at,
  p.version_id,
  p.effective_at,
  p.unit_price
FROM orders o
LEFT JOIN price_history p 
  ON o.product_id = p.product_id 
 AND p.effective_at <= o.order_at
ORDER BY o.order_id, p.effective_at DESC, p.version_id DESC;

Результат:

order_id	product_id	order_at	version_id	effective_at	unit_price
1	100	2024-01-10 10:00:00	3	2024-01-10 10:00:00	80
1	100	2024-01-10 10:00:00	2	2024-01-10 10:00:00	70
1	100	2024-01-10 10:00:00	1	2024-01-05 00:00:00	50
2	100	2024-01-01 09:00:00	NULL	NULL	NULL
3	200	2024-01-15 12:00:00	5	2024-01-14 08:00:00	150

Для заказа 1 вернулись три подходящие строки. Запись 4 с ценой 95 не попала в вывод, так как её effective_at (2024-01-12) больше даты заказа. Заказ 2 получил NULL, поскольку до 2024-01-01 записей цен для товара 100 не было вовсе. Заказ 3 нашёл ровно одну предшествующую цену 150.

На втором шаге нужно выделить ровно одну самую свежую запись для каждого заказа. Для этого вычисляем оконную функцию ROW_NUMBER() OVER (PARTITION BY o.order_id ORDER BY p.effective_at DESC, p.version_id DESC) AS rn внутри CTE.

На третьем шаге во внешнем запросе отбираем строки с WHERE rn = 1. Фильтр rn = 1 нельзя поставить в WHERE того же запроса, где вычисляется ROW_NUMBER(), так как по правилам логического порядка выполнения SQL фильтрация WHERE выполняется до вычисления оконных функций.

Итоговый запрос собирает схему целиком:

WITH ranked_candidates AS (
  SELECT 
    o.order_id,
    o.product_id,
    o.order_at,
    p.version_id,
    p.unit_price,
    ROW_NUMBER() OVER (
      PARTITION BY o.order_id 
      ORDER BY p.effective_at DESC, p.version_id DESC
    ) AS rn
  FROM orders o
  LEFT JOIN price_history p 
    ON o.product_id = p.product_id 
   AND p.effective_at <= o.order_at
)
SELECT 
  order_id,
  product_id,
  order_at,
  version_id,
  unit_price
FROM ranked_candidates
WHERE rn = 1
ORDER BY order_id ASC;

Результат:

order_id	product_id	order_at	version_id	unit_price
1	100	2024-01-10 10:00:00	3	80
2	100	2024-01-01 09:00:00	NULL	NULL
3	200	2024-01-15 12:00:00	5	150

Фильтр rn = 1 устранил размножение строк, вернув результат к исходной гранулярности: ровно одна строка на один order_id.

Детерминированный выбор при совпадающих метках времени

В истории изменений нередко встречаются ситуации, когда несколько записей имеют абсолютно одинаковую отметку времени. В таблице price_history записи 2 и 3 имеют одно и то же значение effective_at: '2024-01-10 10:00:00'. Это происходит при технических корректировках или массовой загрузке данных в одну и ту же секунду.

Если отсортировать окно только по времени: ORDER BY p.effective_at DESC, то строки 2 и 3 будут равны с точки зрения компаратора. Порядок строк с одинаковым значением сортировки в SQL не гарантирован. База данных может вернуть первой как версию 2 (цена 70), так и версию 3 (цена 80). Результат аналитического отчёта станет недетерминированным: один и тот же запрос на неизменных данных сможет возвращать разные суммы заказа в зависимости от плана выполнения или внутренней перестройки страниц таблицы.

Порядок физической вставки строк в SQLite также не гарантирует порядок выборки. Единственный способ получить стабильный результат — добавить в секцию ORDER BY уникальный ключ, который разрешит равенство меток времени (tie-breaker).

В нашей схеме первичным ключом истории выступает монотонно возрастающий version_id. Условие ORDER BY p.effective_at DESC, p.version_id DESC сначала находит самые поздние даты, а при совпадении секунд отдаёт приоритет строке с максимальным version_id. Для заказа 1 версия 3 гарантированно получит rn = 1, а версия 2 — rn = 2. Добавление уникального ключа делает ранжирование полностью детерминированным.

Сохранение заказов без истории и ловушка INNER JOIN

При сопоставлении с историей критически важен тип соединения. Если вместо LEFT JOIN написать INNER JOIN, логика поиска сломается на событиях, у которых нет истории в прошлом.

Проверим поведение запроса с INNER JOIN:

WITH ranked_candidates AS (
  SELECT 
    o.order_id,
    o.product_id,
    o.order_at,
    p.version_id,
    p.unit_price,
    ROW_NUMBER() OVER (
      PARTITION BY o.order_id 
      ORDER BY p.effective_at DESC, p.version_id DESC
    ) AS rn
  FROM orders o
  INNER JOIN price_history p 
    ON o.product_id = p.product_id 
   AND p.effective_at <= o.order_at
)
SELECT 
  order_id,
  product_id,
  order_at,
  version_id,
  unit_price
FROM ranked_candidates
WHERE rn = 1
ORDER BY order_id ASC;

Результат:

order_id	product_id	order_at	version_id	unit_price
1	100	2024-01-10 10:00:00	3	80
3	200	2024-01-15 12:00:00	5	150

Заказ 2 полностью выпал из результата. Он был совершен 2024-01-01 09:00:00, а первая запись о цене товара 100 появилась только 2024-01-05 00:00:00. Условие p.effective_at <= o.order_at не нашло ни одной подходящей строки в таблице цен, и INNER JOIN удалил сам заказ. В итоге нарушилась полнота отчёта: витрина потеряла факт совершения заказа.

Такая же потеря строки произойдёт, если оставить LEFT JOIN, но вынести ограничение по времени из блока ON в секцию WHERE:

-- Ошибочный запрос: WHERE отсекает строки с NULL
WITH ranked_candidates AS (
  SELECT 
    o.order_id,
    o.product_id,
    o.order_at,
    p.version_id,
    p.unit_price,
    ROW_NUMBER() OVER (
      PARTITION BY o.order_id 
      ORDER BY p.effective_at DESC, p.version_id DESC
    ) AS rn
  FROM orders o
  LEFT JOIN price_history p 
    ON o.product_id = p.product_id
  WHERE p.effective_at <= o.order_at
)
SELECT 
  order_id,
  product_id,
  order_at,
  version_id,
  unit_price
FROM ranked_candidates
WHERE rn = 1
ORDER BY order_id ASC;

В этом ошибочном запросе соединение ON o.product_id = p.product_id находит для заказа 2 все четыре записи товара 100 из price_history. Однако даты всех этих записей начинаются с 2024-01-05 и относятся к будущему относительно даты заказа 2024-01-01 09:00:00. Условие WHERE p.effective_at <= o.order_at для каждой из четырёх строк даёт FALSE и исключает их из выборки, удаляя заказ целиком. Если бы товар вообще не имел записей в price_history, LEFT JOIN сгенерировал бы строку с NULL, но предикат WHERE с вычислением NULL <= o.order_at вернул бы NULL и точно так же отбросил строку.

В обоих случаях перенос условия в WHERE превращает внешнее соединение во внутреннее.

Предикат p.effective_at <= o.order_at должен находиться именно в предложении ON таблицы price_history. В этом случае заказ 2 остаётся в выборке, получает строку с NULL во всех столбцах правой таблицы, успешно проходит ранжирование с rn = 1 и сохраняет исходную гранулярность таблицы заказов без потери строк.