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