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