Как реализовать запрос с максимальной датой курса для каждой транзакции с учетом выходных и праздников

Что проверяют

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

Как отвечать

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

Варианты:

• LATERAL с LIMIT 1 (PostgreSQL): left join lateral (select rate from rates r where r.currency = t.currency and r.rate_date <= t.tx_date order by r.rate_date desc limit 1) x on true. С индексом (currency, rate_date) это один короткий поиск по индексу на транзакцию;
• интервалы действия: lead(rate_date) over (partition by currency order by rate_date) превращает курсы в отрезки, дальше join по tx_date >= rate_date and tx_date < coalesce(next_date, date '9999-12-31'). Подходит движкам без LATERAL;
• ROW_NUMBER: join всех курсов не позже даты и row_number() ... = 1. Корректно, но промежуточный результат огромный;
• нативный ASOF JOIN: есть в ClickHouse и DuckDB;
• pandas: pd.merge_asof(tx, rates, on='date', by='currency', direction='backward'), обе таблицы отсортированы по ключу.

Если транзакции хранятся со временем, приведите их к дате в нужном часовом поясе до сравнения.

Где спотыкаются

Жёстко вырезают субботу и воскресенье по номеру дня. Это ломается на переносах, когда суббота рабочая, и зависит от диалекта: DAYOFWEEK в MySQL и extract(dow ...) в PostgreSQL нумеруют дни по-разному.

Берут inner join и молча теряют транзакции, для которых курса ещё не было. Нужен left join и явная обработка NULL.

Генерируют рекурсивным CTE все даты периода и ищут курс по ним. Шаг лишний: максимальная дата считается прямо по таблице курсов, календарь нужен лишь для особых правил.