При построении отчетов по истории изменений (например, хронологии изменения окладов сотрудников, цен номенклатуры или состояний договоров) разработчики часто сталкиваются с проблемой лавинообразного размножения строк. Простая связь таблицы с самой собой по условию «больше» связывает каждую запись со всеми последующими событиями в будущем. В результате вместо красивой цепочки изменений мы получаем избыточную матрицу, где одна и та же старая дата многократно сопоставляется с новыми периодами.
В этой статье мы подробно разберем, почему возникает эта классическая проблема, проанализируем логику работы с периодическими регистрами сведений в системе 1С:Предприятие и построим оптимальный пакетный запрос, который вернет строго последовательную историю переходов из одного состояния в другое.
Давайте проанализируем исходную попытку решения задачи. В первоначальном варианте запроса разработчик пытается связать данные регистра ПлановыеНачисленияРаботников сами с собой, используя условие:
ПлановыеНачисленияРаботников.Период > ВТПланТекущая.Период
Представим, что у сотрудника было четыре изменения оклада:
При использовании простого условия «больше» СУБД сопоставит запись от 09.11.2015 со всеми тремя последующими датами (2016, 2017 и 2018 гг.). Запись от 01.11.2016 будет связана с датами 2017 и 2018 годов. На выходе получится избыточная информация, которая не дает четкого понимания, в какой именно момент и с какого значения на какое изменился оклад.
Наша задача: связать каждую запись строго со следующим по порядку событием. То есть дата 09.11.2015 должна образовать пару только с 01.11.2016, дата 01.11.2016 — только с 15.11.2017, и так далее.
Для реализации этой логики мы применим классический паттерн разработки запросов в 1С: использование временных таблиц с промежуточной группировкой по минимальной дате будущего периода. Рассмотрим весь процесс по шагам.
Для начала нам нужно получить все срезы из регистра сведений по интересующим нас сотрудникам за определенный период. Очень важно сделать это один раз и поместить данные во временную таблицу. Повторное обращение к физической таблице регистра сведений (как это было в исходном запросе автора темы) существенно снижает производительность системы.
Обратите внимание на индексацию временной таблицы. Мы индексируем её по ключевым полям связи: ФизЛицо, ВидРасчета и Период. Это гарантирует быстрое выполнение последующих соединений.
ВЫБРАТЬ РАЗЛИЧНЫЕ
ПлановыеНачисленияРаботников.Период КАК Период,
ПлановыеНачисленияРаботников.ВидРасчета КАК ВидРасчета,
ПлановыеНачисленияРаботников.ФизЛицо КАК ФизЛицо,
ПлановыеНачисленияРаботников.Показатель1 КАК Результат,
ПлановыеНачисленияРаботников.Регистратор КАК Регистратор
ПОМЕСТИТЬ ВТПланТекущая
ИЗ
РегистрСведений.ПлановыеНачисленияРаботников КАК ПлановыеНачисленияРаботников
ГДЕ
ПлановыеНачисленияРаботников.Период МЕЖДУ &НачалоПериода И &КонецПериода
ИНДЕКСИРОВАТЬ ПО
ФизЛицо,
ВидРасчета,
Период
;
Теперь нам нужно сопоставить каждую запись с ее непосредственным «преемником». Для этого мы соединяем временную таблицу ВТПланТекущая саму с собой по условию: следующий период должен быть строго больше текущего. Но чтобы получить ровно одну — самую ближайшую — дату, мы используем агрегатную функцию МИНИМУМ и группируем результат по исходной (старой) дате.
Таким образом, для каждой даты мы находим «минимальную из всех будущих дат», то есть следующую хронологическую точку изменения данных.
ВЫБРАТЬ
ВТ_Старая.ФизЛицо КАК ФизЛицо,
ВТ_Старая.ВидРасчета КАК ВидРасчета,
ВТ_Старая.Период КАК ПериодСтарый,
МИНИМУМ(ВТ_Новая.Период) КАК ПериодНовый
ПОМЕСТИТЬ ВТ_СвязиПериодов
ИЗ
ВТПланТекущая КАК ВТ_Старая
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТПланТекущая КАК ВТ_Новая
ПО ВТ_Новая.ФизЛицо = ВТ_Старая.ФизЛицо
И ВТ_Новая.ВидРасчета = ВТ_Старая.ВидРасчета
И ВТ_Новая.Период > ВТ_Старая.Период
СГРУППИРОВАТЬ ПО
ВТ_Старая.ФизЛицо,
ВТ_Старая.ВидРасчета,
ВТ_Старая.Период
ИНДЕКСИРОВАТЬ ПО
ФизЛицо,
ВидРасчета,
ПериодСтарый,
ПериодНовый
;
В нашей таблице связей ВТ_СвязиПериодов теперь лежат чистые пары дат (например: 09.11.2015 и 01.11.2016). Но в ней нет окладов и регистраторов (документов-оснований). Чтобы вытащить эти реквизиты, мы дважды соединяем таблицу связей с нашей первой временной таблицей ВТПланТекущая:
Также добавим полезное условие ВТ_Старая.Результат <> ВТ_Новая.Результат. Оно позволит отсеять «пустые» кадровые события (например, когда сотрудника перевели в другой отдел, но его оклад не поменялся), оставив в отчете только фактические изменения сумм.
ВЫБРАТЬ
ВТ_Связи.ФизЛицо КАК ФизЛицо,
ВТ_Связи.ВидРасчета КАК ВидРасчета,
ВТ_Связи.ПериодСтарый КАК ПериодСтарый,
ВТ_Старая.Регистратор КАК РегистраторСтарый,
ВТ_Старая.Результат КАК РезультатСтарый,
ВТ_Связи.ПериодНовый КАК ПериодНовый,
ВТ_Новая.Регистратор КАК РегистраторНовый,
ВТ_Новая.Результат КАК РезультатНовый
ИЗ
ВТ_СвязиПериодов КАК ВТ_Связи
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТПланТекущая КАК ВТ_Старая
ПО ВТ_Связи.ФизЛицо = ВТ_Старая.ФизЛицо
И ВТ_Связи.ВидРасчета = ВТ_Старая.ВидРасчета
И ВТ_Связи.ПериодСтарый = ВТ_Старая.Период
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТПланТекущая КАК ВТ_Новая
ПО ВТ_Связи.ФизЛицо = ВТ_Новая.ФизЛицо
И ВТ_Связи.ВидРасчета = ВТ_Новая.ВидРасчета
И ВТ_Связи.ПериодНовый = ВТ_Новая.Период
ГДЕ
ВТ_Старая.Результат <> ВТ_Новая.Результат
УПОРЯДОЧИТЬ ПО
ФизЛицо,
ВидРасчета,
ПериодСтарый
Разберем несколько важных моментов, которые помогут адаптировать данный запрос под различные бизнес-требования в вашей базе УПП 1.3 или ЗУП 2.5.
1. Как не потерять новых сотрудников?
В предложенном выше решении используется ВНУТРЕННЕЕ СОЕДИНЕНИЕ во втором шаге пакета. Это означает, что если по сотруднику в базе зарегистрировано ровно одно событие (например, только «Прием на работу» и никаких последующих изменений оклада), он вообще не попадет в выборку, поскольку для его периода нет «будущей» даты. Если вам необходимо выводить таких сотрудников со старым окладом и пустым значением в колонке нового оклада, во втором шаге измените тип связи на ЛЕВОЕ СОЕДИНЕНИЕ и обработайте возможные значения NULL на третьем шаге с помощью функции ЕСТЬNULL.
2. Что делать, если начисления меняются в один день?
Если в вашей системе возможна ситуация, когда по одному физическому лицу и одному виду расчета в пределах одной секунды проводятся два документа (что в принципе является нештатной ситуацией для периодического регистра сведений с периодичностью «В пределах секунды»), данный запрос может вернуть некорректные связи. В реальной практике рекомендуется следить за уникальностью периодов записей в рамках ключевых измерений.
3. Использование в СКД (Схеме компоновки данных)
Если этот запрос будет использоваться в качестве источника данных для Системы компоновки данных (СКД), вы можете вынести условия по периоду и отборам в параметры. Использование временных таблиц полностью поддерживается СКД и значительно ускоряет построение отчетов по сравнению с тяжелыми вложенными запросами.
Мы полностью решили проблему дублирования строк, разложив логику на три понятных шага. Такой подход не нагружает СУБД лишними операциями и возвращает красивую, последовательную историю изменения начислений ваших сотрудников.