Как в запросе 1С выбрать строку табличной части с максимальным значением реквизита?

← На главную

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

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

В чем сложность простой группировки?

Первая интуитивная попытка решить задачу «в лоб» обычно выглядит следующим образом:


ВЫБРАТЬ
    КонтрагентыТЧСостав.Ссылка КАК ПервичныйКонтрагент,
    КонтрагентыТЧСостав.Контрагент КАК Контрагент,
    МАКСИМУМ(КонтрагентыТЧСостав.Контрагент.ВесЗаполненности) КАК Вес
ИЗ
    Справочник.КонтрагентыТЧ.Состав КАК КонтрагентыТЧСостав
СГРУППИРОВАТЬ ПО
    КонтрагентыТЧСостав.Ссылка,
    КонтрагентыТЧСостав.Контрагент

Проанализируем эту ситуацию. Если мы добавляем поле Контрагент в секцию СГРУППИРОВАТЬ ПО, СУБД будет группировать записи по уникальным парам «Владелец ТЧ + Подчиненный контрагент». В результате запрос вернет абсолютно все строки табличной части, так как каждая строка содержит уникального подчиненного контрагента. Функция МАКСИМУМ в данном случае просто вернет вес конкретного контрагента в этой строке, и никакого отбора по максимальному значению среди всех строк одного владельца не произойдет.

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

Решение задачи для одного конкретного элемента

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


ВЫБРАТЬ ПЕРВЫЕ 1
    КонтрагентыТЧСостав.Контрагент КАК Контрагент,
    КонтрагентыТЧСостав.Контрагент.ВесЗаполненности КАК Вес
ИЗ
    Справочник.КонтрагентыТЧ.Состав КАК КонтрагентыТЧСостав
ГДЕ
    КонтрагентыТЧСостав.Ссылка = &Выб
УПОРЯДОЧИТЬ ПО
    Вес УБЫВ

Этот способ прост и эффективен. Конструкция ВЫБРАТЬ ПЕРВЫЕ 1 в сочетании с УПОРЯДОЧИТЬ ПО ... УБЫВ гарантирует, что мы получим ровно одну строку, у которой значение веса будет наибольшим. Однако данный подход совершенно не работает, если нам нужно выбрать данные сразу для множества контрагентов-владельцев в одном запросе, например, для формирования списка или отчета по всей базе данных.

Классическое решение для множества элементов через Временные Таблицы

Для того чтобы получить наиболее «весомые» строки для множества элементов в одном запросе, нам потребуется составить пакетный запрос. Разберем этот алгоритм по шагам:

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

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


ВЫБРАТЬ
    ТабличнаяЧасть.Ссылка КАК Ссылка,
    МАКСИМУМ(ТабличнаяЧасть.Контрагент.ВесЗаполненности) КАК Вес
ПОМЕСТИТЬ МаксимальныйВес
ИЗ
    Справочник.Контрагенты.Состав КАК ТабличнаяЧасть
СГРУППИРОВАТЬ ПО
    ТабличнаяЧасть.Ссылка
;

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

Обратите внимание на важную деталь во втором запросе пакета: поле ТабличнаяЧасть.Контрагент обернуто в агрегатную функцию МАКСИМУМ. Зачем это сделано? Если в табличной части одного владельца окажется несколько строк с одинаковым максимальным весом (например, у двух подчиненных клиентов вес равен 5), то без дополнительной группировки запрос вернет обе эти строки. Применение функции МАКСИМУМ() к ссылочному полю гарантирует, что для каждого владельца будет выбрана ровно одна запись (в данном случае — имеющая больший уникальный идентификатор GUID в базе данных).

Альтернативный вариант: Использование многополейного сравнения

В платформе 1С существует возможность избежать создания громоздких пакетов с временными таблицами, если объем обрабатываемых данных невелик. Для этого можно применить условие многополейного сравнения с вложенным запросом ГДЕ (Поле1, Поле2) В (ВЫБРАТЬ...). Посмотрим на этот лаконичный пример:


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

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

Дополнительное условие: Получение максимума в разрезе категорий

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

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


ВЫБРАТЬ
    ТабличнаяЧасть.Ссылка КАК Ссылка,
    ТабличнаяЧасть.Контрагент.Категория КАК Категория,
    МАКСИМУМ(ТабличнаяЧасть.Контрагент.ВесЗаполненности) КАК Вес
ПОМЕСТИТЬ МаксимальныйВесПоКатегориям
ИЗ
    Справочник.Контрагенты.Состав КАК ТабличнаяЧасть
СГРУППИРОВАТЬ ПО
    ТабличнаяЧасть.Ссылка,
    ТабличнаяЧасть.Контрагент.Категория
;

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

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

Оптимизация производительности: Избавляемся от "точек"

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

Проанализируем оптимальный способ написания такого запроса (Best Practice):


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

// Индексируем поля, по которым будем группировать и соединять
ИНДЕКСИРОВАТЬ ПО
    Ссылка,
    Категория,
    Вес
;

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

ИНДЕКСИРОВАТЬ ПО
    Ссылка,
    Категория,
    МаксВес
;

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

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

Решение средствами Системы компоновки данных (СКД)

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

Рассмотрим, как настроить получение максимального элемента средствами СКД:

В схеме компоновки данных создайте простой запрос, выбирающий абсолютно все строки табличной части (без группировок и максимумов).
Перейдите на вкладку Настройки.
Добавьте группировку по полю Ссылка (головной контрагент). При необходимости внутри нее добавьте группировку по полю Категория.
Внутри этой группировки добавьте детальные записи (или группировку по подчиненному контрагенту).
Выберите созданную внутреннюю группировку (детальные записи) и перейдите в ее системные свойства (вкладка Другие настройки в нижней части окна настроек СКД).
Найдите параметр «Выбирать первые» и установите для него значение 1.
Перейдите на вкладку Сортировка для этой же внутренней группировки и укажите сортировку по полю ВесЗаполненности по Убыванию.

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