В анализе торговой деятельности часто возникает классическая задача класса «Gaps and Islands» (поиск островов и промежутков). Бизнесу важно выявлять клиентов, которые сначала проявляли активность (покупали товары), затем на долгое время «пропадали» (например, более чем на 13 месяцев), а после этого снова возвращались к покупкам и совершали новые заказы.
В этой статье мы подробно разберем, как реализовать такую выборку в системе 1С:Предприятие. Мы проанализируем базовый способ через самосоединение таблиц, обсудим проблемы его производительности на больших базах данных, рассмотрим эмуляцию оконных функций и альтернативный быстрый метод на встроенном языке 1С.
Самый очевидный способ решить задачу в рамках языка запросов 1С — это сопоставить каждую продажу контрагента со следующей по порядку его продажей и измерить разницу во времени между ними. Давайте разберем этот процесс по шагам.
Шаг 1. Подготовка уникальных периодов продаж
Для начала нам необходимо избавиться от лишней детализации. Если клиент совершал несколько покупок в течение одного месяца, для нашей задачи это считается единым периодом активности. Мы свернем данные до месяца с помощью функции НАЧАЛОПЕРИОДА и запишем их во временную таблицу ВТ_Продажи.
ВЫБРАТЬ
ПродажиОбороты.Контрагент КАК Контрагент,
НАЧАЛОПЕРИОДА(ПродажиОбороты.Период, МЕСЯЦ) КАК Период
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК ПродажиОбороты
СГРУППИРОВАТЬ ПО
ПродажиОбороты.Контрагент,
НАЧАЛОПЕРИОДА(ПродажиОбороты.Период, МЕСЯЦ)
ИНДЕКСИРОВАТЬ ПО
Контрагент,
Период
;
Индексирование по полям Контрагент и Период критически важно, так как далее мы будем выполнять тяжелую операцию соединения таблицы самой с собой.
Шаг 2. Поиск «следующей» даты продажи и расчет интервала
Теперь мы соединяем таблицу ВТ_Продажи (назовем ее Т1) с ней же самой (назовем Т2). Условие связи должно находить все будущие продажи этого же контрагента:
Т1.Контрагент = Т2.КонтрагентТ1.Период < Т2.ПериодСгруппировав результат по исходной дате Т1.Период, мы берем минимальную дату из будущих продаж МИНИМУМ(Т2.Период). Это и будет дата ближайшей следующей покупки. После этого нам остается применить функцию РАЗНОСТЬДАТ с фильтром более 13 месяцев.
ВЫБРАТЬ
Т1.Контрагент КАК Контрагент,
Т1.Период КАК ДатаПоследнейПродажиПередУходом,
МИНИМУМ(Т2.Period) КАК ДатаВозвращенияКлиента,
РАЗНОСТЬДАТ(Т1.Период, МИНИМУМ(Т2.Период), МЕСЯЦ) КАК ПериодБездействияМесяцев
ИЗ
ВТ_Продажи КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Продажи КАК Т2
ПО Т1.Контрагент = Т2.Контрагент
И Т1.Период < Т2.Период
СГРУППИРОВАТЬ ПО
Т1.Контрагент,
Т1.Период
ИМЕЮЩИЕ
РАЗНОСТЬДАТ(Т1.Период, МИНИМУМ(Т2.Период), МЕСЯЦ) > 13
;
В чем проблема этого метода: Данное решение прекрасно работает на небольших объемах данных. Однако оно имеет квадратичную сложность O(N²). Если у одного клиента за всю историю накопились сотни записей продаж, СУБД будет вынуждена перебирать гигантское количество комбинаций «меньше-больше». На рабочей базе с сотнями тысяч документов этот запрос может выполняться часами или вызвать аварийное завершение по таймауту.
Чтобы избежать нелинейного соединения по условию < (меньше), мы можем пронумеровать продажи каждого контрагента по порядку: 1, 2, 3 и т. д. В классическом SQL для этого используются оконные функции ROW_NUMBER() или LAG/LEAD, но в языке запросов 1С их пока нет. Мы можем эмулировать нумерацию строк программно в запросе.
Если строки пронумерованы, то «следующая» строка для строки N — это строго строка N + 1. Соединение по равенству Т1.НомерСтроки = Т2.НомерСтроки - 1 отрабатывает в СУБД мгновенно, используя эффективные алгоритмы типа Hash Match или Merge Join.
Рассмотрим, как выглядит этот алгоритм в запросе:
// Шаг 1. Получаем продажи (аналогично первому способу)
ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент,
НАЧАЛОПЕРИОДА(Продажи.Период, МЕСЯЦ) КАК Период
ПОМЕСТИТЬ ВТ_ПродажиСвернуто
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Контрагент,
НАЧАЛОПЕРИОДА(Продажи.Период, МЕСЯЦ)
ИНДЕКСИРОВАТЬ ПО
Контрагент,
Период
;
// Шаг 2. Пронумеруем записи для каждого контрагента
ВЫБРАТЬ
Т1.Контрагент КАК Контрагент,
Т1.Период КАК Период,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Период) КАК НомерСтроки
ПОМЕСТИТЬ ВТ_ПронумерованныеПродажи
ИЗ
ВТ_ПродажиСвернуто КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ПродажиСвернуто КАК Т2
ПО Т1.Контрагент = Т2.Контрагент
И Т1.Период >= Т2.Период
СГРУППИРОВАТЬ ПО
Т1.Контрагент,
Т1.Период
ИНДЕКСИРОВАТЬ ПО
Контрагент,
НомерСтроки
;
// Шаг 3. Соединяем по равенству номеров строк (N и N+1)
ВЫБРАТЬ
Р1.Контрагент КАК Контрагент,
Р1.Период КАК ДатаПродажиДоЗатишья,
Р2.Период КАК ДатаНовойПродажи,
РАЗНОСТЬДАТ(Р1.Период, Р2.Период, МЕСЯЦ) КАК ПериодБездействия
ИЗ
ВТ_ПронумерованныеПродажи КАК Р1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ПронумерованныеПродажи КАК Р2
ПО Р1.Контрагент = Р2.Контрагент
И Р1.НомерСтроки = Р2.НомерСтроки - 1
ГДЕ
РАЗНОСТЬДАТ(Р1.Period, Р2.Period, МЕСЯЦ) > 13
;
Этот метод работает значительно быстрее классического на базах среднего размера, так как финальное сопоставление дат идет по точному совпадению индексов строк.
Очень часто разработчики пытаются переложить абсолютно всю логику вычислений на СУБД, создавая невероятно громоздкие и медленные запросы. Однако платформа 1С предоставляет мощный встроенный язык, который отлично справляется с последовательным (линейным) обходом коллекций.
Сложность линейного обхода составляет O(N), что в разы эффективнее любых соединений таблиц в SQL. Давайте снимем вычислительную нагрузку с сервера баз данных и выполним простой расчет на сервере приложений 1С.
Проанализируем по шагам, как это реализовать:
УПОРЯДОЧИТЬ ПО Контрагент, Период. Благодаря этому все продажи одного клиента гарантированно будут идти друг за другом в хронологическом порядке.Посмотрим на пример программного кода:
// Сначала готовим и выполняем простой запрос без соединений
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Продажи.Контрагент КАК Контрагент,
| НАЧАЛОПЕРИОДА(Продажи.Период, МЕСЯЦ) КАК Период
|ИЗ
| РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК Продажи
|СГРУППИРОВАТЬ ПО
| Продажи.Контрагент,
| НАЧАЛОПЕРИОДА(Продажи.Период, МЕСЯЦ)
|УПОРЯДОЧИТЬ ПО
| Контрагент,
| Период";
Запрос.УстановитьПараметр("НачалоПериода", НачалоПериода);
Запрос.УстановитьПараметр("КонецПериода", КонецПериода);
Выборка = Запрос.Выполнить().Выбрать();
// Сюда будем сохранять найденных контрагентов и периоды их бездействия
ТаблицаРезультатов = Новый ТаблицаЗначений;
ТаблицаРезультатов.Колонки.Добавить("Контрагент", Новый ОписаниеТипов("СправочникСсылка.Контрагенты"));
ТаблицаРезультатов.Колонки.Добавить("НачалоПропуска", Новый ОписаниеТипов("Дата"));
ТаблицаРезультатов.Колонки.Добавить("КонецПропуска", Новый ОписаниеТипов("Дата"));
ТаблицаРезультатов.Колонки.Добавить("МесяцевПростоя", Новый ОписаниеТипов("Число"));
// Переменные для хранения состояния предыдущей строки
ПредыдущийКонтрагент = Неопределено;
ПредыдущийПериод = '00010101';
Пока Выборка.Следующий() Цикл
// Если перешли к новому контрагенту - просто сбрасываем состояние и идем дальше
Если Выборка.Контрагент <> ПредыдущийКонтрагент Тогда
ПредыдущийКонтрагент = Выборка.Контрагент;
ПредыдущийПериод = Выборка.Период;
Продолжить;
КонецЕсли;
// Вычисляем разницу в месяцах между текущей и предыдущей покупкой одного контрагента
РазницаВМесяцах = Цело(РазностьДат(ПредыдущийПериод, Выборка.Период, "МЕСЯЦ")); // Вспомогательная функция
// Если разница превышает 13 месяцев (пропуск более чем в 13 месяцев)
Если РазницаВМесяцах > 13 Тогда
НоваяСтрока = ТаблицаРезультатов.Добавить();
НоваяСтрока.Контрагент = Выборка.Контрагент;
НоваяСтрока.НачалоПропуска = ПредыдущийПериод;
НоваяСтрока.КонецПропуска = Выборка.Период;
НоваяСтрока.МесяцевПростоя = РазницаВМесяцах;
КонецЕсли;
// Запоминаем текущую дату как предыдущую для следующего шага цикла
ПредыдущийПериод = Выборка.Период;
КонецЦикла;
Для вычисления разности дат на встроенном языке можно использовать простую универсальную функцию РазностьДат(), либо задействовать штатные механизмы платформы по расчету периодов (например, переводя даты в количество месяцев от начала нашей эры).
Давайте сопоставим все три подхода, чтобы вы могли принять правильное архитектурное решение для вашего проекта: