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