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