В практике разработки на платформе 1С программисты часто сталкиваются с задачей, когда необходимо получить значение из периодического регистра сведений (например, курс валюты или цену номенклатуры) не на одну конкретную дату, а на даты различных событий. Типичный пример — отчет по продажам, где каждую строку реализации нужно пересчитать в базовую валюту по курсу, актуальному именно на момент этой самой продажи.
Стандартная виртуальная таблица СрезПоследних принимает в качестве параметра одну дату. Если мы попытаемся использовать её в обычном соединении, мы получим курс на «сейчас» или на фиксированную дату параметра, что не решит задачу. Разберем детально, какими способами можно получить актуальные данные на каждую строку документа.
Этот метод считается наиболее простым в реализации для отчетов, создаваемых на базе Системы Компоновки Данных (СКД). Основная идея заключается в том, чтобы разделить получение данных на два независимых потока.
Рассмотрим алгоритм настройки:
НаборДанныхПродажи). В нем мы выбираем все необходимые поля из документов продажи: Регистратор, Период (дата продажи), Валюта, Сумма.НаборДанныхКурсы). В тексте запроса используем обращение к виртуальной таблице:
ВЫБРАТЬ
КурсыВалютСрезПоследних.Валюта,
КурсыВалютСрезПоследних.Курс,
КурсыВалютСрезПоследних.Кратность
ИЗ
РегистрСведений.КурсыВалют.СрезПоследних(&П_Дата, ) КАК КурсыВалютСрезПоследних
НаборДанныхПродажи, в поле Приемник связи — НаборДанныхКурсы.П_Дата нашего второго набора данных должен получать значение из поля Период первого набора.Проанализируем этот подход: СКД для каждой строки из первого набора будет выполнять отдельный запрос ко второму набору, подставляя дату продажи в качестве параметра &П_Дата. Это «честный» срез последних на каждую дату. Главный минус — при очень большом количестве строк (десятки тысяч) производительность может снизиться, так как количество обращений к СУБД возрастает.
Если нам нужно решить задачу исключительно средствами языка запросов (например, для динамического списка или программной обработки данных) или если скорость работы СКД через связи нас не устраивает, применим метод трех шагов с использованием временных таблиц. Выясним причину популярности этого метода: он выполняется одним пакетом запросов и эффективно оптимизируется СУБД.
Разберем по шагам логику построения такого запроса:
Шаг 1: Подготовка уникальных ключей. Выберем все продажи и сгруппируем их по валюте и дате, чтобы получить уникальные пары «Валюта — Дата». Поместим их во временную таблицу ВТ_Продажи.
Шаг 2: Поиск ближайшей даты курса. Соединим нашу таблицу ВТ_Продажи с реальной (физической) таблицей регистра сведений КурсыВалют. Условие соединения: ВТ_Продажи.Валюта = КурсыВалют.Валюта И КурсыВалют.Период <= ВТ_Продажи.Период.
Затем сгруппируем результат по полям таблицы продаж и найдем МАКСИМУМ(КурсыВалют.Период). Это и будет дата последнего изменения курса перед продажей. Поместим результат в ВТ_КлючиКурсов.
Шаг 3: Получение значений. Соединим ВТ_КлючиКурсов с физической таблицей регистра еще раз по двум полям (Валюта и Период), чтобы забрать числовое значение курса.
Пример кода такого запроса:
ВЫБРАТЬ
Продажи.Валюта КАК Валюта,
Продажи.Период КАК ПериодПродажи,
МАКСИМУМ(Курсы.Период) КАК ПериодКурса
ПОМЕСТИТЬ ВТ_МаксимальныеДаты
ИЗ
РегистрНакопления.Продажи КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КурсыВалют КАК Курсы
ПО Продажи.Валюта = Курсы.Валюта
И Продажи.Период >= Курсы.Период
СГРУППИРОВАТЬ ПО
Продажи.Валюта,
Продажи.Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.ПериодПродажи,
ВТ.Валюта,
РеальныйКурс.Курс
ИЗ
ВТ_МаксимальныеДаты КАК ВТ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.КурсыВалют КАК РеальныйКурс
ПО ВТ.Валюта = РеальныйКурс.Валюта
И ВТ.ПериодКурса = РеальныйКурс.Период
Если ваша платформа версии 8.3.15 и выше, мы можем значительно упростить конструкцию, используя оконные функции. Это позволяет избежать многократных соединений одной и той же таблицы. Мы можем использовать функцию LAST_VALUE или MAX() OVER() в связке с упорядочиванием по периоду.
Проанализируем ситуацию: использование оконных функций позволяет СУБД за один проход по индексу найти нужное значение. Однако стоит помнить, что в самом языке запросов 1С поддержка оконных функций ограничена определенными конструкциями, и зачастую метод с временными таблицами (Способ 2) остается наиболее универсальным и предсказуемым по производительности.
При работе с вычисляемыми полями и ресурсами в СКД, о которых упоминалось в обсуждении, часто возникают сложности с арифметическими операциями. Разберем основные моменты:
NULL. Любая математическая операция с NULL (например, Сумма * Курс) даст в результате NULL. Поэтому в выражениях всегда используйте функцию ЕСТЬNULL(Поле, 0) или ЕСТЬNULL(Поле, 1) для кратности.Поле1 + Поле2. Если же вы делаете это в Ресурсах, убедитесь, что вы суммируете уже вычисленные значения, например: СУММА(Поле1) + СУММА(Поле2).Валюта и Период. Без индексов соединение больших таблиц «каждый с каждым» по условию >= приведет к критическому зависанию базы данных.Таким образом, выбор метода зависит от ваших условий. Если отчет простой и данных немного — используйте связи наборов данных СКД. Если важна максимальная скорость на больших объемах или данные нужны вне отчета — используйте запрос с временными таблицами и поиском максимальной даты.