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