Как найти разницу между таблицей значений и справочником в запросе 1С

← На главную

В процессе разработки на платформе 1С:Предприятие 8 часто возникает задача сопоставления данных, находящихся «в памяти» (в таблице значений), с данными, которые уже записаны в базе данных (в справочнике). Типичный пример: импорт данных из внешнего файла, когда нам нужно выделить только те записи, которых еще нет в системе, чтобы избежать дублирования. Рассмотрим подробно, как эффективно решить эту задачу с помощью механизмов запросов, разберем типичные ошибки и способы оптимизации производительности.

Основная концепция: Использование левого соединения

Для поиска записей, которые присутствуют в одной таблице, но отсутствуют в другой, в языке SQL (и в языке запросов 1С) традиционно используется ЛЕВОЕ СОЕДИНЕНИЕ. Логика проста: мы берем нашу основную таблицу (ТЗ) и пытаемся «приклеить» к ней данные из справочника по ключевым полям. Если соответствие не найдено, поля из справочника в результате запроса будут содержать значение NULL. Именно это значение и служит индикатором отсутствия записи.

Разберем по шагам, как реализовать этот алгоритм. Сначала нам необходимо подготовить таблицу значений, правильно типизировав ее колонки, иначе запрос может отработать некорректно или выдать ошибку типов.


// Подготовка таблицы значений
тзНоменклатураДата = Новый ТаблицаЗначений;
тзНоменклатураДата.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
тзНоменклатураДата.Колонки.Добавить("ДатаПартии", Новый ОписаниеТипов("Дата"));

// Заполнение ТЗ данными...
// (код заполнения)

Теперь проанализируем структуру самого запроса. Нам нужно передать эту таблицу в запрос как параметр и поместить ее во временную таблицу для дальнейшей работы.


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	ВТ_ДанныеТЗ.Номенклатура КАК Номенклатура,
    |	ВТ_ДанныеТЗ.ДатаПартии КАК ДатаПартии
    |ПОМЕСТИТЬ ВТ_ИсходныеДанные
    |ИЗ
    |	&ТЗ КАК ВТ_ДанныеТЗ
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    |	ВТ_ИсходныеДанные.Номенклатура,
    |	ВТ_ИсходныеДанные.ДатаПартии
    |ИЗ
    |	ВТ_ИсходныеДанные КАК ВТ_ИсходныеДанные
    |		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Партии КАК СправочникПартии
    |		ПО ВТ_ИсходныеДанные.Номенклатура = СправочникПартии.Номенклатура
    |			И ВТ_ИсходныеДанные.ДатаПартии = СправочникПартии.ДатаПартии
    |ГДЕ
    |	СправочникПартии.Ссылка ЕСТЬ NULL";

Запрос.УстановитьПараметр("ТЗ", тзНоменклатураДата);
РезультатЗапроса = Запрос.Выполнить();

В данном примере условие ГДЕ СправочникПартии.Ссылка ЕСТЬ NULL отсекает все строки, для которых в справочнике нашлось совпадение. В результате мы получаем только те уникальные комбинации «Номенклатура — Дата», которых в базе еще нет.

Проблема точности сравнения дат

Одной из самых частых причин, по которой запрос «не видит» существующие записи, является разница в составе даты. В 1С тип Дата всегда содержит время (час, минута, секунда). Если в вашей таблице значений дата указана как 01.01.2023 12:00:00, а в справочнике она записана как 01.01.2023 00:00:00, прямое сравнение через оператор = вернет «Ложь», и запись будет считаться отсутствующей.

Чтобы избежать этой проблемы, проанализируем ситуацию и применим функцию НАЧАЛОПЕРИОДА. Это позволит нам сравнивать только календарные даты, игнорируя время.


// Фрагмент запроса с корректировкой по дате
|		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Партии КАК СправочникПартии
|		ПО ВТ_ИсходныеДанные.Номенклатура = СправочникПартии.Номенклатура
|			И (НАЧАЛОПЕРИОДА(ВТ_ИсходныеДанные.ДатаПартии, ДЕНЬ) = НАЧАЛОПЕРИОДА(СправочникПартии.ДатаПартии, ДЕНЬ))

Важный нюанс: Использование функций в условиях соединения может замедлить работу запроса на очень больших объемах данных, так как СУБД не сможет эффективно использовать индексы. Если производительность критична, лучше заранее привести все даты в ТЗ и в справочнике к «пустому» времени (началу дня) при записи.

Оптимизация: Индексация временных таблиц

Если ваша таблица значений содержит тысячи строк, а справочник — десятки тысяч, обычное соединение может выполняться долго. Выясним причину: системе приходится многократно сканировать временную таблицу. Чтобы ускорить процесс, необходимо добавить индексацию по полям связи.

Рассмотрим, как изменить запрос для включения индексов:


|ВЫБРАТЬ
|	ВТ_ДанныеТЗ.Номенклатура КАК Номенклатура,
|	ВТ_ДанныеТЗ.ДатаПартии КАК ДатаПартии
|ПОМЕСТИТЬ ВТ_ИсходныеДанные
|ИЗ
|	&ТЗ КАК ВТ_ДанныеТЗ
|ИНДЕКСИРОВАТЬ ПО
|	Номенклатура,
|	ДатаПартии
|;

Индексирование временной таблицы — это «золотое правило» при работе со сложными запросами. Это позволяет SQL-серверу мгновенно находить нужные строки при выполнении соединения.

Альтернативный метод: Группировка и весовые коэффициенты

Существует еще один интересный алгоритм, который часто применяется для сравнения двух наборов данных. Его можно назвать «методом весов». Он особенно удобен, когда нужно сравнить две таблицы значений или когда нужно понять не только отсутствие, но и разницу в количестве дублей.

Суть метода заключается в следующем:

Выбираем данные из первой таблицы (ТЗ) и добавляем вычисляемое поле Счетчик со значением 1.
Выбираем данные из второй таблицы (Справочник) и добавляем поле Счетчик со значением -1.
Объединяем эти выборки через ОБЪЕДИНИТЬ ВСЕ.
Группируем результат по ключевым полям и суммируем Счетчик.

Если в итоге сумма равна 0 — записи идентичны. Если сумма 1 — запись есть только в ТЗ. Если -1 — запись есть только в справочнике. Рассмотрим пример кода:


ВЫБРАТЬ
	ТЗ.Номенклатура,
	ТЗ.ДатаПартии,
	1 КАК Счетчик
ПОМЕСТИТЬ ВТ_Общая
ИЗ
	&ТЗ КАК ТЗ

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	Спр.Номенклатура,
	Спр.ДатаПартии,
	-1
ИЗ
	Справочник.Партии КАК Спр;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ВТ_Общая.Номенклатура,
	ВТ_Общая.ДатаПартии
ИЗ
	ВТ_Общая КАК ВТ_Общая
СГРУППИРОВАТЬ ПО
	ВТ_Общая.Номенклатура,
	ВТ_Общая.ДатаПартии
ИМЕЮЩИЕ
	СУММА(ВТ_Общая.Счетчик) > 0

Этот метод работает чрезвычайно быстро и часто используется в задачах сверки остатков или больших массивов данных, где классическое соединение может быть менее наглядным.

Тонкости работы со строками и пометками на удаление

Проанализируем еще несколько факторов, которые могут повлиять на результат сравнения:

Пометки на удаление: Если ваша задача — найти разницу только с «живыми» элементами, не забудьте добавить фильтр ГДЕ НЕ СправочникПартии.ПометкаУдаления. Иначе запрос может найти соответствие в корзине, и вы пропустите создание нужного элемента.
Строковые поля: Если сравнение идет по строкам (например, по Артикулу), помните про лишние пробелы. В справочниках строки могут быть фиксированной длины. Рекомендуется использовать функцию СОКРЛП (хотя в запросе ее применять нельзя к полям справочника напрямую, лучше очистить данные в ТЗ перед передачей в запрос).
Пустые ссылки: При сравнении объектов (ссылочных типов) помните, что NULL — это не то же самое, что Справочники.Номенклатура.ПустаяСсылка(). В условии ГДЕ ... ЕСТЬ NULL проверяется именно отсутствие записи в физической таблице базы данных.

Подводя итог, можно сказать, что наиболее надежным и гибким способом является использование ЛЕВОГО СОЕДИНЕНИЯ с обязательной типизацией входящей ТЗ и индексированием временных таблиц. Это обеспечит предсказуемость результата и высокую скорость работы системы.