В процессе разработки или поддержки нестандартных конфигураций 1С часто возникает задача контроля чистоты данных. Типичный пример — документ инвентаризации, куда данные вносятся вручную. Представим ситуацию: кладовщик сканирует или вводит коды товаров, и для одной и той же номенклатуры в разных строках оказываются разные штрихкоды. Это может быть как ошибкой ввода, так и следствием того, что на одну позицию заведено несколько разных этикеток.
В этой статье мы подробно разберем, как одним запросом выявить такие расхождения, проанализируем различные подходы к решению задачи и рассмотрим способы оптимизации кода для больших объемов данных.
Рассмотрим структуру документа ИнвентаризацияНаСкладе. У него есть табличная часть Товары со следующими реквизитами:
Номенклатура — ссылка на справочник;Код — строковый реквизит (штрихкод или артикул), заполняемый вручную;Количество — числовое значение.Наша цель — получить таблицу, в которую попадут только те строки документа, где для одной и той же номенклатуры указано более одного уникального значения в поле Код. Если у товара во всех строках код одинаковый, такие записи нас не интересуют.
Самый прямой и понятный способ — это сначала определить список "проблемной" номенклатуры, а затем выбрать все строки из документа, которые относятся к этому списку. Разберем этот алгоритм по шагам.
Для начала нам нужно найти номенклатуру, у которой количество различных кодов больше единицы. Для этого мы используем группировку по полю Номенклатура и агрегатную функцию КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...).
Посмотрим на пример кода, предложенный в обсуждении:
ВЫБРАТЬ
Док.Номенклатура КАК Номенклатура,
Док.Код КАК Код,
Док.Количество
ИЗ
Документ.ИнвентаризацияНаСкладе.Товары КАК Док
ГДЕ
Док.Ссылка = &Ссылка
И Док.Номенклатура В (
ВЫБРАТЬ
ВложенныйДок.Номенклатура
ИЗ
Документ.ИнвентаризацияНаСкладе.Товары КАК ВложенныйДок
ГДЕ
ВложенныйДок.Ссылка = &Ссылка
СГРУППИРОВАТЬ ПО
ВложенныйДок.Номенклатура
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВложенныйДок.Код) > 1
)
Почему это работает?
В) сканирует табличную часть документа и схлопывает все строки по номенклатуре.ИМЕЮЩИЕ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВложенныйДок.Код) > 1 отфильтровывает только те товары, у которых в поле Код встречается минимум два разных значения.Использование конструкции ГДЕ Номенклатура В (...) удобно для простых отчетов, однако на больших документах (например, инвентаризация крупного гипермаркета на 10-20 тысяч строк) такой запрос может работать медленно. Проанализируем причину: СУБД приходится выполнять подзапрос для каждой проверки условия в основной таблице.
Для повышения производительности рекомендуется использовать МенеджерВременныхТаблиц и соединение таблиц. Рассмотрим этот метод:
// 1. Помещаем список проблемных товаров во временную таблицу
ВЫБРАТЬ
ТЧ.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ПроблемныеТовары
ИЗ
Документ.ИнвентаризацияНаСкладе.Товары КАК ТЧ
ГДЕ
ТЧ.Ссылка = &Ссылка
СГРУППИРОВАТЬ ПО
ТЧ.Номенклатура
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ТЧ.Код) > 1
;
// 2. Соединяем временную таблицу с исходными данными
ВЫБРАТЬ
Док.Номенклатура,
Док.Код,
Док.Количество
ИЗ
Документ.ИнвентаризацияНаСкладе.Товары КАК Док
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПроблемныеТовары КАК Список
ПО Док.Номенклатура = Список.Номенклатура
ГДЕ
Док.Ссылка = &Ссылка
Такой подход позволяет платформе 1С и SQL-серверу построить более эффективный план запроса. Внутреннее соединение работает быстрее, так как оно сразу отсекает ненужные строки основной таблицы.
Если ваша база работает на платформе 1С версии 8.3.20 и выше, мы можем применить оконные функции. Это позволяет избежать повторного обращения к одной и той же таблице и сделать код более элегантным.
Проанализируем ситуацию: нам нужно в контексте каждой строки знать, сколько всего уникальных кодов есть у этой номенклатуры в данном документе. Для этого используется конструкция ОБЪЕДИНИТЬ ПО (аналог SQL-ного OVER PARTITION BY).
ВЫБРАТЬ
Результат.Номенклатура,
Результат.Код,
Результат.Количество
ИЗ (
ВЫБРАТЬ
Док.Номенклатура,
Док.Код,
Док.Количество,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Док.Код) ОБЪЕДИНИТЬ ПО (Док.Номенклатура) КАК ВсегоРазныхКодов
ИЗ
Документ.ИнвентаризацияНаСкладе.Товары КАК Док
ГДЕ
Док.Ссылка = &Ссылка
) КАК Результат
ГДЕ
Результат.ВсегоРазныхКодов > 1
Этот запрос выполняет расчет "на лету" для каждой строки. Если значение в виртуальном поле ВсегоРазныхКодов больше 1, значит, данная строка является частью "проблемного" набора.
При поиске расхождений в кодах (штрихкодах) часто возникают ложные срабатывания. Выясним причину их появления и методы устранения.
СОКРЛП(Док.Код).КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Код) может посчитать пустую строку как отдельный уникальный код. Если это не считается ошибкой, добавьте условие ГДЕ Док.Код <> "".Иногда требуется решить обратную задачу: найти ситуации, когда номенклатура и код совпадают, но товар введен в документ несколькими разными строками (дублирование). В этом случае мы группируем по обоим полям и проверяем количество строк:
ВЫБРАТЬ
ТЧ.Номенклатура,
ТЧ.Код,
КОЛИЧЕСТВО(ТЧ.НомерСтроки) КАК КоличествоСтрок
ИЗ
Документ.ИнвентаризацияНаСкладе.Товары КАК ТЧ
ГДЕ
ТЧ.Ссылка = &Ссылка
СГРУППИРОВАТЬ ПО
ТЧ.Номенклатура,
ТЧ.Код
ИМЕЮЩИЕ
КОЛИЧЕСТВО(ТЧ.НомерСтроки) > 1
Такой запрос поможет вам оптимизировать документ, предложив объединить несколько одинаковых строк в одну, что значительно упростит работу кладовщика при проверке результатов инвентаризации.
Подводя итог, можно сказать, что механизм группировок с использованием ИМЕЮЩИЕ является мощным инструментом для анализа качества данных. Выбирайте наиболее подходящий метод (вложенный запрос, временные таблицы или оконные функции) в зависимости от производительности вашей системы и версии платформы.