Рано или поздно любой разработчик 1С сталкивается с задачей поиска дубликатов в справочнике ФизическиеЛица. Эта проблема особенно актуальна для зарплатных и кадровых решений (таких как ЗУП или ERP), где задвоение карточек сотрудников ведет к критическим ошибкам в расчете налогов, взносов и формировании регламентированной отчетности.
В этой статье мы подробно разберем, как спроектировать оптимальный запрос для поиска дублирующихся записей, почему выполнение запросов в цикле является грубой ошибкой и как правильно организовать сопоставление данных по ФИО и СНИЛС, избегая коварных ловушек СУБД.
Начинающие разработчики часто пытаются решить задачу «в лоб»: получить таблицу физических лиц, обойти ее в цикле Для Каждого и на каждой итерации выполнять запрос к базе данных для поиска совпадений. Давайте выяснить, почему так делать категорически нельзя:
Правильный подход в парадигме 1С — это выполнение всей аналитической работы на стороне СУБД за один единый запрос.
Для того чтобы выявить дубликаты по определенному реквизиту (например, по представлению ФИО), мы должны сгруппировать записи справочника и отобрать только те группы, в которых количество элементов больше одного. Для этого в языке запросов 1С применяется секция ИМЕЮЩИЕ.
Рассмотрим базовый пример запроса, определяющий повторяющиеся ФИО:
ВЫБРАТЬ
ФизическиеЛица.ФИО КАК ФИО,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ФизическиеЛица.Ссылка) КАК КоличествоДублей
ИЗ
Справочник.ФизическиеЛица КАК ФизическиеЛица
СГРУППИРОВАТЬ ПО
ФизическиеЛица.ФИО
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ФизическиеЛица.Ссылка) > 1
Проанализируем этот запрос подробнее:
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...) подсчитывает число уникальных ссылок на элементы справочника для каждого конкретного значения ФИО.СГРУППИРОВАТЬ ПО объединяет все записи с одинаковыми именами.ИМЕЮЩИЕ КОЛИЧЕСТВО(...) > 1 отсекает уникальные записи, оставляя только те ФИО, которые встречаются в базе дважды или более раз.Важная деталь: Этот запрос покажет нам список проблемных ФИО и количество дублей, но мы не сможем получить ссылки (Ссылка) конкретных элементов-двойников. Если мы попытаемся добавить поле ФизическиеЛица.Ссылка в выборку и в группировку, СУБД сгруппирует данные по уникальным ссылкам. В итоге количество для каждой строки всегда будет равно 1, и условие ИМЕЮЩИЕ вернет пустой результат. Как же нам получить сами дублирующиеся элементы?
Чтобы извлечь ссылки на конкретные дублирующиеся элементы, нам нужно построить двухпакетный запрос с использованием временной таблицы. Разберем эту схему по шагам:
ВТ_Дубли).ФизическиеЛица по полю ФИО. Это позволит «развернуть» сгруппированные данные обратно и получить все дублирующиеся ссылки.Посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
ФизическиеЛица.ФИО КАК ФИО,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ФизическиеЛица.Ссылка) КАК КоличествоДублей
ПОМЕСТИТЬ ВТ_Дубли
ИЗ
Справочник.ФизическиеЛица КАК ФизическиеЛица
СГРУППИРОВАТЬ ПО
ФизическиеЛица.ФИО
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ФизическиеЛица.Ссылка) > 1
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Дубли.ФИО КАК ФИО,
ФизическиеЛица.Ссылка КАК Ссылка,
ФизическиеЛица.СтраховойНомерПФР КАК СНИЛС
ИЗ
ВТ_Дубли КАК ВТ_Дубли
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ФизическиеЛица КАК ФизическиеЛица
ПО ВТ_Дубли.ФИО = ФизическиеЛица.ФИО
УПОРЯДОЧИТЬ ПО
ФИО
Обратите внимание на использование ВНУТРЕННЕГО СОЕДИНЕНИЯ вместо левого. Поскольку во временной таблице гарантированно лежат только те ФИО, у которых есть дубли в справочнике, внутреннее соединение отработает максимально быстро и вернет строго нужные строки со всеми ссылками.
Довольно часто возникает задача, когда у нас есть внешняя таблица данных (например, загруженная из файла Excel), и нам нужно проверить, какие из этих людей уже зарегистрированы в нашей базе данных. При этом сопоставление желательно делать как по ФИО, так и по СНИЛС (Страховому номеру ПФР).
Сначала мы передаем внешнюю таблицу значений в качестве параметра запроса (назовем параметр &ТаблицаДанных), предварительно протипизировав ее колонки. Поместим ее во временную таблицу.
Опасная ловушка при связи по «ИЛИ»: Часто разработчики пытаются связать таблицы условием с оператором ИЛИ (OR):
// ПРИМЕР КАК ДЕЛАТЬ НЕ РЕКОМЕНДУЕТСЯ!
ПО (ФизическиеЛица.СтраховойНомерПФР = ВТ_ВходныеДанные.СНИЛС
ИЛИ ФизическиеЛица.ФИО = ВТ_ВходныеДанные.ФИО)
Использование ИЛИ в условиях соединения таблиц в СУБД приводит к катастрофическому падению производительности. Оптимизатор СУБД не может применить индексы и переходит к полному сканированию таблиц (Table Scan). Кроме того, если у вас в базе или во входящей таблице есть пустые значения СНИЛС, то все пустые СНИЛС свяжутся друг с другом, выдав огромное количество ложных дублей!
Для корректного и быстрого сопоставления по нескольким полям (ФИО и СНИЛС) лучше всего использовать разделение логики с помощью конструкции ОБЪЕДИНИТЬ ВСЕ. Мы разделяем поиск на две ветки: сначала ищем жесткие совпадения по уникальному СНИЛС (если он заполнен), а затем — по ФИО для тех записей, которые не удалось сопоставить по СНИЛС.
Давайте посмотрим на пример оптимизированного запроса сопоставления:
// Помещаем входную таблицу в запрос
ВЫБРАТЬ
Таблица.ФИО КАК ВходнойФИО,
Таблица.СНИЛС КАК ВходнойСНИЛС
ПОМЕСТИТЬ ВТ_ВходныеДанные
ИЗ
&ТаблицаДанных КАК Таблица
;
////////////////////////////////////////////////////////////////////////////////
// Поиск по СНИЛС (только если СНИЛС заполнен)
ВЫБРАТЬ
ВТ_ВходныеДанные.ВходнойФИО КАК ВходнойФИО,
ВТ_ВходныеДанные.ВходнойСНИЛС КАК ВходнойСНИЛС,
ФизическиеЛица.Ссылка КАК СсылкаНайдена,
"Совпадение по СНИЛС" КАК СпособПоиска
ИЗ
ВТ_ВходныеДанные КАК ВТ_ВходныеДанные
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ФизическиеЛица КАК ФизическиеЛица
ПО (ВТ_ВходныеДанные.ВходнойСНИЛС = ФизическиеЛица.СтраховойНомерПФР)
ГДЕ
ВТ_ВходныеДанные.ВходнойСНИЛС <> ""
И ВТ_ВходныеДанные.ВходнойСНИЛС <> " - - " // Маска пустого СНИЛС
ОБЪЕДИНИТЬ ВСЕ
// Поиск по ФИО (для записей, у которых нет СНИЛС или он не совпал)
ВЫБРАТЬ
ВТ_ВходныеДанные.ВходнойФИО,
ВТ_ВходныеДанные.ВходнойСНИЛС,
ФизическиеЛица.Ссылка,
"Совпадение по ФИО"
ИЗ
ВТ_ВходныеДанные КАК ВТ_ВходныеДанные
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ФизическиеЛица КАК ФизическиеЛица
ПО (ВТ_ВходныеДанные.ВходнойФИО = ФизическиеЛица.ФИО)
ГДЕ
(ВТ_ВходныеДанные.ВходнойСНИЛС = ""
ИЛИ ВТ_ВходныеДанные.ВходнойСНИЛС <> ФизическиеЛица.СтраховойНомерПФР)
Этот подход гарантирует стабильно высокую скорость работы СУБД, так как оба соединения используют простые равенства по одному полю, под которые СУБД легко строит эффективные индексные планы.
Для достижения 100% точности при поиске дубликатов физических лиц в реальных базах необходимо учитывать специфику хранения данных в типовых конфигурациях 1С:
"123-456-789 01") или сплошной строкой цифр ("12345678901"). Перед передачей таблицы в запрос обязательно очищайте строку СНИЛС от любых нецифровых символов. Сравнивать в запросе нужно только чистые цифровые последовательности.ФИОФизическихЛиц. Если вы ищете дубли только по реквизиту ФИО в самом справочнике, вы упустите ситуацию, когда один и тот же человек заведен под старой и новой фамилиями. Для глубокого поиска дублей рекомендуется делать соединение с таблицей регистра сведений РегистрСведений.ФИОФизическихЛиц.ПоискИУдалениеДублей. С ее помощью пользователи могут в интерактивном режиме объединять карточки, а разработчики могут обращаться к ее API программно через модуль ПоискИУдалениеДублейКлиентСервер для поиска схожих элементов без изобретения велосипедов.Применяя предложенные схемы построения запросов с использованием временных таблиц и разделением условий соединения, вы сможете быстро и безопасно находить дубликаты физических лиц даже на очень больших информационных базах.