Как выбрать в запросе 1С контрагентов, которые формируют 85% выручки?

← На главную

В процессе бизнес-анализа продаж часто возникает задача классификации клиентов. Классическим примером является проведение ABC-анализа, при котором нам необходимо выделить группу клиентов (обычно классы «А» и «В»), обеспечивающую основную долю выручки — например, 85%. Сложность заключается в том, что стандартный язык запросов 1С не поддерживает классические оконные функции SQL (такие как SUM() OVER()), позволяющие легко рассчитать нарастающий итог (кумулятивную сумму).

В этой статье мы подробно рассмотрим несколько практических способов решения данной задачи: от классического и ресурсоемкого самосоединения в запросе до элегантных решений средствами компоновки данных (СКД) и постобработки в коде.

Способ 1. Расчет нарастающего итога в запросе с помощью самосоединения

Если перед нами стоит жесткое требование решить задачу исключительно в рамках одного запроса (без использования кода и СКД), единственным рабочим механизмом в 1С является левое или внутреннее соединение таблицы с самой собой.

Давайте разберем этот алгоритм по шагам:

Сначала мы формируем временную таблицу (например, ВТ_Продажи), в которой группируем продажи по контрагентам и рассчитываем сумму выручки.
Затем мы соединяем эту таблицу саму с собой. Условие соединения должно быть составлено так, чтобы для каждой строки «левой» таблицы подобрались все строки из «правой» таблицы, у которых сумма продаж больше. Если же суммы продаж равны, то соединение происходит по уникальному идентификатору (например, по ссылке на контрагента), чтобы избежать дублирования или потери записей с одинаковыми продажами.
Группируя результат по контрагенту левой таблицы и суммируя показатели правой, мы получаем нарастающий итог.

Посмотрим на пример практической реализации такого запроса:


ВЫБРАТЬ
    ПродажиОбороты.Контрагент КАК Контрагент,
    ПродажиОбороты.СуммаВыручкиОборот КАК Сумма
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
    РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ПродажиОбороты
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Т1.Контрагент КАК Контрагент,
    Т1.Сумма КАК Сумма,
    СУММА(Т2.Сумма) КАК НарастающийИтог
ПОМЕСТИТЬ ВТ_НарастающийИтог
ИЗ
    ВТ_Продажи КАК Т1
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Продажи КАК Т2
            ПО (ВЫБОР
                КОГДА Т1.Сумма = Т2.Сумма
                    ТОГДА Т1.Контрагент <= Т2.Контрагент
                ИНАЧЕ Т1.Сумма < Т2.Сумма
            КОНЕЦ)

ГРУППИРОВАТЬ ПО
    Т1.Контрагент,
    Т1.Сумма
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_НарастающийИтог.Контрагент КАК Контрагент,
    ВТ_НарастающийИтог.Сумма КАК Сумма,
    ВТ_НарастающийИтог.НарастающийИтог КАК НарастающийИтог
ИЗ
    ВТ_НарастающийИтог КАК ВТ_НарастающийИтог
ГДЕ
    ВТ_НарастающийИтог.НарастающийИтог <= (ВЫБРАТЬ СУММА(Сумма) * 0.85 ИЗ ВТ_Продажи)
УПОРЯДОЧИТЬ ПО
    Сумма УБЫВ

Важное предупреждение: Данный метод имеет квадратичную вычислительную сложность $O(N^2)$. Если в вашей исходной таблице 1000 контрагентов, СУБД выполнит до 1 000 000 операций сравнения строк, а результирующий набор данных разрастется до огромных масштабов в оперативной памяти. Для больших баз данных этот метод категорически не подходит, так как вызовет перегрузку сервера.

Способ 2. Программный обход в таблице значений (наиболее надежный метод)

Если задача выполняется в рамках внешней обработки, отчета или фонового задания, наиболее оптимальным с точки зрения производительности и потребления памяти будет выгрузка первоначального запроса в ТаблицуЗначений с последующей быстрой итерацией в цикле.

Выясним причину эффективности этого подхода: сортировка таблицы значений в оперативной памяти и линейный проход по ней занимают ничтожно мало времени (сложность $O(N \log N)$ на сортировку и $O(N)$ на обход). Проанализируем пример кода:


// Шаг 1. Получаем список продаж, отсортированный по убыванию выручки
Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |   ПродажиОбороты.Контрагент КАК Контрагент,
    |   ПродажиОбороты.СуммаВыручкиОборот КАК Сумма
    |ИЗ
    |   РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&НачалоПериода, &КонецПериода) КАК ПродажиОбороты
    |УПОРЯДОЧИТЬ ПО
    |   Сумма УБЫВ";

ТаблицаПродаж = Запрос.Выполнить().Выгрузить();

// Шаг 2. Рассчитываем порог в 85% от общей суммы
ОбщаяВыручка = ТаблицаПродаж.Итог("Сумма");
ПорогВыручки = ОбщаяВыручка * 0.85;

СписокТопКонтрагентов = Новый Массив;
ТекущаяСумма = 0;

// Шаг 3. В цикле собираем контрагентов до достижения порога
Для Каждого СтрокаПродаж Из ТаблицаПродаж Цикл
    ТекущаяСумма = ТекущаяСумма + СтрокаПродаж.Сумма;
    СписокТопКонтрагентов.Добавить(СтрокаПродаж.Контрагент);
    
    Если ТекущаяСумма >= ПорогВыручки Тогда
        Прервать; // Цель достигнута, останавливаем цикл
    КонецЕсли;
КонецЦикла;

// Шаг 4. Теперь массив "СписокТопКонтрагентов" можно передать в следующий запрос
// Например, для поиска потерянных клиентов в текущем году

Способ 3. Вычисление нарастающего итога в СКД

Система компоновки данных (СКД) предоставляет разработчику мощный функционал постобработки данных на стороне платформы без использования тяжелых запросов. Для расчета нарастающего итога мы можем использовать встроенную функцию СКД ВычислитьВыражение.

Разберем по шагам, как настроить расчет кумулятивной суммы в СКД:

В схеме компоновки данных создаем стандартный запрос, выводящий контрагента и сумму его выручки (без хитрых соединений).
Переходим на вкладку «Ресурсы».
Добавляем новое поле ресурса, например, СуммаНарастающимИтогом. В качестве выражения прописываем:
ВычислитьВыражение("Сумма(СуммаВыручки)", , "Группировка", "Первая", "Текущая")
Для вычисления процента от общего итога создаем еще один ресурс (например, ПроцентОтВыручки) со следующим выражением:
Сумма(СуммаВыручки) / ВычислитьВыражение("Сумма(СуммаВыручки)", , "ОбщийИтог") * 100
На уровне настроек структуры отчета, во вкладке «Отбор», устанавливаем фильтр: ПроцентОтВыручки <= 85.

Этот способ работает чрезвычайно быстро на уровне платформы в оперативной памяти и абсолютно безопасен для производительности СУБД.

Решение сопутствующей задачи: Поиск «потерянных» ключевых клиентов

Довольно часто, выделив группу контрагентов, формирующих 85% выручки за прошлый год, бизнес-аналитики хотят сопоставить их с текущим периодом и найти тех клиентов, которые перестали покупать у нас в текущем году (так называемый анализ оттока клиентов).

Имея на руках массив топ-контрагентов прошлого года (полученный, например, вторым способом через Таблицу Значений), мы можем легко решить эту задачу с помощью левого соединения в запросе:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |   ТопПрошлогоГода.Ссылка КАК Контрагент
    |ПОМЕСТИТЬ ВТ_ТопПрошлогоГода
    |ИЗ
    |   &МассивТопКонтрагентов КАК ТопПрошлогоГода // Передаем наш массив
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    |   ВТ_ТопПрошлогоГода.Контрагент КАК ПотерянныйКонтрагент,
    |   ЕСТЬNULL(ПродажиТекущегоГода.СуммаОборот, 0) КАК ПродажиТекущегоГода
    |ИЗ
    |   ВТ_ТопПрошлогоГода КАК ВТ_ТопПрошлогоГода
    |       ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&НачалоТекущегоГода, &КонецТекущегоГода) КАК ПродажиТекущегоГода
    |       ПО ВТ_ТопПрошлогоГода.Контрагент = ПродажиТекущегоГода.Контрагент
    |ГДЕ
    |   ПродажиТекущегоГода.Контрагент ИС ССЫЛКА // Выбираем только тех, у кого нет продаж в текущем периоде
    |   ИЛИ ЕСТЬNULL(ПродажиТекущегоГода.СуммаОборот, 0) = 0";

Запрос.УстановитьПараметр("МассивТопКонтрагентов", СписокТопКонтрагентов);
Запрос.УстановитьПараметр("НачалоТекущегоГода", НачалоТекущегоГода);
Запрос.УстановитьПараметр("КонецТекущегоГода", КонецТекущегоГода);

РезультатЗапроса = Запрос.Выполнить().Выгрузить();

Резюме и рекомендации

Для реализации ABC-анализа и расчета нарастающих итогов в системе 1С:

Используйте программную постобработку в Таблице Значений, если отчет формируется кодом в модуле. Это самый быстрый и наименее требовательный к ресурсам СУБД подход.
Используйте функцию ВычислитьВыражение в СКД, если строится интерактивный пользовательский отчет.
Применяйте самосоединение в запросах только на заведомо небольших объемах данных (до 200–300 строк), чтобы избежать существенного падения производительности информационной базы.