Как рассчитать рейтинг в запросе и выбрать 25% клиентов с крупным оборотом

Программист 1С v8.3 (Управляемые формы) 1С:Управление нашей фирмой Управленческий учет Торговля и дистрибуция
← На главную

В практике разработки на платформе 1С часто возникает задача сегментации данных непосредственно в запросе. Одной из классических задач является выборка определенного процента лидеров (например, ТОП-25% клиентов по обороту) в разрезе каждой торговой точки или магазина. Сложность заключается в том, что стандартный оператор ПЕРВЫЕ работает для всей выборки целиком, а не для каждой группы (магазина) отдельно.

В этой статье мы подробно разберем, как решить эту задачу различными способами: от классического метода с использованием временных таблиц до современных возможностей платформы и системы компоновки данных.

Способ 1. Использование временных таблиц и функции АВТОНОМЕРЗАПИСИ (универсальный метод)

Этот подход является наиболее распространенным для версий платформы ниже 8.3.24. Основная идея заключается в том, чтобы пронумеровать записи внутри каждой группы и затем отсечь те, чей номер превышает рассчитанный порог. Рассмотрим этот процесс по шагам.

Шаг 1. Сбор первичных данных. Сначала нам необходимо получить общие суммы оборотов по каждому контрагенту в разрезе магазинов и поместить их во временную таблицу. Это позволит нам работать с агрегированными данными.


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

Шаг 2. Нумерация записей. Теперь мы создаем вторую временную таблицу, в которую переносим данные из первой, упорядочив их по магазину и сумме оборота (убывание). Здесь мы применяем функцию АВТОНОМЕРЗАПИСИ. Чтобы сортировка сработала корректно, нам приходится использовать конструкцию ВЫБРАТЬ ПЕРВЫЕ 1000000000, так как без нее УПОРЯДОЧИТЬ ПО внутри запроса, создающего временную таблицу, может быть проигнорировано оптимизатором СУБД.


ВЫБРАТЬ ПЕРВЫЕ 1000000000
    ВТ_Обороты.Магазин КАК Магазин,
    ВТ_Обороты.Контрагент КАК Контрагент,
    ВТ_Обороты.Сумма КАК Сумма,
    АВТОНОМЕРЗАПИСИ() КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Нумерованная
ИЗ
    ВТ_Обороты КАК ВТ_Обороты
УПОРЯДОЧИТЬ ПО
    ВТ_Обороты.Магазин,
    ВТ_Обороты.Сумма УБЫВ;

Шаг 3. Расчет границ для каждого магазина. Нам нужно знать минимальный и максимальный номер строки для каждого магазина, чтобы вычислить общее количество клиентов в нем и определить порог в 25%.


ВЫБРАТЬ
    ВТ_Нумерованная.Магазин КАК Магазин,
    МИНИМУМ(ВТ_Нумерованная.НомерСтроки) КАК НачальныйНомер,
    МАКСИМУМ(ВТ_Нумерованная.НомерСтроки) КАК КонечныйНомер,
    ВЫРАЗИТЬ((МАКСИМУМ(ВТ_Нумерованная.НомерСтроки) - МИНИМУМ(ВТ_Нумерованная.НомерСтроки) + 1) * 0.25 КАК ЧИСЛО(15, 0)) КАК ПорогКоличества
ПОМЕСТИТЬ ВТ_Границы
ИЗ
    ВТ_Нумерованная КАК ВТ_Нумерованная
СГРУППИРОВАТЬ ПО
    ВТ_Нумерованная.Магазин;

Шаг 4. Финальная выборка. Мы соединяем нумерованную таблицу с таблицей границ. Условие соединения позволяет нам оставить только те записи, чей НомерСтроки попадает в диапазон первых 25% для своего магазина.


ВЫБРАТЬ
    ВТ_Нумерованная.Магазин,
    ВТ_Нумерованная.Контрагент,
    ВТ_Нумерованная.Сумма
ИЗ
    ВТ_Нумерованная КАК ВТ_Нумерованная
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Границы КАК ВТ_Границы
    ПО ВТ_Нумерованная.Магазин = ВТ_Границы.Магазин
ГДЕ
    ВТ_Нумерованная.НомерСтроки < ВТ_Границы.НачальныйНомер + ВТ_Границы.ПорогКоличества;

Способ 2. Использование оконных функций (для платформы 8.3.24 и выше)

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

Проанализируем ситуацию: нам нужно пронумеровать строки внутри каждого магазина и сравнить этот номер с общим количеством строк в этом же магазине. Разберем, как это сделать одним запросом:


ВЫБРАТЬ
    Продажи.Магазин,
    Продажи.Контрагент,
    СУММА(Продажи.СуммаОборота) КАК Сумма,
    // Номер строки внутри магазина по убыванию суммы
    ROW_NUMBER() OVER (PARTITION BY Продажи.Магазин ORDER BY СУММА(Продажи.СуммаОборота) УБЫВ) КАК Рейтинг,
    // Общее количество контрагентов в магазине
    COUNT(*) OVER (PARTITION BY Продажи.Магазин) КАК ВсегоВМагазине
ПОМЕСТИТЬ ВТ_Рейтинг
ИЗ
    РегистрНакопления.Продажи.Обороты КАК Продажи
СГРУППИРОВАТЬ ПО
    Продажи.Магазин,
    Продажи.Контрагент;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_Рейтинг.Магазин,
    ВТ_Рейтинг.Контрагент,
    ВТ_Рейтинг.Сумма
ИЗ
    ВТ_Рейтинг КАК ВТ_Рейтинг
ГДЕ
    ВТ_Рейтинг.Рейтинг <= ВТ_Рейтинг.ВсегоВМагазине * 0.25;

Этот метод не только короче, но и значительно производительнее, так как СУБД выполняет эти операции оптимизировано на уровне ядра, не создавая лишних индексов и промежуточных данных в tempdb.

Способ 3. Реализация через Систему компоновки данных (СКД)

Иногда выгоднее не усложнять сам запрос, а воспользоваться встроенными механизмами СКД для постобработки результата. Рассмотрим этот вариант подробнее.

  1. Создание вычисляемого поля: В СКД можно создать вычисляемое поле с выражением ПроцентВГруппе(СуммаОборота). Однако это покажет долю суммы, а не долю количества клиентов.
  2. Использование системных полей: В настройках группировки "Магазин" добавьте группировку по "Контрагенту". В выбранных полях используйте ПорядковыйНомерВГруппе.
  3. Настройка отбора: Самый эффективный способ в СКД — это использование ресурсов и функций. Вы можете рассчитать общее количество записей в группе через Количество(ВычислитьВыражениеСГруппировкойМассив("Контрагент")) и использовать это значение для фильтрации.

Тем не менее, для жесткого ограничения выборки (чтобы лишние 75% данных даже не передавались с сервера на клиент) лучше использовать решение непосредственно в языке запросов, описанное в первом или втором способе.

Важные нюансы и рекомендации

При реализации рейтинга следует учитывать несколько критических моментов, которые могут повлиять на корректность результата:

Проблема одинаковых значений: Выясним причину, по которой рейтинг может быть нестабильным. Если у нескольких клиентов одинаковая сумма оборота, их порядковый номер при использовании АВТОНОМЕРЗАПИСИ может меняться от запуска к запуску. Чтобы этого избежать, всегда добавляйте в УПОРЯДОЧИТЬ ПО дополнительное поле с уникальными данными, например, Контрагент.Код или Контрагент.Ссылка.

Производительность: При использовании первого способа (временные таблицы) обязательно индексируйте поля, по которым происходит соединение. В нашем примере это поле Магазин во временных таблицах ВТ_Нумерованная и ВТ_Границы. Без индексов на больших объемах данных запрос может выполняться недопустимо долго.

Точность округления: При расчете порога 25% мы использовали ВЫРАЗИТЬ(... КАК ЧИСЛО(15, 0)). Помните, что при малом количестве клиентов в магазине (например, 3 клиента) 25% может округлиться до 1 или 0. Заранее определитесь с бизнес-логикой: нужно ли выводить хотя бы одного клиента, если общее их число мало.

Рассмотренные методы позволяют гибко управлять выборкой данных, обеспечивая высокую точность анализа без использования циклов в программном коде, что соответствует стандартам разработки "1С:Совместимо".

← На главную