SCD2 в SQL: значение на дату и соединение по интервалу

Короткий ответ: условие «действует в момент t»

В таблице SCD2 строка — период, когда значение действовало: valid_from — начало, valid_to — конец, пустой конец — «без даты окончания». Запись действует в момент t, если valid_from <= t AND t < COALESCE(valid_to, TIMESTAMP '9999-12-31'). Начало в интервал входит, конец — нет.

Чтобы приписать значение факту, то же условие ставят в ON: ключ совпадает, момент события попадает в интервал. Три решения остаются за вами: какой момент брать (покупку или вылет), как сохранить факты без истории (LEFT JOIN) и что делать, когда действуют две записи сразу (оставить одну через ROW_NUMBER).

Запрос ниже отвечает, сколько стоил перелёт Домодедово → Казань в полдень 10 августа 2026 года: бизнес — 21 500 ₽, эконом — 7 200 ₽. Конец обеих записей — 1 сентября: следующая цена уже утверждена и лежит в той же таблице.

SELECT fare_conditions, price, valid_from, valid_to
FROM fare_price_history
WHERE departure_airport = 'DME' AND arrival_airport = 'KZN'
  AND valid_from <= TIMESTAMP '2026-08-10 12:00'
  AND TIMESTAMP '2026-08-10 12:00' < COALESCE(valid_to, TIMESTAMP '9999-12-31')
ORDER BY fare_conditions;

Что такое SCD2 и чем тип 2 отличается от типа 1

SCD — slowly changing dimension, медленно меняющееся измерение: атрибут справочника, который изредка меняется, — уровень клиента, тариф, менеджер аккаунта. Тип 1 перезаписывает значение, и прошлое исчезает. Тип 2 закрывает прежнюю строку и добавляет новую со своим valid_from.

В учебной базе авиакомпании таких таблиц две: loyalty_tier_history — уровень участника программы лояльности, fare_price_history — тариф на направление и класс. Эконом Домодедово → Казань с января 2025 года пересматривали четыре раза — от 6 300 ₽ до нынешних 7 200 ₽, один раз вниз, — а с 1 сентября утверждены 7 500 ₽. При типе 1 от этого ряда осталось бы одно число.

Ключи версий, загрузка и поздние исправления — работа инженера данных; о них — в разборе собеседования Data Engineer. Аналитику достаётся готовая история, и её нужно правильно прочитать.

Полуоткрытый интервал и ловушка BETWEEN на стыке

Интервал [valid_from, valid_to) устроен так, чтобы на стыке двух записей действовала ровно одна: конец прежней равен началу следующей и сам в интервал не входит. На шкале ниже — участник 29086: basic с 1 июля, когда в программе появились уровни, silver с 6 августа.

У него четыре покупки, а LEFT JOIN по интервалу возвращает пять строк. Покупка 25 июня сделана до появления уровней — в tier пусто, и это верный ответ, а не пропуск. Покупка 7 августа соединилась с двумя записями: basic закрыли 8 августа, на два дня позже, чем начался silver. Это дефект учебных данных; что с ним делать — в ловушке 4.

**Ловушка 1: BETWEEN включает оба конца.** 1 июня 2026 года в 00:00 часть тарифов пересмотрели. Тарифов из Домодедово в эту полночь действует 78 — по одному на направление и класс. С BETWEEN получается 105: у 27 тарифов засчитаны и старая цена, и новая. С двумя строгими сравнениями — 51: записи, начавшиеся ровно в полночь, не прошли.

Ошибка видна, только когда t совпадает с границей. А границы стоят на полуночи и первом числе месяца — там же, где строят отчёты.

WITH purchases AS (
  SELECT tm.member_id, b.book_ref, b.book_date, sum(tf.amount) AS amount
  FROM ticket_members AS tm
  JOIN tickets AS t ON t.ticket_no = tm.ticket_no
  JOIN bookings AS b ON b.book_ref = t.book_ref
  JOIN ticket_flights AS tf ON tf.ticket_no = tm.ticket_no
  WHERE tm.member_id = 29086
  GROUP BY tm.member_id, b.book_ref, b.book_date
)
SELECT p.book_ref, p.book_date, p.amount, h.tier, h.valid_from, h.valid_to
FROM purchases AS p
LEFT JOIN loyalty_tier_history AS h
  ON h.member_id = p.member_id
 AND p.book_date >= h.valid_from
 AND p.book_date < COALESCE(h.valid_to, TIMESTAMP '9999-12-31')
ORDER BY p.book_date, h.valid_from;

SELECT count(*) FILTER (
         WHERE valid_from <= TIMESTAMP '2026-06-01 00:00'
           AND TIMESTAMP '2026-06-01 00:00' < COALESCE(valid_to, TIMESTAMP '9999-12-31')
       ) AS half_open,
       count(*) FILTER (
         WHERE TIMESTAMP '2026-06-01 00:00'
               BETWEEN valid_from AND COALESCE(valid_to, TIMESTAMP '9999-12-31')
       ) AS with_between,
       count(*) FILTER (
         WHERE valid_from < TIMESTAMP '2026-06-01 00:00'
           AND TIMESTAMP '2026-06-01 00:00' < COALESCE(valid_to, TIMESTAMP '9999-12-31')
       ) AS both_strict
FROM fare_price_history
WHERE departure_airport = 'DME';

Уровень участника на дату покупки: соединение по интервалу

Вернёмся к вопросу маркетинга. Рейсы с вылетом с 27 июля по 9 августа: 52 513 сегментов билетов участников на 1 037,2 млн ₽. Маркетинг оценивает, как уровень связан с покупкой, поэтому уровень нужен на момент брони — book_date.

Запрос идёт в два шага. Первый собирает сегменты: участник, момент брони, цена. Период отфильтрован до встречи с историей: соединение по интервалу работает с 52 тысячами строк, а не с миллионом. Второй присоединяет историю по участнику и интервалу. ROW_NUMBER нумерует найденные записи от поздней к ранней, rn = 1 оставляет одну на сегмент.

Почти вся выручка — на basic: уровни ввели 1 июля, и к моменту покупки мало кто успел подняться. Silver — 467 сегментов на 6,8 млн ₽, gold — 13 сегментов. Ещё 1 009 сегментов куплены, когда уровня у участника не было.

WITH seg AS (
  -- шаг 1: сегменты участников на рейсах двух недель
  SELECT tm.member_id, tf.ticket_no, tf.flight_id, b.book_date, tf.amount
  FROM flights AS f
  JOIN ticket_flights AS tf ON tf.flight_id = f.flight_id
  JOIN ticket_members AS tm ON tm.ticket_no = tf.ticket_no
  JOIN tickets AS t ON t.ticket_no = tf.ticket_no
  JOIN bookings AS b ON b.book_ref = t.book_ref
  WHERE f.scheduled_departure >= TIMESTAMP '2026-07-27'
    AND f.scheduled_departure < TIMESTAMP '2026-08-10'
), with_tier AS (
  -- шаг 2: запись истории, действовавшая в момент брони
  SELECT seg.amount, h.tier,
         row_number() OVER (PARTITION BY seg.ticket_no, seg.flight_id
                            ORDER BY h.valid_from DESC) AS rn
  FROM seg
  LEFT JOIN loyalty_tier_history AS h
    ON h.member_id = seg.member_id
   AND seg.book_date >= h.valid_from
   AND seg.book_date < COALESCE(h.valid_to, TIMESTAMP '9999-12-31')
)
SELECT COALESCE(tier, 'уровня ещё не было') AS tier_at_booking,
       count(*) AS segments, sum(amount) AS revenue
FROM with_tier
WHERE rn = 1
GROUP BY tier
ORDER BY revenue DESC;

Ловушка 2: текущий уровень, приписанный прошлым покупкам

Самый короткий запрос — взять запись с valid_to IS NULL. Он отвечает на другой вопрос: какой уровень у участника сегодня. Тем же сегментам он приписывает 10 928 сегментов silver на 206,4 млн ₽ и 1 161 сегмент platinum, хотя в момент брони платинового уровня не было ни у одного покупателя.

Есть и третий вариант — уровень на дату вылета: silver и выше дают 110,0 млн ₽. Это не ошибка, а другой вопрос: «на каком уровне летел», а не «на каком покупал». Момент выбирают по смыслу метрики и записывают в её определении.

У запроса с valid_to IS NULL есть вторая беда: он вернул 52 883 строки на 52 513 сегментов. У 105 участников из выборки в истории две незакрытые записи, и их 370 сегментов посчитаны дважды — выручка завышена на 6,7 млн ₽.

WITH seg AS (
  SELECT tm.member_id, tf.amount
  FROM flights AS f
  JOIN ticket_flights AS tf ON tf.flight_id = f.flight_id
  JOIN ticket_members AS tm ON tm.ticket_no = tf.ticket_no
  WHERE f.scheduled_departure >= TIMESTAMP '2026-07-27'
    AND f.scheduled_departure < TIMESTAMP '2026-08-10'
)
SELECT h.tier AS current_tier, count(*) AS segments, sum(seg.amount) AS revenue
FROM seg
JOIN loyalty_tier_history AS h
  ON h.member_id = seg.member_id AND h.valid_to IS NULL
GROUP BY h.tier
ORDER BY revenue DESC;

Ловушки 3 и 4: потерянные и размноженные строки

Соединение с историей может и потерять строки, и добавить лишние — одновременно, так что итог выглядит правдоподобно. Проверка одна: число строк до и после.

**Ловушка 3: INNER JOIN.** У 1 009 сегментов на 23,7 млн ₽ записи в истории нет: бронь оформлена до 1 июля. Обычный JOIN выбросит их молча, и сумма по уровням перестанет сходиться с общей выручкой. LEFT JOIN сохраняет строку, COALESCE(tier, …) даёт ей подпись.

Ловушка 4: пересечения. После соединения строк 52 521 — на 8 больше, чем сегментов: восемь сегментов попали в нахлёст двух записей, тот же случай, что у участника 29086. Без rn = 1 выручка выросла бы на 81 400 ₽. Сумма небольшая, но отчёт перестанет сходиться с общей выручкой.

«Берём позднюю запись» — договорённость, а не свойство данных: её называют в отчёте.

WITH seg AS (
  SELECT tm.member_id, tf.ticket_no, tf.flight_id, b.book_date, tf.amount
  FROM flights AS f
  JOIN ticket_flights AS tf ON tf.flight_id = f.flight_id
  JOIN ticket_members AS tm ON tm.ticket_no = tf.ticket_no
  JOIN tickets AS t ON t.ticket_no = tf.ticket_no
  JOIN bookings AS b ON b.book_ref = t.book_ref
  WHERE f.scheduled_departure >= TIMESTAMP '2026-07-27'
    AND f.scheduled_departure < TIMESTAMP '2026-08-10'
), joined AS (
  SELECT seg.amount, h.tier
  FROM seg
  LEFT JOIN loyalty_tier_history AS h
    ON h.member_id = seg.member_id
   AND seg.book_date >= h.valid_from
   AND seg.book_date < COALESCE(h.valid_to, TIMESTAMP '9999-12-31')
)
SELECT (SELECT count(*) FROM seg) AS segments,
       count(*) AS rows_after_join,
       count(*) FILTER (WHERE tier IS NULL) AS rows_without_tier,
       sum(amount) FILTER (WHERE tier IS NULL) AS revenue_without_tier,
       count(*) - (SELECT count(*) FROM seg) AS extra_rows,
       sum(amount) - (SELECT sum(amount) FROM seg) AS extra_revenue
FROM joined;

Ловушка 5: тариф на дату вылета вместо даты брони

Цена сегмента фиксируется при покупке, поэтому тариф ищут на book_date. Проверим рейсы первой недели сентября: не продан ли какой-то из 53 594 сегментов дешевле тарифа. По тарифу на дату брони — ни один: 50 784 проданы ровно по тарифу, 2 810 дороже. Все 2 810 — эконом: в базе часть мест эконома продаётся с наценкой.

Добавьте в seg вылет по расписанию, f.scheduled_departure, и поставьте его в ON вместо seg.book_date — 13 178 сегментов окажутся «дешевле тарифа», в сумме на 15,0 млн ₽. Скидок не было. С 1 сентября на части направлений действует повышенный тариф, а билеты на эти рейсы куплены в июле и августе по прежнему.

WITH seg AS (
  -- сегменты рейсов первой недели сентября: маршрут, класс, цена, момент брони
  SELECT f.departure_airport, f.arrival_airport, tf.fare_conditions,
         tf.amount, b.book_date
  FROM flights AS f
  JOIN ticket_flights AS tf ON tf.flight_id = f.flight_id
  JOIN tickets AS t ON t.ticket_no = tf.ticket_no
  JOIN bookings AS b ON b.book_ref = t.book_ref
  WHERE f.scheduled_departure >= TIMESTAMP '2026-09-01'
    AND f.scheduled_departure < TIMESTAMP '2026-09-08'
)
SELECT count(*) AS segments,
       count(*) FILTER (WHERE seg.amount < fp.price) AS below_tariff,
       count(*) FILTER (WHERE seg.amount = fp.price) AS at_tariff,
       count(*) FILTER (WHERE seg.amount > fp.price) AS above_tariff
FROM seg
JOIN fare_price_history AS fp
  ON fp.departure_airport = seg.departure_airport
 AND fp.arrival_airport = seg.arrival_airport
 AND fp.fare_conditions = seg.fare_conditions
 AND seg.book_date >= fp.valid_from
 AND seg.book_date < COALESCE(fp.valid_to, TIMESTAMP '9999-12-31');

Ловушка 6: открытая запись — не всегда текущая

Привычка подсказывает: текущая цена — там, где valid_to IS NULL. В таблице тарифов это не так: утверждённые заранее повышения уже записаны. У 20 из 78 тарифов из Домодедово открытая запись начинается 1 сентября — после среза базы 11 августа.

Оценим по прайсу брони 8–10 августа — 79 554 сегмента. По записи, действовавшей в момент брони, сумма тарифов — 1 521,0 млн ₽. По открытой — 1 544,0 млн ₽: 20 694 сегмента получили сентябрьскую цену, и оценка выросла на 23,1 млн ₽.

«Текущая» — это запись, которая действует в заданный момент, а не запись без конца. Условие на момент верно в обоих случаях, valid_to IS NULL — только пока в таблицу не пишут будущее.

WITH seg AS (
  -- сегменты броней трёх дней
  SELECT f.departure_airport, f.arrival_airport, tf.fare_conditions, b.book_date
  FROM bookings AS b
  JOIN tickets AS t ON t.book_ref = b.book_ref
  JOIN ticket_flights AS tf ON tf.ticket_no = t.ticket_no
  JOIN flights AS f ON f.flight_id = tf.flight_id
  WHERE b.book_date >= TIMESTAMP '2026-08-08'
    AND b.book_date < TIMESTAMP '2026-08-11'
)
SELECT count(*) AS segments,
       sum(act.price) AS tariff_at_booking,
       sum(opn.price) AS tariff_open_row,
       count(*) FILTER (WHERE opn.price <> act.price) AS segments_with_future_price
FROM seg
JOIN fare_price_history AS act
  ON act.departure_airport = seg.departure_airport
 AND act.arrival_airport = seg.arrival_airport
 AND act.fare_conditions = seg.fare_conditions
 AND seg.book_date >= act.valid_from
 AND seg.book_date < COALESCE(act.valid_to, TIMESTAMP '9999-12-31')
JOIN fare_price_history AS opn
  ON opn.departure_airport = seg.departure_airport
 AND opn.arrival_airport = seg.arrival_airport
 AND opn.fare_conditions = seg.fare_conditions
 AND opn.valid_to IS NULL;

OR valid_to IS NULL или COALESCE: что показывает план

Открытый конец записывают двумя способами: t < COALESCE(valid_to, TIMESTAMP '9999-12-31') или (valid_to IS NULL OR t < valid_to). Результат одинаковый, разница — в том, как движок выполняет соединение.

PostgreSQL 14 для запроса об уровне на дату покупки строит в обоих вариантах один план: Hash Left Join с Hash Cond: (tm.member_id = h.member_id), а условие интервала уходит в Join Filter, который отбрасывает 21 710 пар.

DuckDB 1.5 ведёт себя иначе. С COALESCE он выбирает HASH_JOIN, с OR во внешнем соединении — BLOCKWISE_NL_JOIN: всё условие становится одним выражением, и движок перебирает пары строк. На двухнедельной выборке в нашей проверке это около секунды вместо сотых долей.

Пишите COALESCE: форма одна для обоих движков. И не переносите выводы о плане с одной СУБД на другую — смотрите EXPLAIN там, где запрос будет работать. Как его читать — в статье про EXPLAIN ANALYZE в PostgreSQL.

То же в pandas: merge_asof

В ноутбуке ту же задачу решает pd.merge_asof с by='member_id' и direction='backward': каждой покупке достаётся последняя запись участника, начавшаяся не позже неё. Обе таблицы сначала сортируют по времени, иначе pandas остановится с ошибкой left keys must be sorted.

«Последняя начавшаяся» — не то же, что «действующая»: конец интервала merge_asof не проверяет. Если у ключа в истории есть разрыв, покупка внутри него получит уже закрытое значение. Поэтому после соединения стоит проверка valid_to. На этой базе она не отсекает ни одной строки — разрывов в истории уровней нет, — и результат совпадает с SQL: 51 024, 1 009, 467 и 13 сегментов.

Вторая особенность: merge_asof всегда возвращает одну строку на покупку — 52 513, а не 52 521. Пересечение он молча решает в пользу поздней записи. Правило то же, что у ROW_NUMBER, но лишних строк, по которым в SQL заметен дефект истории, здесь не появится: стыки проверяют отдельно.

В DuckDB та же логика есть в SQL — ASOF JOIN. Для четырёх покупок участника 29086 он возвращает четыре строки, покупке 7 августа достаётся silver. В PostgreSQL 14 такого соединения нет: запрос падает с syntax error at or near "ASOF".

WITH purchases AS (
  SELECT DISTINCT tm.member_id, b.book_ref, b.book_date
  FROM ticket_members AS tm
  JOIN tickets AS t ON t.ticket_no = tm.ticket_no
  JOIN bookings AS b ON b.book_ref = t.book_ref
  WHERE tm.member_id = 29086
)
SELECT p.book_ref, p.book_date, h.tier
FROM purchases AS p
ASOF LEFT JOIN loyalty_tier_history AS h
  ON h.member_id = p.member_id AND p.book_date >= h.valid_from
ORDER BY p.book_date;

import pandas as pd

f = pd.read_parquet('flights.parquet', columns=['flight_id', 'scheduled_departure'])
f['scheduled_departure'] = pd.to_datetime(f['scheduled_departure'])
f = f.loc[f.scheduled_departure.ge(pd.Timestamp('2026-07-27'))
          & f.scheduled_departure.lt(pd.Timestamp('2026-08-10')), ['flight_id']]
tf = pd.read_parquet('ticket_flights.parquet', columns=['ticket_no', 'flight_id', 'amount'])
tm = pd.read_parquet('ticket_members.parquet', columns=['ticket_no', 'member_id'])
t = pd.read_parquet('tickets.parquet', columns=['ticket_no', 'book_ref'])
b = pd.read_parquet('bookings.parquet', columns=['book_ref', 'book_date'])
h = pd.read_parquet('loyalty_tier_history.parquet',
                    columns=['member_id', 'tier', 'valid_from', 'valid_to'])
for frame, cols in ((b, ['book_date']), (h, ['valid_from', 'valid_to'])):
    for col in cols:
        frame[col] = pd.to_datetime(frame[col])

seg = (tf.merge(f, on='flight_id').merge(tm, on='ticket_no')
         .merge(t, on='ticket_no').merge(b, on='book_ref'))
seg['amount'] = seg['amount'].astype(float)

# последняя запись участника, начавшаяся не позже покупки
asof = pd.merge_asof(seg.sort_values('book_date'), h.sort_values('valid_from'),
                     left_on='book_date', right_on='valid_from',
                     by='member_id', direction='backward')
# действует ли она ещё: конец пустой или позже покупки
expired = asof['valid_to'].notna() & asof['book_date'].ge(asof['valid_to'])
asof['tier_at_booking'] = asof['tier'].mask(expired).fillna('уровня ещё не было')

print(len(seg), len(asof), int(expired.sum()))
out = (asof.groupby('tier_at_booking')['amount'].agg(['size', 'sum'])
           .sort_values('sum', ascending=False))
for tier, row in out.iterrows():
    print(tier, int(row['size']), int(row['sum']))

Сборка истории из дат изменений и проверка стыков

Иногда вместо готовых интервалов есть только журнал: «с такого-то момента значение такое-то». Конец записи тогда — начало следующей записи того же ключа: LEAD(valid_from) OVER (PARTITION BY member_id ORDER BY valid_from). У последней записи следующей нет, LEAD вернёт NULL — это и есть открытый конец. О самой функции — в статье про LAG и LEAD.

Тот же LEAD проверяет стыки готовой истории. У участника 29086 хранимый конец basic — 8 августа, пересобранный — 6 августа: нахлёст два дня. Равные значения означают ровный стык, хранимый конец меньше пересобранного — разрыв.

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

WITH h AS (
  SELECT tier, valid_from, valid_to,
         lead(valid_from) OVER (PARTITION BY member_id ORDER BY valid_from) AS rebuilt_to
  FROM loyalty_tier_history
  WHERE member_id = 29086
)
SELECT tier, valid_from, valid_to, rebuilt_to,
       CASE WHEN rebuilt_to IS NULL THEN 'последняя запись'
            WHEN valid_to IS NULL OR valid_to > rebuilt_to THEN 'нахлёст'
            WHEN valid_to < rebuilt_to THEN 'разрыв'
            ELSE 'ровный стык' END AS seam
FROM h
ORDER BY valid_from;

Частые вопросы

Что такое SCD2 простыми словами? Способ хранить историю атрибута: строка — значение и период, когда оно действовало. Тип 1 перезаписывает значение и теряет прошлое, тип 2 сохраняет все версии.

Как выбрать запись, действующую на дату? valid_from <= t AND t < COALESCE(valid_to, TIMESTAMP '9999-12-31'). Если подходят две записи, история пересекается: берите позднюю и сообщите о дефекте.

Почему не BETWEEN? Он включает оба конца, и на стыке интервалов подходят обе записи — прежняя и новая.

Какую дату брать для соединения факта с историей? Момент, когда значение повлияло на факт: цена — при покупке, обслуживание по уровню — при вылете. Открытая запись годится только для вопроса «что сейчас».

В уроке курса — задачи на то же условие: момент на границе интервала, действующая и открытая запись.