Как получить 3 максимальных значения в разрезе групп в запросе 1С?

← На главную

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

Сложность этой задачи в 1С заключается в том, что язык запросов платформы не поддерживает классические оконные функции SQL, такие как ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...). В связи с этим разработчикам приходится прибегать к альтернативным механизмам: использованию пакетных запросов с временными таблицами, левым соединениям таблиц сами с собой, функционалу Системы компоновки данных (СКД) или программной постобработке результатов. В этой статье мы подробно разберем каждый из этих четырех подходов, проанализируем их эффективность и приведем готовые примеры кода.

Вариант 1. Классический пакетный запрос с эмуляцией нумерации строк

Рассмотрим решение, к которому пришли участники обсуждения на форуме. Этот метод основан на создании цепочки временных таблиц. Основная идея заключается в том, чтобы сначала рассчитать максимальные показатели (первое место), затем исключить их и рассчитать максимальные показатели среди оставшихся (второе место), и повторить процедуру для третьего места. В конце результаты объединяются через оператор ОБЪЕДИНИТЬ ВСЕ.

Давайте разберем по шагам структуру такого запроса на примере анализа регистра накопления Продажи.Обороты:


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

// Шаг 2. Находим абсолютный максимум (1-е место) для каждой группы
ВЫБРАТЬ
    втИсходныеДанные.НоменклатурнаяГруппа КАК НоменклатурнаяГруппа,
    МАКСИМУМ(втИсходныеДанные.Количество) КАК Количество
ПОМЕСТИТЬ втПервоеМесто
ИЗ
    втИсходныеДанные КАК втИсходныеДанные
СГРУППИРОВАТЬ ПО
    втИсходныеДанные.НоменклатурнаяГруппа
;

// Шаг 3. Находим второй максимум (2-е место), исключая показатели первого места
ВЫБРАТЬ
    втИсходныеДанные.НоменклатурнаяГруппа КАК НоменклатурнаяГруппа,
    МАКСИМУМ(втИсходныеДанные.Количество) КАК Количество
ПОМЕСТИТЬ втВтороеМесто
ИЗ
    втИсходныеДанные КАК втИсходныеДанные
ГДЕ
    НЕ (втИсходныеДанные.НоменклатурнаяГруппа, втИсходныеДанные.Количество) В
        (ВЫБРАТЬ
            втПервоеМесто.НоменклатурнаяГруппа,
            втПервоеМесто.Количество
        ИЗ
            втПервоеМесто КАК втПервоеМесто)
СГРУППИРОВАТЬ ПО
    втИсходныеДанные.НоменклатурнаяГруппа
;

// Шаг 4. Находим третий максимум (3-е место), исключая первое и второе места
ВЫБРАТЬ
    втИсходныеДанные.НоменклатурнаяГруппа КАК НоменклатурнаяГруппа,
    МАКСИМУМ(втИсходныеДанные.Количество) КАК Количество
ПОМЕСТИТЬ втТретьеМесто
ИЗ
    втИсходныеДанные КАК втИсходныеДанные
ГДЕ
    НЕ (втИсходныеДанные.НоменклатурнаяГруппа, втИсходныеДанные.Количество) В
        (ВЫБРАТЬ
            втПервоеМесто.НоменклатурнаяГруппа,
            втПервоеМесто.Количество
        ИЗ
            втПервоеМесто КАК втПервоеМесто
        
        ОБЪЕДИНИТЬ ВСЕ
        
        ВЫБРАТЬ
            втВтороеМесто.НоменклатурнаяГруппа,
            втВтороеМесто.Количество
        ИЗ
            втВтороеМесто КАК втВтороеМесто)
СГРУППИРОВАТЬ ПО
    втИсходныеДанные.НоменклатурнаяГруппа
;

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

Преимущества метода: Высокая наглядность логики расчета для фиксированного количества записей (в данном случае ровно три). Запрос легко отлаживается в консоли запросов по шагам.

Недостатки метода: Если потребуется изменить выборку с ТОП-3 до ТОП-10 или ТОП-20, текст запроса разрастется до гигантских размеров, так как для каждого нового шага придется плодить новые временные таблицы и условия вложенного исключения. Кроме того, создание множества временных таблиц создает повышенную нагрузку на базу данных tempdb СУБД.

Вариант 2. Оптимизированный запрос с использованием Self-Join (левое соединение «само на себя»)

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

Идея метода заключается в следующем: для каждой строки продаж мы находим все остальные строки из этой же группы, у которых объем продаж строго больше. Сгруппировав результат по аналитике исходной строки, с помощью агрегатной функции КОЛИЧЕСТВО() мы узнаем, сколько конкурентов опережают текущую позицию. Если таких конкурентов меньше 3 (то есть 0, 1 или 2), значит, текущая позиция входит в ТОП-3.

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


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

// Шаг 2. Соединяем таблицу саму с собой и фильтруем в секции ИМЕЮЩИЕ
ВЫБРАТЬ
    Т1.НоменклатурнаяГруппа КАК НоменклатурнаяГруппа,
    Т1.Номенклатура КАК Номенклатура,
    Т1.Количество КАК Количество
ИЗ
    втДанныеПродаж КАК Т1
    ЛЕВОЕ СОЕДИНЕНИЕ втДанныеПродаж КАК Т2
        ПО Т1.НоменклатурнаяГруппа = Т2.НоменклатурнаяГруппа
        И Т1.Количество < Т2.Количество
СГРУППИРОВАТЬ ПО
    Т1.НоменклатурнаяГруппа,
    Т1.Номенклатура,
    Т1.Количество
ИМЕЮЩИЕ
    КОЛИЧЕСТВО(Т2.Номенклатура) < 3
УПОРЯДОЧИТЬ ПО
    НоменклатурнаяГруппа,
    Количество УБЫВ

Важная деталь обработки дублей (Ties): Если несколько позиций имеют абсолютно одинаковое количество продаж (например, у нас есть пять позиций с продажами по 10 штук, и это максимальный показатель), простое условие сравнения Т1.Количество < Т2.Количество вернет для всех этих пяти строк значение конкурентов равное 0. В итоге в финальный отчет попадут все пять записей.

Если условиями задачи строго регламентировано выводить не более 3 записей в группе при любых обстоятельствах, необходимо добавить в условие соединения уникальное поле (например, ссылку на номенклатуру или её код) для однозначного ранжирования одинаковых значений:


ПО Т1.НоменклатурнаяГруппа = Т2.НоменклатурнаяГруппа
    И (Т1.Количество < Т2.Количество 
       ИЛИ (Т1.Количество = Т2.Количество И Т1.Номенклатура.Код < Т2.Номенклатура.Код))

Вариант 3. Использование штатных механизмов СКД (без программирования)

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

Для реализации этого метода выполните следующие шаги в конструкторе схемы компоновки данных:

На вкладке «Набор данных» напишите самый простой запрос без ограничений по количеству, который выберет все продажи в разрезе номенклатурных групп и номенклатуры.
Перейдите на вкладку «Настройки». Создайте структуру отчета: добавьте группировку по полю НоменклатурнаяГруппа, а внутрь нее добавьте подчиненную группировку по полю Номенклатура.
Выделите подчиненную группировку Номенклатура в дереве настроек структуры.
В нижней части экрана перейдите на вкладку «Другие настройки», которая относится именно к этой выделенной группировке.
Найдите параметр «Количество записей», установите для него флаг использования и укажите значение 3.
Перейдите на вкладку «Сортировка» для этой же подчиненной группировки и укажите поле КоличествоОборот с направлением сортировки Убыв.

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

Вариант 4. Программная постобработка выборки в Таблице Значений

Бывают ситуации, когда база данных содержит миллионы записей, а номенклатурные группы очень обширны. В таких условиях сложные запросы с вложенными соединениями (как в Варианте 2) могут выполняться крайне медленно и блокировать работу СУБД. В целях оптимизации производительности гораздо эффективнее получить плоскую выборку простым быстрым запросом, а фильтрацию ТОП-3 выполнить на стороне сервера приложений 1С с помощью объекта ТаблицаЗначений.

Рассмотрим по шагам алгоритм программного ранжирования:


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

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

// Шаг 2. Сортируем таблицу по группе (возрастание) и по количеству (убывание)
ТаблицаПродаж.Сортировать("НоменклатурнаяГруппа Возр, Количество Убыв");

// Шаг 3. Создаем пустую таблицу-приемник для хранения результатов
ТаблицаРезультат = ТаблицаПродаж.СкопироватьКолонки();

ТекущаяГруппа = Неопределено;
СчетчикВГруппе = 0;

// Шаг 4. Обходим строки и переносим только первые 3 записи для каждой группы
Для Каждого Строка Из ТаблицаПродаж Цикл
    
    // Если группа изменилась, сбрасываем счетчик элементов
    Если Строка.НоменклатурнаяГруппа <> ТекущаяГруппа Тогда
        ТекущаяГруппа = Строка.НоменклатурнаяГруппа;
        СчетчикВГруппе = 0;
    КонецЕсли;
    
    СчетчикВГруппе = СчетчикВГруппе + 1;
    
    // Переносим запись, если она входит в ТОП-3
    Если СчетчикВГруппе <= 3 Тогда
        ЗаполнитьЗначенияСвойств(ТаблицаРезультат.Добавить(), Строка);
    КонецЕсли;
    
КонецЦикла;

// В переменной ТаблицаРезультат теперь лежат очищенные данные ТОП-3

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