В процессе работы торговых предприятий руководство часто ставит перед разработчиками задачу: проанализировать продажи по так называемым «новым» клиентам. Например, необходимо вывести продажи за 2018 год по тем контрагентам, которые ранее ничего у нас не покупали. На первый взгляд задача кажется тривиальной, однако при детальном рассмотрении всплывает множество нюансов: от методологии определения «новизны» клиента до серьезных проблем с производительностью СУБД при обработке больших массивов данных.
В этой статье мы подробно разберем, как решить эту задачу в рамках одного запроса, рассмотрим несколько вариантов реализации — от простых до максимально оптимизированных, а также обсудим архитектурные подходы, которые упростят получение подобных отчетов в будущем.
Прежде чем приступать к написанию кода, определимся с логикой отбора. Существует три основных подхода к определению даты «появления» клиента в базе:
Рассмотрим вариант запроса, который часто выбирают начинающие разработчики. Его суть заключается в том, чтобы выбрать все продажи за отчетный период и отсечь тех контрагентов, которые встречаются в продажах за прошлые периоды, используя конструкцию НЕ Контрагент В (...).
Посмотрим на пример такого запроса:
ВЫБРАТЬ
ПродажиОбороты.Контрагент КАК Контрагент,
ПродажиОбороты.КоличествоОборот КАК КоличествоОборот,
ПродажиОбороты.СтоимостьОборот КАК СтоимостьОборот
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ПродажиОбороты
ГДЕ
НЕ ПродажиОбороты.Контрагент В
(ВЫБРАТЬ
ПродажиПредыдущие.Контрагент
ИЗ
РегистрНакопления.Продажи.Обороты(, &НачалоПериода, , ) КАК ПродажиПредыдущие)
Почему этот вариант плох? Конструкция НЕ В (в терминах SQL — NOT IN) со вложенным подзапросом крайне неэффективно обрабатывается СУБД (MS SQL, PostgreSQL). При выполнении такого запроса СУБД не может построить оптимальный план использования индексов и зачастую прибегает к полному сканированию таблиц (Table Scan) или вложенным циклам (Nested Loops) для каждой строки. Если в вашей базе данных сотни тысяч записей о продажах, этот отчет будет выполняться очень долго и может полностью заблокировать работу пользователей.
Для того чтобы отчет работал быстро даже на высоконагруженных базах данных, мы должны использовать профессиональный паттерн разработки: Временные Таблицы (ВТ) и Левое соединение по условию ЕСТЬ NULL.
Разберем этот алгоритм по шагам:
ЛЕВОГО СОЕДИНЕНИЯ. Те контрагенты, которые не найдутся во второй таблице (для них значение связи будет NULL), и есть наши новые клиенты.Проанализируем текст оптимизированного запроса:
// Шаг 1: Получаем продажи за интересующий нас период
ВЫБРАТЬ
ПродажиОбороты.Контрагент КАК Контрагент,
ПродажиОбороты.КоличествоОборот КАК Количество,
ПродажиОбороты.СтоимостьОборот КАК Стоимость
ПОМЕСТИТЬ втПродажиПериода
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ПродажиОбороты
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Получаем список контрагентов, у которых были продажи ДО начала периода
ВЫБРАТЬ РАЗЛИЧНЫЕ
ПродажиДо.Контрагент КАК Контрагент
ПОМЕСТИТЬ втБылиПродажиРанее
ИЗ
РегистрНакопления.Продажи.Обороты(, &НачалоПериода, , ) КАК ПродажиДо
ИНДЕКСИРОВАТЬ ПО
Контрагент
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Соединяем таблицы и отсекаем старых клиентов
ВЫБРАТЬ
втПродажиПериода.Контрагент КАК Контрагент,
втПродажиПериода.Количество КАК Количество,
втПродажиПериода.Стоимость КАК Стоимость
ИЗ
втПродажиПериода КАК втПродажиПериода
ЛЕВОЕ СОЕДИНЕНИЕ втБылиПродажиРанее КАК втБылиПродажиРанее
ПО втПродажиПериода.Контрагент = втБылиПродажиРанее.Контрагент
ГДЕ
втБылиПродажиРанее.Контрагент ЕСТЬ NULL
Преимущества данного решения: Использование временных таблиц позволяет СУБД построить четкий план запроса. Индексация временной таблицы втБылиПродажиРанее по полю Контрагент гарантирует моментальное левое соединение (обычно методом Merge Join или Hash Match). Операция фильтрации по ЕСТЬ NULL отрабатывает за доли секунды, исключая из результирующей выборки всех «старых» клиентов.
Если вам необходимо построить гибкий отчет, где можно анализировать появление новых клиентов в динамике по периодам (например, помесячно), можно использовать механизм группировок по периодам. Рассмотрим пример запроса, который анализирует продажи за весь период, а затем отсекает тех, у кого дата первой продажи меньше даты начала исследуемого периода:
ВЫБРАТЬ
ПродажиОбороты.Период КАК Период,
ПродажиОбороты.Контрагент КАК Контрагент,
ПродажиОбороты.СтоимостьОборот КАК СтоимостьОборот
ПОМЕСТИТЬ втВсеПродажи
ИЗ
РегистрНакопления.Продажи.Обороты(&МинимальнаяДатаВБазе, &КонецПериода, День, ) КАК ПродажиОбороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
втВсеПродажи.Период КАК ПериодПродажи,
втВсеПродажи.Контрагент КАК Контрагент,
втВсеПродажи.СтоимостьОборот КАК Стоимость
ИЗ
втВсеПродажи КАК втВсеПродажи
ЛЕВОЕ СОЕДИНЕНИЕ втВсеПродажи КАК втВсеПродажиПрошлые
ПО втВсеПродажи.Контрагент = втВсеПродажиПрошлые.Контрагент
И втВсеПродажиПрошлые.Период < втВсеПродажи.Период
ГДЕ
втВсеПродажиПрошлые.Контрагент ЕСТЬ NULL
И втВсеПродажи.Период МЕЖДУ &НачалоПериодаОтчета И &КонецПериодаОтчета
В данном запросе мы соединяем таблицу продаж саму с собой. Условие связи втВсеПродажиПрошлые.Период < втВсеПродажи.Период позволяет нам найти для каждой продажи более раннюю продажу этого же клиента. Если такой продажи не нашлось (значение ЕСТЬ NULL), значит, продажа в текущем периоде является самой первой в истории этого клиента.
Постоянное сканирование регистра накопления Продажи с начала времен для определения «новизны» клиента — это ресурсоемкая операция, которая со временем (при росте базы данных) будет работать все медленнее. Для системного решения этой задачи правильным архитектурным шагом будет добавление специального реквизита непосредственно в справочник.
Выполним следующие шаги для доработки системы:
Контрагенты новый реквизит ДатаРегистрации с типом данных Дата (состав даты — Дата и время).ПередЗаписью или ПриЗаписи для справочника Контрагенты.Пример кода обработчика подписки на событие:
Процедура ЗаполнитьДатуРегистрацииКонтрагентаПередЗаписью(Источник, Отказ, РежимЗаписи, РежимПроведения) Экспорт
// Проверяем, является ли элемент новым
Если Источник.ЭтоНовый() Тогда
// Заполняем дату регистрации текущим временем сервера
Источник.ДатаРегистрации = ТекущаяДатаСеанса();
КонецЕсли;
КонецПроцедуры
После внедрения этого механизма задача построения отчета по новым клиентам упростится в разы. Запрос для отчета будет выглядеть максимально просто и выполняться мгновенно, поскольку нам больше не нужно анализировать исторические данные продаж — достаточно наложить фильтр по дате регистрации контрагента:
ВЫБРАТЬ
ПродажиОбороты.Контрагент КАК Контрагент,
ПродажиОбороты.Контрагент.ДатаРегистрации КАК ДатаРегистрации,
ПродажиОбороты.СтоимостьОборот КАК СтоимостьОборот
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ПродажиОбороты
ГДЕ
ПродажиОбороты.Контрагент.ДатаРегистрации МЕЖДУ &НачалоПериода И &КонецПериода
Что делать, если руководство требует отчет прямо сейчас, исторические продажи анализировать тяжело (например, база велась некорректно или данные прошлых лет были свернуты), а реквизит ДатаРегистрации ранее не велся?
Выясним причину, по которой мы можем решить эту задачу без изменения метаданных. В платформе 1С:Предприятие 8 уникальные идентификаторы ссылочных типов (GUID) по умолчанию генерируются по стандарту RFC 4122 (тип 1, базирующийся на времени). Это означает, что внутри уникального идентификатора любого объекта базы данных зашифровано точное время его создания.
Мы можем написать вспомогательную функцию, которая будет извлекать дату создания контрагента непосредственно из его ссылки. Такую функцию можно вызывать в вычисляемых полях Системы Компоновки Данных (СКД) при построении отчета.
Рассмотрим пример реализации такой функции в общем модуле:
Функция ПолучитьДатуСозданияИзСсылки(Ссылка) Экспорт
Если Не ЗначениеЗаполнено(Ссылка) Тогда
Возврат Неопределено;
КонецЕсли;
УИДСтрока = Строка(Ссылка.УникальныйИдентификатор());
// GUID имеет формат: xxxxxxxx-xxxx-1xxx-xxxx-xxxxxxxxxxxx
// Проверим, относится ли GUID к типу 1 (содержит "1" в первом символе третьего блока)
Если Сред(УИДСтрока, 15, 1) <> "1" Тогда
// GUID сгенерирован случайным образом (тип 4) либо сторонней системой
Возврат Неопределено;
КонецЕсли;
// Извлекаем шестнадцатеричные части временного штампа
ВременнойШтампHex = Сред(УИДСтрока, 16, 3) + Сред(УИДСтрока, 10, 4) + Сред(УИДСтрока, 1, 8);
// Переводим шестнадцатеричное число в десятичное
База = 1;
ЧислоСекунд = 0;
Для Сч = 1 По СтрДлина(ВременнойШтампHex) Цикл
Символ = Сред(ВременнойШтампHex, СтрДлина(ВременнойШтампHex) - Сч + 1, 1);
Позиция = Найти("0123456789abcdef", Символ) - 1;
ЧислоСекунд = ЧислоСекунд + Позиция * База;
База = База * 16;
КонецЦикла;
// Переводим интервалы по 100 наносекунд (начиная с 15 октября 1582 г.) в обычные секунды
ЧислоСекунд = ЧислоСекунд / 10000000;
// Разница во времени между началом Григорианского календаря и началом эпохи UNIX (1 января 1970 г.)
РазницаЭпох = 12219292800;
ДатаUnix = ЧислоСекунд - РазницаЭпох;
// Возвращаем дату с учетом локального часового пояса (прибавляем разницу)
Возврат Дата("19700101000000") + ДатаUnix + (3 * 3600); // Пример для Москвы (+3)
КонецФункции
Используя данную функцию в качестве вычисляемого поля СКД, вы сможете мгновенно вывести дату создания любого контрагента в отчет и использовать её для группировки или фильтрации, не нагружая сервер тяжелыми запросами к истории продаж.
Мы проанализировали различные методы построения отчета по продажам новых клиентов в 1С. Для оперативного решения «здесь и сейчас» лучшим выбором является пакетный запрос с использованием временных таблиц и левого соединения по ЕСТЬ NULL. Для долгосрочной перспективы и обеспечения максимального быстродействия информационной системы рекомендуется внедрить автозаполнение реквизита даты регистрации контрагента при его создании.