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