SCD: история измерений и соединение на момент события

Мира · маркетолог магазина настолок «Кубик»

Хочу решить, в каком городе открыть пункт выдачи. Посмотрела выручку по городам: Москва далеко впереди. Но я знаю, что клиенты переезжают. Это точно выручка Москвы или выручка людей, которые просто сейчас там живут?

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

Паспорт и трудовая книжка

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

У измерений хранилища те же два характера. Их называют типами SCD, медленно меняющихся измерений.

• Тип 1, паспорт. Новое значение перезаписывает старое. В «Кубике» так устроен customers.city: там только текущий город.
• Тип 2, трудовая книжка. Каждое значение отдельной строкой со сроком действия. В «Кубике» это таблица customer_cities.

Есть и другие типы. Тип 0 не меняется никогда, как дата регистрации. Тип 3 хранит одно предыдущее значение в соседней колонке. На практике аналитик чаще всего встречает первые два.

Как выглядит история

Клиентка 238 зарегистрировалась в Иркутске, а в феврале 2026 года переехала в Москву:

SELECT * FROM customer_cities WHERE customer_id = 238;

 customer_id | city    | valid_from             | valid_to
-------------+---------+------------------------+------------------------
         238 | Иркутск | 2025-02-15 14:52:59+00 | 2026-02-20 14:52:59+00
         238 | Москва  | 2026-02-20 14:52:59+00 |

Одна строка на период. valid_from включительно, valid_to уже нет, а у текущего периода valid_to пустой. Период кончается ровно там, где начинается следующий. В customers.city у неё только «Москва».

Всего в «Кубике» 3 534 клиента. У 94 город неизвестен, и в истории их нет. У остальных 3 440 история есть, 140 из них переезжали, по разу.

Соединение на момент события

У клиентки 238 тринадцать заказов. Восемь неотменённых на 15 801 ₽ сделаны в Иркутске, четыре на 10 617 ₽ уже в Москве. Отчёт по customers.city запишет все 26 418 ₽ Москве. Чтобы каждый заказ попал в свой город, заказ соединяют с той строкой истории, чей период накрывает момент заказа:

SELECT o.id, h.city
FROM orders o
JOIN customer_cities h
  ON h.customer_id = o.customer_id
 AND o.created_at >= h.valid_from
 AND (h.valid_to IS NULL OR o.created_at < h.valid_to);

Это соединение на момент события. Условие с valid_to IS NULL нужно, потому что у текущего периода конца нет, а сравнение с NULL даёт NULL, и заказ бы выпал. Полуоткрытый период, «начало включительно, конец нет», гарантирует, что заказ ровно в момент переезда попадёт в один город, а не в два.