Как убрать дубли в запросе по табличной части, сохранив исходный порядок строк и выбрав максимальную цену?

← На главную

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

В этой статье мы разберем несколько стратегий решения данной задачи: от простых манипуляций с порядком до использования современных оконных функций и классических соединений SQL.

Почему сбивается порядок строк и как это работает в СУБД

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

Решение 1: Использование агрегатных функций для сохранения сортировки

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

Рассмотрим пример запроса:


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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ.НомерСтроки,
    ВТ.Номенклатура,
    ВТ.Цена
ИЗ
    ВТ_СгруппированныеДанные КАК ВТ
УПОРЯДОЧИТЬ ПО
    ВТ.НомерСтроки ВОЗР

Важный нюанс по производительности: как отмечалось в обсуждении, на больших объемах данных (свыше 10 000 строк) вычисление агрегатов по номеру строки может замедлить запрос. Чтобы этого избежать, убедитесь, что временная таблица проиндексирована по полям группировки.

Решение 2: Получение точного номера строки с максимальной ценой (через соединение)

Выясним причину, по которой обычная группировка не подходит для поиска «той самой строки». Если в группе из трех дублей максимальная цена находится во второй строке, то МАКСИМУМ(Цена) и МАКСИМУМ(НомерСтроки) могут вернуть данные из разных физических строк (цену из второй, а номер — из третьей).

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

Сначала найдем максимальную цену для каждой номенклатуры и поместим во временную таблицу.
Затем соединим исходную таблицу с этой временной по двум полям: по Номенклатуре и по Цене.


// Шаг 1: Находим максимумы
ВЫБРАТЬ
    ТЧ.Номенклатура КАК Номенклатура,
    МАКСИМУМ(ТЧ.Цена) КАК МаксЦена
ПОМЕСТИТЬ ВТ_МаксимальныеЦены
ИЗ
    ТабЧасть КАК ТЧ
СГРУППИРОВАТЬ ПО
    ТЧ.Номенклатура;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Получаем номер строки через соединение
ВЫБРАТЬ
    МИНИМУМ(ТЧ.НомерСтроки) КАК НомерСтроки, // МИН на случай, если есть две строки с одинаковой макс. ценой
    ТЧ.Номенклатура,
    ТЧ.Цена
ИЗ
    ТабЧасть КАК ТЧ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_МаксимальныеЦены КАК Макс
    ПО ТЧ.Номенклатура = Макс.Номенклатура
    И ТЧ.Цена = Макс.МаксЦена
СГРУППИРОВАТЬ ПО
    ТЧ.Номенклатура,
    ТЧ.Цена
УПОРЯДОЧИТЬ ПО
    НомерСтроки ВОЗР

Решение 3: Оконные функции (для платформы 8.3.15 и выше)

Если ваша конфигурация работает на современной платформе, проанализируем использование оконных функций. Это самый изящный и производительный способ решения задачи «Top-1 в группе». Функция ROW_NUMBER() позволяет пронумеровать строки внутри каждой группы номенклатуры, отсортировав их по цене прямо внутри группы.


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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    НомерСтроки,
    Номенклатура,
    Цена
ИЗ
    ВТ_Пронумерованные
ГДЕ
    НомерВГруппе = 1
УПОРЯДОЧИТЬ ПО
    НомерСтроки ВОЗР

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

Решение 4: Информирование пользователя о диапазоне дублей

Рассмотрим нестандартный подход, предложенный автором темы. Если задача стоит не просто «удалить», а «обработать и подсветить», можно выводить пользователю диапазон строк, в которых встречалась данная номенклатура. Это помогает сохранить контекст исходного документа.

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

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

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

Совет: Если вам нужно сохранить целостность строки, всегда выбирайте одну «ведущую» колонку, по которой определяется максимальность всей записи, и используйте методы из Решения 2 или Решения 3.