При разработке отчетов и проведении документов в платформе «1С:Предприятие 8» часто возникает задача получения курсов валют за определенный период. Типичная ситуация: у нас есть временная таблица с документами, и нам необходимо получить курсы валют, актуальные для этих документов. Прямая попытка использовать подзапросы в условиях соединения (например, конструкции с ВЫБРАТЬ МИНИМУМ(Дата)) приводит к ошибкам синтаксиса или крайне неэффективной работе СУБД. В этой статье мы подробно разберем, как правильно решить эту задачу несколькими способами, рассмотрим подводные камни периодических регистров и научимся оптимизировать такие запросы для высоконагруженных систем.
Прежде чем переходить к технической реализации запроса, разберем важную методологическую деталь. Регистр сведений КурсыВалют является периодическим. Записи в него вносятся только в момент изменения курса. Если курс определенной валюты не менялся в течение месяца до даты вашего самого раннего документа, то в интервале между Минимум(ДатаДокумента) и Максимум(ДатаДокумента) физически не будет записи на минимальную дату. СУБД вернет значение NULL для первых документов в выборке.
Решение: Нижней границей диапазона должна быть не просто минимальная дата документа из выборки, а дата актуального курса на момент этой минимальной даты. То есть перед получением диапазона необходимо определить дату «среза последних» на самую раннюю дату документов.
Поскольку язык запросов 1С не разрешает использовать подзапросы внутри условий связи таблиц (конструкции вида ПО Регистр.Период >= (ВЫБРАТЬ...) не поддерживаются платформой), нам необходимо предварительно вычислить границы периода и сохранить их в отдельную временную таблицу.
Рассмотрим по шагам, как реализовать этот алгоритм в пакетном запросе:
МЕЖДУ.Посмотрим на пример практической реализации этого запроса:
// Первый запрос пакета: определяем границы периода
ВЫБРАТЬ
МИНИМУМ(ВТ_Документы.Дата) КАК МинДата,
МАКСИМУМ(ВТ_Документы.Дата) КАК МаксДата
ПОМЕСТИТЬ ВТ_ГраницыПериода
ИЗ
ВТ_Документы КАК ВТ_Документы
;
// Второй запрос пакета: выбираем курсы в полученном диапазоне
ВЫБРАТЬ
КурсыВалют.Период КАК Период,
КурсыВалют.Валюта КАК Валюта,
КурсыВалют.Курс КАК Курс,
КурсыВалют.Кратность КАК Кратность
ИЗ
ВТ_ГраницыПериода КАК Границы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.КурсыВалют КАК КурсыВалют
ПО КурсыВалют.Период МЕЖДУ Границы.MинДата И Границы.МаксДата
Этот способ отлично подходит, если вам действительно нужно получить массив курсов для последующей обработки в коде или выгрузки во внешние системы.
Как справедливо замечают опытные разработчики, в 95% случаев диапазон курсов требуется для того, чтобы сопоставить каждому конкретному документу курс, актуальный именно на его дату. Поскольку виртуальная таблица СрезПоследних не умеет принимать на вход список дат, применяется классический паттерн «Срез последних на каждую дату в запросе».
Проанализируем этот алгоритм, состоящий из трех шагов:
Шаг 1. Получение уникальных пар Валюта-Дата. Нам необходимо выбрать все уникальные сочетания валют и дат документов. Даты документов рекомендуется приводить к началу дня с помощью функции НАЧАЛОПЕРИОДА, так как курсы валют обычно устанавливаются раз в день.
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВТ_Документы.Валюта КАК Валюта,
НАЧАЛОПЕРИОДА(ВТ_Документы.Дата, ДЕНЬ) КАК ПериодДокумента
ПОМЕСТИТЬ ВТ_УникальныеДаты
ИЗ
ВТ_Документы КАК ВТ_Документы
ИНДЕКСИРОВАТЬ ПО
Валюта,
ПериодДокумента
;
Шаг 2. Поиск дат наиболее актуальных курсов. Мы соединяем наши уникальные даты с физической таблицей регистра курсов по условию «период курса меньше или равен дате документа». Сгруппировав результат по валюте и дате документа, с помощью функции МАКСИМУМ(КурсыВалют.Период) мы находим точную дату последней записи в регистре для каждого документа.
ВЫБРАТЬ
УникальныеДаты.Валюта КАК Валюта,
УникальныеДаты.ПериодДокумента КАК ПериодДокумента,
МАКСИМУМ(КурсыВалют.Период) КАК ПериодКурса
ПОМЕСТИТЬ ВТ_МаксимальныеДатыКурсов
ИЗ
ВТ_УникальныеДаты КАК УникальныеДаты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КурсыВалют КАК КурсыВалют
ПО УникальныеДаты.Валюта = КурсыВалют.Валюта
И КурсыВалют.Период <= УникальныеДаты.ПериодДокумента
ГРУППИРОВАТЬ ПО
УникальныеДаты.Валюта,
УникальныеДаты.ПериодДокумента
ИНДЕКСИРОВАТЬ ПО
Валюта,
ПериодКурса
;
Шаг 3. Получение значений курса и кратности. Теперь, когда у нас есть точные даты курсов для каждого документа, мы просто соединяем таблицу ВТ_МаксимальныеДатыКурсов с физической таблицей регистра по строгому равенству валюты и периода.
ВЫБРАТЬ
МаксДаты.Валюта КАК Валюта,
МаксДаты.ПериодДокумента КАК ПериодДокумента,
КурсыВалют.Курс КАК Курс,
КурсыВалют.Кратность КАК Кратность
ИЗ
ВТ_МаксимальныеДатыКурсов КАК МаксДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.КурсыВалют КАК КурсыВалют
ПО МаксДаты.Валюта = КурсыВалют.Валюта
И МаксДаты.ПериодКурса = КурсыВалют.Период
Этот подход гарантирует 100% точность получения курса на любую дату без пропусков и значений NULL.
Если вы разрабатываете отчет на базе Системы компоновки данных (СКД), то писать сложные трехступенчатые запросы нет необходимости. Платформа «1С:Предприятие» умеет делать срез последних на каждую дату в автоматическом режиме с помощью механизма связей наборов данных.
Выясним, как настроить такую связь:
НаборДанныхДокументы), который выбирает документы, их даты и валюты.НаборДанныхКурсы) к виртуальной таблице среза последних: ВЫБРАТЬ Курс, Кратность ИЗ РегистрСведений.КурсыВалют.СрезПоследних(&Период, Валюта = &Валюта).НаборДанныхДокументы, Приемник — НаборДанныхКурсы.Валюта -> Валюта.Дата (из источника) должно передаваться в параметр Период (приемника).При генерации отчета СКД сама выполнит оптимальные точечные запросы к СУБД для каждой строки, избавив вас от ручного написания сложных соединений.
При работе с большими объемами данных (когда временная таблица документов содержит десятки тысяч строк) соединение по неравенству КурсыВалют.Период <= УникальныеДаты.ПериодДокумента может стать «узким горлышком» системы.
Для предотвращения критического падения производительности обязательно соблюдайте следующие правила:
Валюта, ПериодДокумента и ПериодКурса. Инструкция ИНДЕКСИРОВАТЬ ПО заставляет СУБД строить оптимальные индексы в СУБД.Nested Loop (вложенные циклы) вместо Hash Match, что приведет к зависанию запроса. Своевременная индексация ВТ полностью решает эту проблему.Применяя данные методики на практике, мы получаем быстрое, надежное и отказоустойчивое решение для работы с курсами валют в любых прикладных задачах на платформе 1С.