При разработке сложных отчетов и механизмов проведения документов в 1С часто возникает задача вычитания наборов данных (получения разности двух таблиц). Разберем типичный сценарий: у нас есть две временные таблицы (например, ВТ1 и ВТ2), каждая из которых содержит составной ключ из полей Сотрудник и Дата. Нам необходимо выбрать из первой таблицы только те строки, пара значений которых отсутствует во второй таблице.
Рассмотрим по шагам основные способы решения этой задачи в языке запросов 1С, их особенности, а также нюансы производительности на различных СУБД.
Наиболее популярный, понятный и предсказуемый вариант вычитания данных — это использование левого соединения исходной таблицы ВТ1 с таблицей исключений ВТ2 по всем ключевым полям с фильтрацией тех строк, где записи в правой таблице не нашлись.
Проанализируем пример такого запроса:
ВЫБРАТЬ
ВТ1.Сотрудник КАК Сотрудник,
ВТ1.Дата КАК Дата
ИЗ
ВТ1 КАК ВТ1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ2 КАК ВТ2
ПО ВТ1.Сотрудник = ВТ2.Сотрудник
И ВТ1.Дата = ВТ2.Дата
ГДЕ
ВТ2.Сотрудник ЕСТЬ NULL
В секции ГДЕ мы проверяем условие ВТ2.Сотрудник ЕСТЬ NULL (или любого другого ключевого поля правой таблицы, которое гарантированно заполнено в исходных данных). Если совпадение по ключу не найдено, СУБД подставляет значение NULL для всех полей из ВТ2, и строка попадает в итоговый результат.
Язык запросов 1С позволяет использовать составные ключи (кортежи полей) в условии проверки вхождения ГДЕ НЕ (Поле1, Поле2) В (ВЫБРАТЬ ...). Это позволяет записать условие вычитания в более лаконичной форме.
Посмотрим на пример реализации данного подхода:
ВЫБРАТЬ
ВТ1.Сотрудник КАК Сотрудник,
ВТ1.Дата КАК Дата
ИЗ
ВТ1 КАК ВТ1
ГДЕ
НЕ (ВТ1.Сотрудник, ВТ1.Дата) В
(ВЫБРАТЬ
ВТ2.Сотрудник,
ВТ2.Дата
ИЗ
ВТ2 КАК ВТ2)
При использовании этого способа обратим внимание на следующие особенности синтаксиса платформы:
(ВТ1.Сотрудник, ВТ1.Дата) должны строго соответствовать колонкам в подзапросе из ВТ2.(Поле1, Поле2) В (...) разрешено использовать только в секциях ГДЕ и ИМЕЮЩИЕ. В секции ПО при связи таблиц такой синтаксис недопустим.NULL в составе проверяемых полей. В некоторых СУБД использование классического NOT IN при наличии NULL в подзапросе может привести к пустой итоговой выборке. Вариант с левым соединением в этом отношении более устойчив.Если временные таблицы содержат огромные объемы данных и построение индексов на них обходится слишком дорого, можно применить подход с агрегацией данных без использования операций соединения или подзапросов с отрицанием.
Разберем по шагам данный алгоритм:
ОБЪЕДИНИТЬ ВСЕ для обеих таблиц, добавив признак источника (например, значение 1 для первой таблицы и 100 для второй).СГРУППИРОВАТЬ ПО и фильтрацией в секции ИМЕЮЩИЕ.Пример реализации альтернативного метода:
ВЫБРАТЬ
ВТ1.Сотрудник КАК Сотрудник,
ВТ1.Дата КАК Дата,
1 КАК ПризнакИсточника
ПОМЕСТИТЬ ВТ_Объединенная
ИЗ
ВТ1 КАК ВТ1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВТ2.Сотрудник,
ВТ2.Дата,
100
ИЗ
ВТ2 КАК ВТ2
;
ВЫБРАТЬ
ВТ.Сотрудник КАК Сотрудник,
ВТ.Дата КАК Дата
ИЗ
ВТ_Объединенная КАК ВТ
СГРУППИРОВАТЬ ПО
ВТ.Сотрудник,
ВТ.Дата
ИМЕЮЩИЕ
СУММА(ВТ.ПризнакИсточника) = 1
Записи, присутствующие только в первой таблице, дадут сумму признака, равную 1. Если же пара Сотрудник и Дата есть во второй таблице, сумма превысит 1 (например, будет 101 и более), и запись отфильтруется.
Выясним, от чего зависит скорость работы рассмотренных методов вычитания таблиц:
ВТ2 обязательно указываем предложение ИНДЕКСИРОВАТЬ ПО:ПОМЕСТИТЬ ВТ2 ИНДЕКСИРОВАТЬ ПО Сотрудник, Дата.ГДЕ НЕ ... В и ЛЕВОЕ СОЕДИНЕНИЕ ... ГДЕ ЕСТЬ NULL к одинаковому физическому оператору Anti Semi Join. При наличии составного индекса скорость работы вариантов практически идентична.ГДЕ НЕ ... В может приводить к выбору менее эффективного плана (использование SubPlan). На этой СУБД вариант с ЛЕВОЕ СОЕДИНЕНИЕ ... ГДЕ ВТ2.Сотрудник ЕСТЬ NULL показывает стабильно лучшую скорость выполнения.Таким образом, наилучшей практикой для языка запросов 1С является применение левого соединения с проверкой на NULL в сочетании с индексированием ключевых полей во временных таблицах.