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

← На главную

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

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

Причина сложности: почему нельзя просто добавить поле в группировку

Рассмотрим структуру исходных данных на примере:

У нас есть таблица со следующими строками:
Товар 1 — Данные 1 — 11
Товар 1 — Данные 2 — 26
Товар 2 — Данные 3 — 57
Товар 2 — Данные 4 — 24
Товар 3 — Данные 5 — 13
Товар 3 — Данные 6 — 42

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

В реляционных СУБД для таких задач применяются оконные функции (например, ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)), однако в языке запросов 1С оконные функции и коррелированные подзапросы с ВЫБРАТЬ ПЕРВЫЕ 1 в списке полей не поддерживаются. Поэтому стандартным и наиболее эффективным решением является применение пакетного запроса и временных таблиц.

Решение через пакетный запрос и временную таблицу

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

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

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


ВЫБРАТЬ
    ИсходнаяТаблица.Товар КАК Товар,
    ИсходнаяТаблица.Данные КАК Данные,
    ИсходнаяТаблица.Число КАК Число
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
    &ТаблицаДанных КАК ИсходнаяТаблица
;

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

СГРУППИРОВАТЬ ПО
    ВТ.Товар
;

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

Проблема неоднозначности и задвоения строк

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

Товар 2 — Данные 3 — 57
Товар 2 — Данные 4 — 57

В этом случае простое внутреннее или левое соединение найдет сразу две подходящие строки и вернет обе записи, что приведет к дублированию товара в итоговой выборке. Чтобы исключить неоднозначность, применяют следующие подходы:

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

Рассмотрим пример итогового запроса с защитой от дублей через дополнительную группировку:


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

СГРУППИРОВАТЬ ПО
    ВТ_Максимумы.Товар,
    ВТ_Максимумы.МаксЧисло

Альтернативные способы решения

В зависимости от архитектуры решения и объема обрабатываемых данных можно применять альтернативные механизмы:

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