При работе с периодическими регистрами сведений в платформе 1С:Предприятие разработчики часто сталкиваются с ситуацией, когда виртуальная таблица СрезПоследних возвращает несколько записей по одному и тому же объекту (например, по физическому лицу), хотя ожидается только одна — самая последняя. Особенно ярко это проявляется в кадровых регистрах, таких как КадроваяИсторияСотрудников.
В рамках этой статьи мы подробно разберем, почему возникает эта проблема, проанализируем внутреннее устройство виртуальных таблиц срезов, а также рассмотрим наиболее эффективные и быстрые способы получения единственной записи с максимальной датой с помощью языка запросов 1С.
Для начала выясним причину такого поведения системы. Многие начинающие разработчики полагают, что СрезПоследних работает глобально по всей таблице или по выбранному реквизиту. Однако это не так. Виртуальная таблица СрезПоследних рассчитывает самые свежие записи в разрезе уникального набора всех измерений регистра.
Рассмотрим структуру типового регистра сведений КадроваяИсторияСотрудников (который используется в конфигурациях «1С:Зарплата и управление персоналом», «1С:ERP Управление предприятием» и «1С:Комплексная автоматизация»). Измерениями данного регистра являются:
Обратите внимание, что поле ФизическоеЛицо в данном регистре чаще всего является не измерением, а обычным реквизитом. Из-за этого при попытке получить срез последних с отбором или группировкой по ФизическоеЛицо мы можем получить две или три записи по одному человеку. Это происходит по следующим причинам:
Когда нам требуется получить строго одну, самую последнюю запись по физическому лицу (независимо от совместительства, организаций и количества элементов в справочнике сотрудников), стандартный СрезПоследних без предварительной подготовки данных использовать нельзя.
Рассмотрим попытку решения, к которой часто приходят разработчики интуитивно (именно этот вариант изначально получился у автора ветки обсуждения):
ВЫБРАТЬ
МАКСИМУМ(КадроваяИсторияСотрудников.Период) КАК Период,
КадроваяИсторияСотрудников.ФизическоеЛицо КАК ФизическоеЛицо
ПОМЕСТИТЬ ВТмаксдата
ИЗ
РегистрСведений.КадроваяИсторияСотрудников КАК КадроваяИсторияСотрудников
СГРУППИРОВАТЬ ПО
КадроваяИсторияСотрудников.ФизическоеЛицо
ИНДЕКСИРОВАТЬ ПО
ФизическоеЛицо,
Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТмаксдата.Период КАК Период,
ВТмаксдата.ФизическоеЛицо КАК ФизическоеЛицо,
КадроваяИсторияСотрудниковСрезПоследних.Подразделение КАК Подразделение
ИЗ
ВТмаксдата КАК ВТмаксдата
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.КадроваяИсторияСотрудников.СрезПоследних КАК КадроваяИсторияСотрудниковСрезПоследних
ПО (КадроваяИсторияСотрудниковСрезПоследних.ФизическоеЛицо = ВТмаксдата.ФизическоеЛицо)
И (КадроваяИсторияСотрудниковСрезПоследних.Период = ВТмаксдата.Период)
Почему этот запрос работает медленно и неоптимально?
Основная проблема заключается во втором запросе пакета, где происходит соединение временной таблицы с виртуальной таблицей СрезПоследних. СУБД (особенно PostgreSQL) крайне неэффективно строит планы запросов при соединении физических или временных таблиц с виртуальными таблицами срезов. Платформа 1С вынуждена сначала рассчитать срез последних для всего регистра (или попытаться передать параметры внутрь, что не всегда срабатывает корректно при сложных связях), а затем выполнять соединение в оперативной памяти. На больших базах данных такой запрос приведет к сильным зависаниям и избыточной нагрузке на сервер.
Чтобы получить максимальную производительность и гарантированно корректный результат, мы должны полностью отказаться от соединения с виртуальной таблицей СрезПоследних. Вместо нее мы будем использовать основную (физическую) таблицу регистра.
Разберем оптимальный алгоритм по шагам:
ФизическоеЛицо и Период. Из физической таблицы мы забираем все необходимые нам разрезы и реквизиты (подразделение, должность, организацию и т.д.).Посмотрим на практическую реализацию этого алгоритма в коде запроса:
ВЫБРАТЬ
КадроваяИстория.ФизическоеЛицо КАК ФизическоеЛицо,
МАКСИМУМ(КадроваяИстория.Период) КАК МаксимальныйПериод
ПОМЕСТИТЬ ВТ_МаксимальныеДаты
ИЗ
РегистрСведений.КадроваяИсторияСотрудников КАК КадроваяИстория
СГРУППИРОВАТЬ ПО
КадроваяИстория.ФизическоеЛицо
ИНДЕКСИРОВАТЬ ПО
ФизическоеЛицо,
МаксимальныйПериод
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Даты.ФизическоеЛицо КАК ФизическоеЛицо,
ВТ_Даты.МаксимальныйПериод КАК Период,
КадроваяИсторияРегистр.Организация КАК Организация,
КадроваяИсторияРегистр.Подразделение КАК Подразделение,
КадроваяИсторияРегистр.Сотрудник КАК Сотрудник
ИЗ
ВТ_МаксимальныеДаты КАК ВТ_Даты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.КадроваяИсторияСотрудников КАК КадроваяИсторияРегистр
ПО (КадроваяИсторияРегистр.ФизическоеЛицо = ВТ_Даты.ФизическоеЛицо)
И (КадроваяИсторияРегистр.Период = ВТ_Даты.МаксимальныйПериод)
Почему данный вариант работает быстро?
В этом сценарии СУБД выполняет простейшую выборку с группировкой по физической таблице (которая отлично оптимизирована индексами СУБД по умолчанию). Во втором запросе соединение происходит по проиндексированным полям двух физических таблиц (временная таблица также проиндексирована нами при создании). Оптимизатор СУБД мгновенно строит идеальный план выполнения (обычно это Index Seek или Merge Join), что обеспечивает выполнение запроса за доли секунды даже на миллионных объемах данных.
Описанный выше метод с функцией МАКСИМУМ(Период) имеет один скрытый недостаток, о котором важно знать каждому разработчику. Он кроется в возможности коллизии дат.
Если в системе по одному физическому лицу в одну и ту же секунду были проведены два разных кадровых документа (например, в 12:00:00 провели «Кадровый перевод» в другое подразделение, и этой же секундой провели «Увольнение»), то в физической таблице регистра появятся две записи с одинаковым значением поля Период.
В этом случае наш запрос на втором шаге соединит временную таблицу с физической и вернет обе записи на эту секунду. Мы снова получим дубли по физическому лицу.
Как гарантированно получить только одну запись?
Если регистр подчинен регистратору, то для однозначного определения порядка записей внутри одной секунды используется понятие «Момент времени» или связка полей Период и Регистратор. На уровне базы данных у каждого документа есть уникальный идентификатор (GUID) или позиция на оси времени.
Для решения этой проблемы мы можем усложнить наш пакет запросов, добавив выборку по максимальному регистратору:
ВЫБРАТЬ
КадроваяИстория.ФизическоеЛицо КАК ФизическоеЛицо,
МАКСИМУМ(КадроваяИстория.Период) КАК МаксимальныйПериод
ПОМЕСТИТЬ ВТ_ПредварительныеДаты
ИЗ
РегистрСведений.КадроваяИсторияСотрудников КАК КадроваяИстория
СГРУППИРОВАТЬ ПО
КадроваяИстория.ФизическоеЛицо
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
КадроваяИстория.ФизическоеЛицо КАК ФизическоеЛицо,
КадроваяИстория.Период КАК Период,
МАКСИМУМ(КадроваяИстория.Регистратор) КАК МаксимальныйРегистратор
ПОМЕСТИТЬ ВТ_МаксимальныеРегистраторы
ИЗ
ВТ_ПредварительныеДаты КАК ВТ_ПредварительныеДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.КадроваяИсторияСотрудников КАК КадроваяИстория
ПО (КадроваяИстория.ФизическоеЛицо = ВТ_ПредварительныеДаты.ФизическоеЛицо)
И (КадроваяИстория.Период = ВТ_ПредварительныеДаты.МаксимальныйПериод)
СГРУППИРОВАТЬ ПО
КадроваяИстория.ФизическоеЛицо,
КадроваяИстория.Период
ИНДЕКСИРОВАТЬ ПО
ФизическоеЛицо,
Период,
МаксимальныйРегистратор
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Рег.ФизическоеЛицо КАК ФизическоеЛицо,
ВТ_Рег.Период КАК Период,
КадроваяИсторияРегистр.Подразделение КАК Подразделение,
КадроваяИсторияРегистр.Организация КАК Организация
ИЗ
ВТ_МаксимальныеРегистраторы КАК ВТ_Рег
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.КадроваяИсторияСотрудников КАК КадроваяИсторияРегистр
ПО (КадроваяИсторияРегистр.ФизическоеЛицо = ВТ_Рег.ФизическоеЛицо)
И (КадроваяИсторияРегистр.Период = ВТ_Рег.Период)
И (КадроваяИсторияРегистр.Регистратор = ВТ_Рег.МаксимальныйРегистратор)
Использование агрегатной функции МАКСИМУМ(Регистратор) позволяет выбрать только один документ-регистратор из нескольких, созданных в одну секунду (система выберет тот, у которого уникальная ссылка больше по значению). Это гарантирует получение ровно одной строки для каждого физического лица в любых, даже самых неординарных ситуациях ведения учета.
Если вы ведете разработку в современных типовых конфигурациях фирмы 1С, вам не обязательно писать сложные многоуровневые запросы вручную. Разработчики конфигураций уже предусмотрели готовые программные механизмы для работы с кадровой историей.
В новых версиях ЗУП и ERP кадровые данные хранятся не в классических периодических регистрах, а в специальных интервальных регистрах сведений (например, КадроваяИсторияСотрудниковИнтервальный). Напрямую к ним обращаться запросами настоятельно не рекомендуется, так как структура хранения данных оптимизирована под внутренние алгоритмы платформы.
Для получения актуальных кадровых данных рекомендуется использовать следующие подходы:
Представления_КадровыеДанныеСотрудников. При вызове стандартного метода общего модуля ПриКомпоновкеРезультата система автоматически преобразует эти таблицы в оптимизированные запросы с учетом дат среза и всех видов совмещений.КадровыйУчет.КадровыеДанныеСотрудников() позволяет получить таблицу значений со всеми необходимыми кадровыми свойствами (подразделение, должность, организация) на определенную дату по массиву сотрудников без написания ручных запросов к базам данных.Использование типового API не только ускоряет разработку, но и гарантирует, что ваш код продолжит корректно работать после очередного обновления конфигурации.