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

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

Чтобы ответить на вопрос о прошлом, нужна история статусов. Для каждого клиента сохраняем версию с моментом начала valid_from и моментом окончания valid_to. Текущая версия имеет valid_to = NULL. Такой способ хранения версий часто называют SCD Type 2. У заказа есть свой момент ordered_at, и ему подходит только интервал, содержащий этот момент.

Граница интервала решает судьбу заказа

Возьмём интервалы вида [valid_from, valid_to): начало включено, конец исключён. Если в полночь 1 апреля статус сменился с regular на vip, заказ ровно в полночь относится уже к vip. При закрытой с обеих сторон границе один заказ попал бы в две версии.

Вот самодостаточный пример для SQLite. Текстовые отметки времени записаны в одном формате и одной временной зоне, поэтому их лексикографический порядок совпадает с хронологическим. В рабочей схеме лучше использовать тип даты и времени своей СУБД и заранее определить зону.

CREATE TABLE orders (order_id INTEGER, customer_id INTEGER, ordered_at TEXT);
CREATE TABLE customer_status (customer_id INTEGER, valid_from TEXT, valid_to TEXT, status TEXT);
INSERT INTO orders VALUES
  (101, 7, '2025-03-10 09:00:00'),
  (102, 7, '2025-04-01 00:00:00'),
  (103, 8, '2025-03-11 12:00:00');
INSERT INTO customer_status VALUES
  (7, '2025-01-01 00:00:00', '2025-04-01 00:00:00', 'regular'),
  (7, '2025-04-01 00:00:00', NULL, 'vip'),
  (8, '2025-01-01 00:00:00', NULL, 'new');

SELECT o.order_id, s.status
FROM orders AS o
LEFT JOIN customer_status AS s
  ON s.customer_id = o.customer_id
 AND o.ordered_at >= s.valid_from
 AND (o.ordered_at < s.valid_to OR s.valid_to IS NULL)
ORDER BY o.order_id;

Запрос возвращает 101 → regular, 102 → vip, 103 → new. Условия по времени стоят в ON, поэтому LEFT JOIN сохранит и заказ, для которого статус ещё не найден: в столбце status будет NULL. Если перенести их в WHERE, такой заказ исчезнет из результата.

Почему одного условия «последняя запись до заказа» мало

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

Ещё одна опасность — пересекающиеся интервалы одного клиента. Тогда заказ размножится в результате JOIN, а сумма выручки после группировки окажется завышена. Проверьте инвариант: на каждый order_id должно приходиться не больше одной исторической строки. В самой таблице истории версии одного клиента должны идти без пересечений; разрешать ли разрывы, зависит от смысла статуса. Уникальность (customer_id, valid_from) защищает только от одинакового начала, но не от перекрытия интервалов.

Практически такую проверку удобно сделать перед расчётом выручки: обернуть соединение в CTE, сгруппировать его по order_id и вывести те заказы, у которых COUNT(s.customer_id) > 1. Нулевая кратность — другая категория: заказ сохранился из-за LEFT JOIN, но для него нет версии статуса. Отчёт может показать его отдельно как «неизвестный статус», а владелец данных — решить, действительно ли это допустимый разрыв истории.

Если статус обновляют задним числом, важно уточнить вопрос отчёта. «Какой статус действовал в момент заказа по исправленной истории?» и «Какой статус система знала на день формирования старого отчёта?» — разные задачи. Для второй потребуется ещё время записи или исправления версии, то есть история самих изменений истории. Обычный SCD2-интервал по бизнес-времени не восстановит прежнее знание системы.

Для большого объёма заказов индекс по (customer_id, valid_from) помогает искать кандидатов, однако план выполнения и фактическое время запроса стоит проверять на своей СУБД: условие по верхней границе тоже участвует в отборе. В PostgreSQL при необходимости можно хранить временной диапазон отдельным range-типом; смысл границы [) остаётся тем же.

На курсе «Продвинутый SQL» похожая задача продолжается соединениями нескольких источников, восстановлением исторических значений и проверкой дублей в финансовых отчётах. Студент пишет запросы в браузере на SQLite и проверяет их на разных наборах данных, затем собирает аналитическую витрину интернет-магазина. Откройте программу курса, если хотите отработать исторический JOIN вместе с остальными задачами аналитической отчётности.

По теме соединений полезен разбор почему JOIN позиций и оплат раздувает сумму: там та же проверка кратности результата возникает уже между двумя таблицами фактов.