JOIN по диапазону и self-join

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

Историчная таблица: какое значение было тогда

В customers лежит текущий сегмент клиента. В customers_history вся история: у каждой строки период действия [valid_from, valid_to), у последней valid_to = 9999-12-31. Это стандартная схема медленно меняющихся измерений, в модуле про моделирование она называется SCD2.

Вопрос «выручка по сегментам» имеет два разных ответа. По текущему сегменту клиента:

SELECT c.segment, sum(o.total_amount)
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY 1;
-- new | 96 222 170.92, regular | 92 291 249.89, vip | 21 341 599.85

По сегменту, который был у клиента в момент заказа:

SELECT h.segment, sum(o.total_amount)
FROM orders o
JOIN customers_history h
  ON h.customer_id = o.customer_id
 AND o.created_at::date >= h.valid_from
 AND o.created_at::date <  h.valid_to
GROUP BY 1;
-- new | 87 358 779.22, regular | 70 210 952.60, vip | 14 644 861.38

У 516 заказов сегмент на момент покупки отличается от нынешнего. Для отчёта «сколько приносят vip-клиенты» правильный ответ второй: клиент стал vip после этих покупок, и приписывать их сегменту vip неверно.

Три ошибки в соединении по периоду

Забыть условие по дате. JOIN customers_history только по customer_id соединит заказ со всеми периодами клиента: 11 812 строк вместо 6429. Каждый заказ повторяется столько раз, сколько периодов в истории клиента, и все суммы по заказам завышаются.

BETWEEN на границах. Условие o.created_at::date BETWEEN h.valid_from AND h.valid_to включает обе границы. День, в который клиент сменил сегмент, попадёт в оба периода, и заказ этого дня удвоится. В базе таких заказов 8. Схема периодов с исключённой правой границей и полуинтервал >= from AND < to избавляют от этого, как и в уроке про даты.

Тип границы. created_at это timestamptz, а valid_from это date. Сравнение created_at >= valid_from сработает через неявное приведение даты к полуночи UTC, а вот created_at < valid_to уже не то же самое, что «дата заказа меньше valid_to». Приводите к одному типу явно, как в примере выше, и помните про часовой пояс.

Чего история не покрывает

После правильного соединения 6429 заказов нашли период. Заказов с существующим клиентом 7916. Куда делись 1487?

SELECT count(*)
FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id)
  AND NOT EXISTS (
    SELECT 1 FROM customers_history h
    WHERE h.customer_id = o.customer_id
      AND o.created_at::date >= h.valid_from
      AND o.created_at::date <  h.valid_to
  );   -- 1487

История клиента начинается с даты регистрации, а эти заказы созданы раньше. В источнике так бывает: регистрация переписана при миграции, заказ оформлен гостем и привязан позже. Для отчёта нужно решение: отбросить, отнести к первому периоду или к отдельной категории «до регистрации». Любое из них лучше, чем не знать, что 19% заказов выпали.

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

Self-join: таблица с самой собой

Соединение таблицы с собой нужно, когда строки одной таблицы связаны друг с другом. Перекрытия периодов:

SELECT a.customer_id, a.valid_from, a.valid_to, b.valid_from AS next_from
FROM customers_history a
JOIN customers_history b
  ON b.customer_id = a.customer_id
 AND a.valid_from < b.valid_from
 AND a.valid_to   > b.valid_from;
-- 4 строки, например клиент 483: период до 2025-08-26, а следующий начался 2025-08-16

Условие a.valid_from < b.valid_from важно: без него каждая пара найдётся дважды, и период найдёт сам себя.

Повторные покупки в течение недели после предыдущей:

SELECT count(*)
FROM orders a
JOIN orders b
  ON b.customer_id = a.customer_id
 AND b.order_id <> a.order_id
 AND b.created_at >  a.created_at
 AND b.created_at <  a.created_at + interval '7 days';   -- 1713 пар

Self-join по неравенству стоит дорого: для клиента с 215 заказами база проверит 215 × 215 пар. Когда нужен только предыдущий или следующий элемент, оконная функция lag или lead делает то же за один проход по отсортированным данным, это тема следующего блока. Self-join остаётся для случаев, где нужны все пары в интервале.

Соединение по ближайшему значению

Частный случай диапазона: к каждой строке нужна одна ближайшая строка из другой таблицы. Например, цена товара на момент заказа, если история цен хранится с датами изменения. В PostgreSQL для этого есть LATERAL:

SELECT o.order_id, p.price
FROM orders o
CROSS JOIN LATERAL (
  SELECT price FROM price_history ph
  WHERE ph.product_id = o.product_id AND ph.changed_at <= o.created_at
  ORDER BY ph.changed_at DESC
  LIMIT 1
) p;

Подзапрос в LATERAL видит колонки строки слева и выполняется для каждой строки. В нашей базе истории цен нет, зато в order_items лежит цена на момент заказа: у 2669 позиций из 17 956 она отличается от текущей цены в каталоге. Именно поэтому в таблицах фактов фиксируют значения на момент события, а не ссылаются на справочник.

Практика