Как в запросе 1С найти покупателя, купившего товар на самую большую сумму?

← На главную

В процессе разработки аналитических отчетов, планировщиков закупок или систем лояльности часто возникает задача: определить для каждого товара ключевого клиента, который приобрел этот товар на максимальную сумму за определенный период. На первый взгляд задача кажется простой, но при попытке написать прямой запрос разработчики часто сталкиваются с трудностями группировки данных в СУБД.

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

В чем сложность задачи?

Если мы попытаемся написать простой запрос к регистру продаж с группировкой по полям Номенклатура и Покупатель, выбрав при этом МАКСИМУМ(Сумма), мы получим максимальную разовую покупку каждого клиента по каждому товару. Если же мы сначала сгруппируем продажи по товарам и покупателям, посчитав общую сумму по каждой паре, то при попытке выбрать максимальную сумму по товару СУБД потребует включить поле Покупатель в группировку. Как только мы добавляем покупателя в группировку, СУБД снова начинает рассчитывать итоги по каждой паре «Товар — Покупатель» отдельно, и мы теряем возможность определить, кто же именно является абсолютным лидером по конкретному товару.

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

---

Способ 1. Пакетный запрос с временной таблицей (Классическое решение)

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

Шаг 1. Сбор детальной информации во временную таблицу

Сначала мы обращаемся к виртуальной таблице оборотов регистра продаж. Нам необходимо получить общие суммы и количества продаж по каждой паре контрагента и номенклатуры за интересующий нас период. Результат мы помещаем во временную таблицу Продажи и обязательно индексируем ее по полю Номенклатура для ускорения последующих соединений.

Шаг 2. Нахождение максимальной суммы по каждому товару

Вторым запросом пакета мы выбираем из нашей временной таблицы Продажи все товары и вычисляем для каждого из них максимальную сумму с помощью агрегатной функции МАКСИМУМ. На этом этапе поле покупателя мы не выбираем, группировка идет исключительно по полю Номенклатура.

Шаг 3. Внутреннее соединение для получения покупателя-лидера

На финальном этапе мы соединяем детальную таблицу Продажи с полученной выборкой максимальных сумм. Соединение выполняется по двум условиям: равенство номенклатуры и равенство суммы продаж. Таким образом, в итоговую выборку попадут только те строки из детальной таблицы, сумма которых совпала с максимальной суммой по данному товару.

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


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

СГРУППИРОВАТЬ ПО
    ПродажиОбороты.Номенклатура,
    ПродажиОбороты.Покупатель

ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Продажи.Номенклатура КАК Номенклатура,
    Продажи.Покупатель КАК Покупатель,
    Продажи.Количество КАК Количество,
    Продажи.Сумма КАК Сумма
ИЗ
    Продажи КАК Продажи
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
            ПродажиМакс.Номенклатура КАК Номенклатура,
            МАКСИМУМ(ПродажиМакс.Сумма) КАК МаксСумма
        ИЗ
            Продажи КАК ПродажиМакс
        
        СГРУППИРОВАТЬ ПО
            ПродажиМакс.Номенклатура) КАК МаксимальныеПродажи
        ПО Продажи.Номенклатура = МаксимальныеПродажи.Номенклатура
            И Продажи.Сумма = МаксимальныеПродажи.МаксСумма

---

Важный нюанс: Борьба с дублированием строк

Рассматривая предложенное решение, необходимо обратить внимание на важную деталь. Что произойдет, если два или более покупателей купят один и тот же товар на абсолютно одинаковую максимальную сумму?

В этом случае внутреннее соединение сработает для обоих покупателей, и в итоговом результате по одной и той же номенклатуре появятся две (или более) строки. Если бизнес-логика отчета или алгоритма требует, чтобы для каждого товара выводился ровно один покупатель, нам придется усложнить наш запрос и применить дополнительное сворачивание данных.

Чтобы гарантировать уникальность номенклатуры в результате, мы можем использовать агрегатную функцию МИНИМУМ или МАКСИМУМ для поля Покупатель (выберет первого или последнего по алфавиту/уникальному идентификатору ссылки) либо для поля Количество. Проанализируем, как это реализовать с помощью цепочки временных таблиц:


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

СГРУППИРОВАТЬ ПО
    ПродажиОбороты.Номенклатура,
    ПродажиОбороты.Покупатель

ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_ДетальныеПродажи.Номенклатура КАК Номенклатура,
    МАКСИМУМ(ВТ_ДетальныеПродажи.Сумма) КАК МаксСумма
ПОМЕСТИТЬ ВТ_Максимумы
ИЗ
    ВТ_ДетальныеПродажи КАК ВТ_ДетальныеПродажи

СГРУППИРОВАТЬ ПО
    ВТ_ДетальныеПродажи.Номенклатура

ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
// Выбираем только одного покупателя (первого по алфавиту ссылки), если суммы совпали
ВЫБРАТЬ
    ВТ_ДетальныеПродажи.Номенклатура КАК Номенклатура,
    ВТ_ДетальныеПродажи.Сумма КАК Сумма,
    МИНИМУМ(ВТ_ДетальныеПродажи.Покупатель) КАК Покупатель
ПОМЕСТИТЬ ВТ_УникальныеЛидеры
ИЗ
    ВТ_ДетальныеПродажи КАК ВТ_ДетальныеПродажи
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Максимумы КАК ВТ_Максимумы
        ПО ВТ_ДетальныеПродажи.Номенклатура = ВТ_Максимумы.Номенклатура
            И ВТ_ДетальныеПродажи.Сумма = ВТ_Максимумы.МаксСумма

СГРУППИРОВАТЬ ПО
    ВТ_ДетальныеПродажи.Номенклатура,
    ВТ_ДетальныеПродажи.Сумма

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    Покупатель
;

////////////////////////////////////////////////////////////////////////////////
// Соединяем обратно с продажами, чтобы получить точное количество по выбранному лидеру
ВЫБРАТЬ
    ВТ_УникальныеЛидеры.Номенклатура КАК Номенклатура,
    ВТ_УникальныеЛидеры.Покупатель КАК Покупатель,
    ВТ_ДетальныеПродажи.Количество КАК Количество,
    ВТ_УникальныеЛидеры.Сумма КАК Сумма
ИЗ
    ВТ_УникальныеЛидеры КАК ВТ_УникальныеЛидеры
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ДетальныеПродажи КАК ВТ_ДетальныеПродажи
        ПО ВТ_УникальныеЛидеры.Номенклатура = ВТ_ДетальныеПродажи.Номенклатура
            И ВТ_УникальныеЛидеры.Покупатель = ВТ_ДетальныеПродажи.Покупатель

---

Способ 2. Альтернативный вариант через многомерный оператор «В»

В платформе 1С поддерживается интересная и лаконичная конструкция — сравнение кортежей (многомерный оператор В в секции ГДЕ). Это позволяет избежать явного написания внутреннего соединения во втором запросе пакета, делая текст запроса визуально более чистым.

Давайте посмотрим на пример реализации такого подхода:


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

СГРУППИРОВАТЬ ПО
    ПродажиОбороты.Номенклатура,
    ПродажиОбороты.Покупатель

ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_Продажи.Номенклатура КАК Номенклатура,
    ВТ_Продажи.Покупатель КАК Покупатель,
    ВТ_Продажи.Количество КАК Количество,
    ВТ_Продажи.Сумма КАК Сумма
ИЗ
    ВТ_Продажи КАК ВТ_Продажи
ГДЕ
    (ВТ_Продажи.Номенклатура, ВТ_Продажи.Сумма) В
        (ВЫБРАТЬ
            ВТ_ПродажиМакс.Номенклатура,
            МАКСИМУМ(ВТ_ПродажиМакс.Сумма)
        ИЗ
            ВТ_Продажи КАК ВТ_ПродажиМакс
        СГРУППИРОВАТЬ ПО
            ВТ_ПродажиМакс.Номенклатура)

СУБД транслирует конструкцию с многомерным оператором В в эффективный план выполнения (обычно через полусоединение Semi-Join). Этот способ отлично подходит для простых выборок, где гарантированно нет совпадений по суммам или бизнес-логика допускает вывод нескольких покупателей-лидеров при равенстве показателей.

---

Правила оптимизации производительности

Когда вы работаете с крупными информационными базами, в которых таблицы продаж содержат миллионы записей, неоптимальный запрос может привести к перегрузке процессора сервера СУБД и длительным блокировкам таблиц. Чтобы избежать этого, всегда придерживайтесь следующих правил:

Используйте параметры виртуальных таблиц. Никогда не накладывайте фильтры по периоду, организации или номенклатуре в секции ГДЕ итогового запроса. Всегда передавайте их непосредственно в параметры виртуальной таблицы: Продажи.Обороты(&НачалоПериода, &КонецПериода, , Номенклатура В (&СписокНоменклатуры)). Это позволит платформе использовать встроенные индексы итогов регистра и СУБД не будет читать лишние миллионы записей.
Индексируйте временные таблицы. Временные таблицы, которые вы планируете соединять с другими таблицами или подзапросами, обязательно должны иметь индексы по полям связи. В нашем случае это инструкция ИНДЕКСИРОВАТЬ ПО Номенклатура сразу после помещения данных во временную таблицу. Без этого СУБД (особенно PostgreSQL) будет вынуждена выполнять полное сканирование таблицы (Sequential Scan) при каждом соединении.
Ограничивайте выборку разумными рамками. Старайтесь не выполнять подобные расчеты за всю историю существования предприятия. Всегда ограничивайте период (например, текущий год или прошлый квартал).

---

Способ 3. Решение задачи средствами СКД (без сложного SQL-кода)

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

Выясним, как настроить получение «лидера продаж» в СКД по шагам:

Создайте новый набор данных - запрос. Напишите максимально простой запрос, выбирающий продажи в разрезе товаров и покупателей:


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

Перейдите на вкладку «Ресурсы» и сделайте поля Сумма и Количество ресурсами (с функцией СУММА).
Перейдите на вкладку «Настройки». Создайте структуру отчета следующим образом:
- Добавьте группировку по полю Номенклатура.
- Внутрь группировки Номенклатура добавьте подчиненную группировку по полю Покупатель.
Выберите подчиненную группировку Покупатель и настройте для нее индивидуальные свойства на вкладке «Другие настройки» (внизу окна настроек СКД):
- Найдите параметр «Выбирать первые» и установите для него значение 1.
Перейдите на вкладку «Сортировка» для подчиненной группировки Покупатель и добавьте сортировку по ресурсу Сумма с направлением «По убыванию».

При формировании такого отчета СКД автоматически сгруппирует все продажи по товарам, внутри каждого товара отсортирует покупателей по убыванию их сумм покупок и выведет на экран ровно одну первую строку — то есть покупателя, купившего товар на наибольшую сумму. Это элегантное, декларативное решение, которое работает быстро и не требует написания сложного программного кода.