В практике разработки на платформе 1С:Предприятие часто возникает задача поиска дублирующихся данных. Типичный пример — необходимость найти элементы справочника, которые были ошибочно или намеренно выбраны в нескольких различных документах. В этой статье мы подробно разберем, как решить эту задачу с помощью языка запросов 1С, проанализируем различные подходы с точки зрения производительности СУБД и выберем наиболее оптимальный вариант, соответствующий стандартам разработки.
Представим ситуацию, когда у нас есть табличная часть документа или временная таблица, содержащая две колонки: Серия (ссылка на справочник серий номенклатуры) и Документ (ссылка на документ-регистратор, например, "Поступление товаров и услуг").
Нам необходимо написать запрос, который выберет только те серии, которые используются более чем в одном документе, и выведет все строки с этими дубликатами в формате:
Разберем классическое решение, которое часто используют разработчики на начальном этапе. Чтобы отсечь серии, которые встречаются только один раз, мы можем использовать конструкцию группировки с условием ИМЕЮЩИЕ (аналог секции HAVING в SQL). Мы подсчитываем количество уникальных документов для каждой серии, и если это количество больше 1, значит, перед нами дубликат.
Запрос с использованием вложенного подзапроса выглядит следующим образом:
ВЫБРАТЬ
ТабЧасть.Серия КАК Серия,
ТабЧасть.Документ КАК Документ
ИЗ
ДокументСерия КАК ТабЧасть
ГДЕ
ТабЧасть.Серия В
(ВЫБРАТЬ
ВТ.Серия КАК Серия
ИЗ
ДокументСерия КАК ВТ
СГРУППИРОВАТЬ ПО
ВТ.Серия
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТ.Документ) > 1)
УПОРЯДОЧИТЬ ПО
Серия
Как работает этот запрос? Во вложенном подзапросе мы группируем исходную таблицу по полю Серия и накладываем условие КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТ.Документ) > 1. Это позволяет получить чистый список "проблемных" серий. Затем во внешнем запросе мы выбираем все записи из исходной таблицы, где серия входит в полученный нами список дубликатов.
Несмотря на то, что данный запрос полностью решает задачу и возвращает корректный результат, он имеет серьезные проблемы с производительностью при работе на реальных (промышленных) объемах данных.
Выясним причину, почему конструкция с подзапросом ГДЕ ... В (ВЫБРАТЬ ... ИМЕЮЩИЕ ...) работает неоптимально в профайлере СУБД (MS SQL Server или PostgreSQL):
ГДЕ... В СУБД не может заранее точно рассчитать, сколько строк вернет этот подзапрос. Ошибившись в оценке количества записей, СУБД может выбрать неэффективный план.Hash Match) или соединения слиянием (Merge Join), СУБД может применить вложенные циклы (Nested Loops). В этом случае вложенный запрос будет выполняться повторно для каждой строки основной таблицы, что приведет к колоссальному росту чтений с диска и зависанию системы.IN (SELECT ...), преобразуя ее в полусоединение (Left Semi Join), то PostgreSQL на сложных подзапросах с агрегацией часто скатывается к полному сканированию таблиц (Seq Scan), полностью игнорируя существующие индексы.Использование Временных таблиц позволяет полностью обойти эти проблемы. Разделяя сложный запрос на простые шаги, мы помогаем оптимизатору СУБД построить предсказуемый и максимально быстрый план выполнения.
Рассмотрим, как должен выглядеть оптимальный и профессиональный запрос по стандартам разработки 1С. Мы разделим его на два шага: сначала выделим дубликаты во временную таблицу и проиндексируем её, а затем выполним внутреннее соединение с исходными данными.
// Шаг 1. Отбираем дублирующиеся серии во временную таблицу и индексируем её
ВЫБРАТЬ
ВТ.Серия КАК Серия
ПОМЕСТИТЬ ВТ_ДубликатыСерий
ИЗ
ДокументСерия КАК ВТ
СГРУППИРОВАТЬ ПО
ВТ.Серия
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТ.Документ) > 1
ИНДЕКСИРОВАТЬ ПО
Серия
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2. Соединяем исходную таблицу с временной по внутреннему соединению
ВЫБРАТЬ
ТабЧасть.Серия КАК Серия,
ТабЧасть.Документ КАК Документ
ИЗ
ДокументСерия КАК ТабЧасть
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ДубликатыСерий КАК ВТ_Дубли
ПО ТабЧасть.Серия = ВТ_Дубли.Серия
УПОРЯДОЧИТЬ ПО
Серия
Проанализируем преимущества этого подхода:
ИМЕЮЩИЕ и записывает результат во временную таблицу в базе данных tempdb.ИНДЕКСИРОВАТЬ ПО Серия заставляет СУБД мгновенно построить индекс по полю связи. Согласно стандартам 1С, индексация полей связи во временных таблицах обязательна, если эти таблицы в дальнейшем участвуют в соединениях.ВТ_ДубликатыСерий и наличие индекса на ней. Это гарантирует выбор идеального физического оператора (например, Index Seek и Merge Join), что обеспечивает максимальную скорость работы.Если ваша база данных содержит миллионы записей, использование конструкции КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...) может сильно замедлить выполнение запроса. СУБД вынуждена выполнять операцию уникализации данных в оперативной памяти (оператор Distinct Sort в плане выполнения), что создает огромную нагрузку на процессоры сервера.
Мы можем оптимизировать этот шаг, избавившись от ключевого слова РАЗЛИЧНЫЕ. Для этого мы сначала сгруппируем данные по уникальным парам "Серия - Документ", а уже потом посчитаем количество записей. Рассмотрим этот продвинутый вариант:
// Шаг 1. Получаем уникальные пары Серия-Документ (избавляемся от повторов внутри одного документа)
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВТ.Серия КАК Серия,
ВТ.Документ КАК Документ
ПОМЕСТИТЬ ВТ_УникальныеПары
ИЗ
ДокументСерия КАК ВТ
ИНДЕКСИРОВАТЬ ПО
Серия
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2. Ищем дубликаты серий без использования тяжелой функции РАЗЛИЧНЫЕ
ВЫБРАТЬ
ВТ_Пары.Серия КАК Серия
ПОМЕСТИТЬ ВТ_ДубликатыБезРазличные
ИЗ
ВТ_УникальныеПары КАК ВТ_Пары
СГРУППИРОВАТЬ ПО
ВТ_Пары.Серия
ИМЕЮЩИЕ
КОЛИЧЕСТВО(ВТ_Пары.Серия) > 1
ИНДЕКСИРОВАТЬ ПО
Серия
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3. Соединяем уникальные пары с таблицей дубликатов
ВЫБРАТЬ
ТабЧасть.Серия КАК Серия,
ТабЧасть.Документ КАК Документ
ИЗ
ВТ_УникальныеПары КАК ТабЧасть
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ДубликатыБезРазличные КАК ВТ_Дубли
ПО ТабЧасть.Серия = ВТ_Дубли.Серия
УПОРЯДОЧИТЬ ПО
Серия
Такой трехэтапный подход работает значительно быстрее на терабайтных базах данных, так как мы заменяем тяжелую сортировку уникализации в памяти на простые и последовательные операции индексации и группировки.
В современных версиях платформы 1С:Предприятие 8 (начиная с релиза 8.3.20) появилась долгожданная поддержка оконных функций. С их помощью мы можем решить задачу в один проход, избавившись от необходимости повторного соединения таблиц.
Однако здесь есть важное ограничение: в оконных функциях языка запросов 1С не поддерживается ключевое слово РАЗЛИЧНЫЕ (DISTINCT). Написать конструкцию вида КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Документ) НАД (РАЗБИТЬ ПО Серия) напрямую невозможно.
Тем не менее, мы можем применить обходной путь: сначала свернуть таблицу до уникальных пар "Серия - Документ", а затем рассчитать количество использований с помощью оконного оператора НАД (РАЗБИТЬ ПО ...). Посмотрим, как это выглядит в коде запроса:
// Шаг 1. Предварительный расчет уникальных пар с использованием оконной функции
ВЫБРАТЬ
СвернутаяТЧ.Серия КАК Серия,
СвернутаяТЧ.Документ КАК Документ,
КОЛИЧЕСТВО(СвернутаяТЧ.Документ) НАД (РАЗБИТЬ ПО СвернутаяТЧ.Серия) КАК КоличествоИспользований
ПОМЕСТИТЬ ВТ_ПредварительныйРасчет
ИЗ
(ВЫБРАТЬ РАЗЛИЧНЫЕ
ДокументСерия.Серия КАК Серия,
ДокументСерия.Документ КАК Документ
ИЗ
ДокументСерия КАК ДокументСерия) КАК СвернутаяТЧ
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2. Выборка результатов, где количество использований серии больше 1
ВЫБРАТЬ
ВТ.Серия КАК Серия,
ВТ.Документ КАК Документ
ИЗ
ВТ_ПредварительныйРасчет КАК ВТ
ГДЕ
ВТ.КоличествоИспользований > 1
УПОРЯДОЧИТЬ ПО
Серия
Этот вариант особенно эффективен при работе на СУБД PostgreSQL. Оконные функции позволяют серверу БД рассчитать количество дубликатов "на лету" в рамках одного сканирования набора данных, исключая дорогостоящие операции повторного чтения физических таблиц с диска.
Таким образом, мы детально разобрали эволюцию решения задачи поиска дубликатов в 1С: от простейшего подзапроса с ИМЕЮЩИЕ до высокопроизводительного пакетного запроса с временными таблицами, индексами и современными оконными функциями. Выбирайте вариант в зависимости от объема ваших данных и версии используемой платформы!