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