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