Как в запросе 1С выбрать строку с максимальной суммой для каждого документа

← На главную

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

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

Анализ проблемы: почему нельзя сделать всё в одной группировке?

Посмотрим на типичную ошибку начинающих разработчиков. Пытаясь решить задачу одним простым запросом, они пишут следующую конструкцию:


ВЫБРАТЬ
    ЧекККМТовары.Ссылка КАК Ссылка,
    ЧекККМТовары.Номенклатура КАК Номенклатура,
    МАКСИМУМ(ЧекККМТовары.Сумма) КАК Сумма
ИЗ
    Документ.ЧекККМ.Товары КАК ЧекККМТовары
СГРУППИРОВАТЬ ПО
    ЧекККМТовары.Ссылка,
    ЧекККМТовары.Номенклатура

Давайте проанализируем этот запрос. Что он вернет? Вместо того чтобы выдать одну номенклатуру с максимальной суммой по всему чеку, СУБД сгруппирует данные по уникальным парам «Документ — Номенклатура». Если в документе было 10 разных товаров, в результате мы получим все те же 10 строк. Группировка сработает только в том случае, если одна и та же номенклатура дублировалась в табличной части несколько раз — тогда для нее выберется максимальная сумма.

Если же мы попробуем убрать поле Номенклатура из секции СГРУППИРОВАТЬ ПО, то платформа 1С выдаст синтаксическую ошибку: «Поле не входит в группу...». Язык SQL строго требует, чтобы все выбираемые поля, к которым не применяются агрегатные функции (такие как МАКСИМУМ, СУММА, КОЛИЧЕСТВО), обязательно присутствовали в группировке.

Именно поэтому для решения задачи нам придется использовать многоэтапные запросы или вложенные конструкции.

Способ 1. Классический трехэтапный запрос через временные таблицы

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

Разберем логику построения такого запроса по шагам.

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


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

Шаг 2. Разрешение неоднозначности (борьба с дублями).
Представьте ситуацию: в чеке есть две строки с одинаковой максимальной суммой (например, два дорогих товара стоят по 5 000 рублей). Если мы сразу соединим первую таблицу с исходной табличной частью по полям Ссылка и Сумма, то на выходе получим две строки для одного документа. Чтобы этого избежать, нам нужно выбрать только одну из этих строк. Самый простой и надежный критерий — взять минимальный (или максимальный) номер строки НомерСтроки:


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

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


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

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

Способ 2. Оптимизированный двухэтапный запрос с группировкой

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

Посмотрим на этот изящный вариант:


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

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

Как это работает:

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

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

Способ 3. Использование группового оператора вхождения (Кортежи)

Малоизвестная, но очень мощная фича языка запросов 1С — это использование группового оператора вхождения В (сравнение кортежей). Эта конструкция позволяет сопоставлять сразу несколько полей с результатами вложенного подзапроса. С ее помощью мы можем решить всю задачу в рамках одного-единственного запроса вообще без использования механизма временных таблиц!

Рассмотрим структуру такого запроса:


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

Разберем по шагам, как СУБД выполняет этот запрос:

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

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

Критически важный нюанс: Индексация временных таблиц

Если в вашем запросе присутствует жесткий отбор по конкретному документу (например, ГДЕ ЧекККМТовары.Ссылка = &Ссылка), то скорость работы любого из предложенных вариантов будет мгновенной, так как СУБД будет обрабатывать всего несколько строк одной табличной части.

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

Обратите внимание на конструкцию в первом и втором способах:


ИНДЕКСИРОВАТЬ ПО
    Ссылка,
    Сумма

Если пренебречь индексацией временной таблицы ВТ_МаксимальныеСуммы, СУБД при соединении с реальной таблицей Документ.ЧекККМ.Товары будет вынуждена использовать неэффективные алгоритмы соединения (например, выполнять полный перебор строк — Nested Loops со сканированием всей таблицы). Создание индекса позволяет оптимизатору СУБД построить оптимальный план выполнения запроса с использованием Index Seek или Merge Join, что ускорит выполнение запроса в десятки и сотни раз на больших базах данных.

Какое решение выбрать?

Давайте сопоставим все три варианта, чтобы вы могли выбрать наиболее подходящий под ваши задачи:

Способ 3 (Групповой оператор «В»): Идеально подходит для простых отчетов, небольших по объему таблиц или случаев, когда вы делаете выборку по одному конкретному документу (передавая параметр &Ссылка). Он не засоряет менеджер временных таблиц и пишется быстрее всего. Минус: на огромных таблицах оптимизаторы СУБД (особенно PostgreSQL) могут построить неоптимальный план выполнения для коррелированного подзапроса EXISTS, в который платформа 1С транслирует оператор вхождения.
Способ 2 (Два этапа с группировкой): Является «золотым стандартом» для промышленной разработки в 1С. Он сочетает в себе высокую производительность благодаря использованию индексированной временной таблицы и компактность кода по сравнению с классическим трехэтапным методом. СУБД легко строит предсказуемый и быстрый план для такого запроса на любых объемах данных.
Способ 1 (Три этапа через НомерСтроки): Этот вариант стоит использовать, если вам принципиально важно получить номенклатуру строго из первой по порядку строки табличной части при наличии дублей сумм. Во всех остальных случаях Способ 2 будет предпочтительнее и быстрее.

Применяйте оптимальные методы построения запросов, не забывайте про индексацию временных таблиц, и ваши решения в 1С всегда будут работать максимально быстро и эффективно!