При работе с базами данных 1С:Предприятие разработчики часто сталкиваются с задачей поиска дублирующихся записей. Особенно остро этот вопрос встает при анализе информации в регистрах сведений, когда необходимо не просто выявить сам факт наличия дублей по какому-то ключевому полю (например, по номеру договора), но и вывести детальные сведения по этим строкам: суммы, ответственных менеджеров, контрагентов и другие реквизиты.
В этой статье мы подробно разберем, почему стандартная группировка в лоб не решает эту задачу, как правильно построить запрос с использованием временных таблиц, рассмотрим альтернативный метод самосоединения, а также обсудим важные нюансы оптимизации производительности СУБД и специфику работы с регистрами сведений.
Рассмотрим типичную ошибку, которую часто совершают начинающие разработчики. Попытка получить дублирующиеся записи одним простым запросом с группировкой по всем интересующим полям выглядит следующим образом:
ВЫБРАТЬ
ПП_КОЖ.НомерДоговора,
ПП_КОЖ.Сумма,
ПП_КОЖ.Менеджер,
ПП_КОЖ.Заемщик,
КОЛИЧЕСТВО(ПП_КОЖ.НомерДоговора) КАК Количество
ИЗ
РегистрСведений.ПП_КОЖ КАК ПП_КОЖ
СГРУППИРОВАТЬ ПО
ПП_КОЖ.НомерДоговора,
ПП_КОЖ.Сумма,
ПП_КОЖ.Менеджер,
ПП_КОЖ.Заемщик
ИМЕЮЩИЕ
КОЛИЧЕСТВО(ПП_КОЖ.НомерДоговора) > 1
Проанализируем ситуацию: почему этот запрос вернет пустой результат или покажет количество, равное 1, даже если в базе есть три строки с одинаковым номером договора? Дело в том, что СУБД группирует записи по уникальной комбинации всех указанных полей. Если у записей совпадает НомерДоговора, но отличаются значения в колонках Сумма, Менеджер или Заемщик, то для СУБД это будут абсолютно разные группировочные строки. В итоге мы получим несколько строк с количеством 1, и условие раздела ИМЕЮЩИЕ (HAVING) просто отсечет их из результата.
Если же мы оставим в группировке только одно ключевое поле, по которому ищем дубли (например, НомерДоговора):
ВЫБРАТЬ
ПП_КОЖ.НомерДоговора,
КОЛИЧЕСТВО(ПП_КОЖ.НомерДоговора) КАК Количество
ИЗ
РегистрСведений.ПП_КОЖ КАК ПП_КОЖ
СГРУППИРОВАТЬ ПО
ПП_КОЖ.НомерДоговора
ИМЕЮЩИЕ
КОЛИЧЕСТВО(ПП_КОЖ.НомерДоговора) > 1
Такой запрос действительно корректно найдет все повторяющиеся номера договоров. Но мы сталкиваемся с другой проблемой: в результате запроса у нас будет только сам номер договора и количество его дублей. Получить информацию о суммах, менеджерах и заемщиках напрямую из этого запроса невозможно, так как эти поля нельзя добавить в выборку без добавления их в секцию СГРУППИРОВАТЬ ПО.
Чтобы решить эту задачу, мы применим классический двухэтапный подход. Выясним причину популярности этого метода: он прост в отладке, работает быстро и позволяет вытянуть абсолютно любые детальные поля для найденных дубликатов.
Шаг 1. Создание временной таблицы с дублями
Сначала мы пишем запрос, который находит только неуникальные ключевые поля (в нашем примере это НомерДоговора) и помещает их во временную таблицу. Назовем ее Дубли.
Шаг 2. Соединение временной таблицы с исходным регистром
Во втором запросе пакета мы выбираем данные из нашей временной таблицы Дубли и соединяем ее с физической таблицей регистра РегистрСведений.ПП_КОЖ по ключевому полю. Таким образом, система выберет из базы только те строки, номера договоров которых присутствуют в списке дубликатов, и при этом позволит нам обратиться к любым детальным полям исходных записей.
Посмотрим на готовый и оптимизированный пример кода:
ВЫБРАТЬ
ПП_КОЖ.НомерДоговора КАК НомерДоговора
ПОМЕСТИТЬ ВТ_ДублирующиеДоговоры
ИЗ
РегистрСведений.ПП_КОЖ КАК ПП_КОЖ
СГРУППИРОВАТЬ ПО
ПП_КОЖ.НомерДоговора
ИМЕЮЩИЕ
КОЛИЧЕСТВО(ПП_КОЖ.НомерДоговора) > 1
ИНДЕКСИРОВАТЬ
НомерДоговора
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ДублирующиеДоговоры.НомерДоговора,
ПП_КОЖ_Детали.Сумма,
ПП_КОЖ_Детали.Менеджер,
ПП_КОЖ_Детали.Заемщик
ИЗ
ВТ_ДублирующиеДоговоры КАК ВТ_ДублирующиеДоговоры
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ПП_КОЖ КАК ПП_КОЖ_Детали
ПО ВТ_ДублирующиеДоговоры.НомерДоговора = ПП_КОЖ_Детали.НомерДоговора
Обратите внимание на два ключевых момента в приведенном выше коде, которые критически важны для быстродействия вашей базы данных:
ИНДЕКСИРОВАТЬ НомерДоговора указывает СУБД на необходимость создания индекса по полю связи во временной таблице. Если в вашем регистре содержатся десятки или сотни тысяч записей, отсутствие индекса приведет к тому, что для каждой строки основного регистра СУБД будет выполнять полное сканирование (Table Scan) временной таблицы. Это вызовет колоссальную нагрузку на дисковую подсистему и процессор, вплоть до зависания сеанса.ЛЕВОЕ СОЕДИНЕНИЕ. Однако в данном сценарии правильнее использовать ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Поскольку во временной таблице гарантированно лежат только те номера договоров, которые физически существуют в регистре, левое соединение здесь избыточно. Внутреннее соединение дает СУБД больше свободы при построении оптимального плана выполнения запроса.Иногда требуется решить задачу в один шаг, например, когда на платформу накладываются ограничения (некоторые внешние источники данных или особенности работы в определенных режимах интеграции). В этом случае можно применить классический SQL-прием — внутреннее соединение таблицы самой с собой. Мы соединяем две копии одной и той же таблицы по ключевому полю, но добавляем условие неравенства других значимых параметров, чтобы строка не соединилась сама с собой.
Рассмотрим пример такого запроса:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ПП_КОЖ_1.НомерДоговора,
ПП_КОЖ_1.Сумма,
ПП_КОЖ_1.Менеджер,
ПП_КОЖ_1.Заемщик
ИЗ
РегистрСведений.ПП_КОЖ КАК ПП_КОЖ_1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ПП_КОЖ КАК ПП_КОЖ_2
ПО ПП_КОЖ_1.НомерДоговора = ПП_КОЖ_2.НомерДоговора
И (ПП_КОЖ_1.Сумма <> ПП_КОЖ_2.Сумма
ИЛИ ПП_КОЖ_1.Менеджер <> ПП_КОЖ_2.Менеджер
ИЛИ ПП_КОЖ_1.Заемщик <> ПП_КОЖ_2.Заемщик)
Этот запрос сразу вернет все строки регистра, у которых совпадает НомерДоговора, но при этом отличается хотя бы один из других значимых реквизитов. Плюс этого метода — лаконичность кода и отсутствие необходимости использовать менеджер временных таблиц. Минус — на очень больших таблицах самосоединение без использования индексов по всем сравниваемым полям может работать медленнее, чем вариант с индексированной временной таблицей.
Разберем методологический вопрос: а как вообще физически в регистре сведений могут появиться дубли? Платформа 1С:Предприятие жестко контролирует уникальность записей в регистрах по ключу, состоящему из Периода (для периодических регистров) и всех его Измерений. Записать две абсолютно идентичные по ключу строки СУБД просто не позволит.
Если вы обнаружили «дубли» по полю НомерДоговора, это может означать следующее:
НомерДоговора является обычным Реквизитом или Ресурсом регистра, а измерениями выступают совсем другие поля.НомерДоговора является измерением, но кроме него в регистре есть другие измерения (например, Организация или ВидПлатежа). Уникальность записей контролируется по их совокупности, что и позволяет записать несколько строк по одному договору.Важный совет: Если регистр периодический и вам нужны только актуальные на сегодняшний день данные (без учета устаревших версий), стройте запрос не к физической таблице регистра, а к виртуальной таблице СрезПоследних:
ВЫБРАТЬ
ПП_КОЖ_Срез.НомерДоговора,
ПП_КОЖ_Срез.Сумма,
ПП_КОЖ_Срез.Менеджер,
ПП_КОЖ_Срез.Заемщик
ИЗ
РегистрСведений.ПП_КОЖ.СрезПоследних(&Период, ) КАК ПП_КОЖ_Срез
Использование виртуальной таблицы СрезПоследних автоматически отсечет старые записи и избавит вас от ложных дублей, связанных с изменением данных во времени.
В процессе обсуждения подобных проблем разработчики часто рекомендуют воспользоваться готовой системной обработкой «Поиск и удаление дублей», которая входит в состав Библиотеки стандартных подсистем (БСП). Однако здесь кроется важное ограничение, о котором необходимо знать.
Стандартный механизм поиска и замены дублей БСП предназначен исключительно для работы со ссылочными типами данных (Справочники, Документы). Он находит одинаковые элементы (например, дубли контрагентов «Ромашка» и «ООО Ромашка»), перенаправляет на один выбранный «правильный» элемент все ссылки из документов и регистров, после чего удаляет или помечает на удаление дублирующиеся объекты.
Регистры сведений не являются ссылочными сущностями. Их записи не имеют уникального идентификатора (UUID) и ссылки (Ref). Поэтому стандартная обработка БСП физически не сможет очистить или объединить дубли в регистре сведений. Эту задачу вам придется решать программно. На основании полученной таблицы дублей нужно будет написать обработку, которая с помощью объекта РегистрСведенийНаборЗаписей с установленным отбором по измерениям будет перезаписывать или удалять некорректные строки. Для выборочной очистки и изменения данных можно использовать универсальную обработку для удаления и редактирования записей регистров сведений.