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