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