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