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