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