В практике разработки на платформе 1С часто возникает задача поиска «дырок» в данных. Типичный пример: у нас есть список контрагентов и список обязательных типов документов. Нам нужно быстро выяснить, у каких контрагентов каких именно документов не хватает. В терминах теории множеств это называется поиском дополнения или Anti-Join. В языке запросов 1С нет прямых операторов вроде EXCEPT или MINUS, которые есть в классическом SQL, поэтому задачу приходится решать через комбинацию нескольких приемов.
В этой статье мы подробно разберем, как построить запрос, который выявит все отсутствующие комбинации между двумя наборами данных, проанализируем производительность и рассмотрим «подводные камни» этого метода.
Представим, что у нас есть две таблицы. В первой — существующие связи (например, Имя и Буква), во второй — полный перечень всех возможных букв. Нам нужно получить таблицу, где для каждого имени будут указаны только те буквы, которых у него нет в первой таблице.
Рассмотрим пример исходных данных:
Таблица 1 (Связи):
"Первый" — "А"
"Первый" — "Б"
"Второй" — "В"
Таблица 2 (Все варианты):
"А", "Б", "В", "Г"
Результат, который мы хотим получить:
"Первый" — "В", "Первый" — "Г"
"Второй" — "А", "Второй" — "Б", "Второй" — "Г"
Проанализируем ситуацию: чтобы понять, чего не хватает, нам сначала нужно понять, а что могло бы быть. То есть нам нужно построить полную матрицу всех возможных комбинаций «каждое Имя из Таблицы 1» + «каждая Буква из Таблицы 2», а затем вычесть из этой матрицы те строки, которые уже существуют в Таблице 1.
Первым делом нам необходимо создать временные таблицы для работы. В реальных задачах это могут быть справочники или физические таблицы базы данных, но для примера мы сформируем их прямо в запросе.
Чтобы построить матрицу всех возможных связей, мы используем соединение таблиц без указания условия (запятая в тексте запроса или СОЕДИНЕНИЕ по условию ИСТИНА). Это создаст декартово произведение — каждая строка первой таблицы соединится с каждой строкой второй.
// Создаем Таблицу 1 с существующими связями
ВЫБРАТЬ
"Первый" КАК Имя,
"А" КАК Буква
ПОМЕСТИТЬ Таблица1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Первый", "Б"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Второй", "В"
;
// Создаем Таблицу 2 со всеми доступными буквами
ВЫБРАТЬ
"А" КАК Буква
ПОМЕСТИТЬ Таблица2
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "Б"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "В"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "Г"
;
Теперь создадим таблицу ВсеСвязи. Важный нюанс: если в Таблица1 имена повторяются, нам нужно сначала получить список уникальных имен, иначе в матрице возникнут дубликаты, которые замедлят работу.
ВЫБРАТЬ РАЗЛИЧНЫЕ
Таблица1.Имя КАК Имя
ПОМЕСТИТЬ УникальныеИмена
ИЗ
Таблица1 КАК Таблица1
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
УникальныеИмена.Имя КАК Имя,
Таблица2.Буква КАК Буква
ПОМЕСТИТЬ ВсеВозможныеКомбинации
ИЗ
УникальныеИмена КАК УникальныеИмена,
Таблица2 КАК Таблица2
;
Теперь у нас есть таблица ВсеВозможныеКомбинации, в которой записаны абсолютно все варианты (например, для "Первого" это А, Б, В, Г). Нам нужно сравнить её с исходной Таблица1 и оставить только то, чего в Таблица1 нет.
Разберем, как это работает: мы выполняем ЛЕВОЕ СОЕДИНЕНИЕ полной матрицы с нашей исходной таблицей по обоим полям (Имя и Буква). Для тех строк, которые есть в обеих таблицах, поля из правой таблицы заполнятся значениями. Для тех строк, которых в исходной таблице нет, поля правой таблицы примут значение NULL. Нам остается только наложить фильтр.
ВЫБРАТЬ
ВсеКомбинации.Имя КАК Имя,
ВсеКомбинации.Буква КАК Буква
ИЗ
ВсеВозможныеКомбинации КАК ВсеКомбинации
ЛЕВОЕ СОЕДИНЕНИЕ Таблица1 КАК ИсходныеДанные
ПО ВсеКомбинации.Имя = ИсходныеДанные.Имя
И ВсеКомбинации.Буква = ИсходныеДанные.Буква
ГДЕ
ИсходныеДанные.Имя ЕСТЬ NULL
УПОРЯДОЧИТЬ ПО
Имя,
Буква
Этот паттерн называется Anti-Join. Он является наиболее эффективным способом в 1С для нахождения разности множеств по составному ключу (когда нужно проверять связку из двух и более полей).
Рассмотрим альтернативы, которые часто предлагают на форумах, и выясним их недостатки:
ГДЕ НЕ Поле В (ВЫБРАТЬ Поле ИЗ...) отлично работает для одного поля. Но 1С не поддерживает сравнение кортежей, например: ГДЕ (Имя, Буква) НЕ В (...). Поэтому для нашей задачи этот метод неприменим.ПО Таблица1.Буква <> Таблица2.Буква. Это ошибка. Такое условие выдаст все комбинации, которые просто не совпадают в конкретных строках, но оно не гарантирует, что буквы совсем нет у данного пользователя. Оно лишь перемешает данные, создав огромный и бесполезный результат.РАЗЛИЧНЫЕ имена.Если ваши таблицы содержат тысячи или миллионы строк, выполнение декартового произведения может стать «тяжелой» операцией. Чтобы СУБД (MS SQL или PostgreSQL) работала эффективно, проанализируем следующие рекомендации:
1. Индексация временных таблиц.
При создании временных таблиц (УникальныеИмена, Таблица2, ВсеВозможныеКомбинации) обязательно указывайте индексацию по полям, по которым будет происходить соединение. В нашем случае это Имя и Буква.
2. Использование менеджера временных таблиц.
Разделяйте сложные запросы на этапы. Это упрощает построение плана запроса оптимизатором СУБД. Вынесение формирования матрицы в отдельный шаг (как мы сделали выше) — это хорошая практика.
3. Проверка на NULL.
Если в исходных данных поля Имя или Буква могут содержать NULL, логика сравнения в соединении может нарушиться (так как NULL = NULL в SQL всегда Ложь). Проанализируем ситуацию: перед обработкой лучше исключить NULL или заменить их на пустые значения с помощью функции ЕСТЬNULL().
Соберем все наши выводы в один эффективный блок кода, который можно использовать в реальной разработке:
// 1. Получаем список уникальных объектов
ВЫБРАТЬ РАЗЛИЧНЫЕ
Т1.Имя КАК Имя
ПОМЕСТИТЬ ВТ_Имена
ИЗ
Таблица1 КАК Т1
ИНДЕКСИРОВАТЬ ПО
Имя
;
// 2. Формируем полную матрицу (Декартово произведение)
ВЫБРАТЬ
ВТ_Имена.Имя КАК Имя,
Т2.Буква КАК Буква
ПОМЕСТИТЬ ВТ_Матрица
ИЗ
ВТ_Имена КАК ВТ_Имена
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Таблица2 КАК Т2
ПО (ИСТИНА)
ИНДЕКСИРОВАТЬ ПО
Имя,
Буква
;
// 3. Вычитаем существующие связи
ВЫБРАТЬ
Матрица.Имя,
Матрица.Буква
ИЗ
ВТ_Матрица КАК Матрица
ЛЕВОЕ СОЕДИНЕНИЕ Таблица1 КАК Факт
ПО Матрица.Имя = Факт.Имя
И Матрица.Буква = Факт.Буква
ГДЕ
Факт.Имя ЕСТЬ NULL
Рассмотренный метод является «золотым стандартом» для решения подобных задач в 1С. Несмотря на кажущуюся громоздкость из-за использования временных таблиц, он обеспечивает предсказуемый результат и высокую скорость работы за счет использования индексов и эффективных алгоритмов соединения СУБД.