Как в запросе 1С свернуть дублирующиеся строки и рассчитать сумму и цену?

← На главную

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

Анализ типичной ошибки в секциях СГРУППИРОВАТЬ ПО и ВЫБРАТЬ

Посмотрим на исходный фрагмент запроса, в котором не происходит объединение повторяющихся позиций:


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

Выясним причину, почему данные не сворачиваются:

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

Способ 1. Корректная группировка и расчет цены в запросе

Разберем по шагам, как исправить запрос:

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

Посмотрим на исправленный текст запроса:


ВЫБРАТЬ
    ТребованиеНакладнаяМатериалы.Номенклатура.Код КАК Код,
    ТребованиеНакладнаяМатериалы.Номенклатура.Наименование КАК Наименование,
    ТребованиеНакладнаяМатериалы.Ссылка.Номер КАК Номер,
    ТребованиеНакладнаяМатериалы.Ссылка.Дата КАК Дата,
    ТребованиеНакладнаяМатериалы.Ссылка.Склад КАК Склад,
    ТребованиеНакладнаяМатериалы.Счет КАК Счет,
    ТребованиеНакладнаяМатериалы.СчетЗатрат КАК СчетЗатрат,
    ТребованиеНакладнаяМатериалы.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
    ТребованиеНакладнаяМатериалы.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    ТребованиеНакладнаяМатериалы.СерияНоменклатуры КАК СерияНоменклатуры,
    СУММА(ТребованиеНакладнаяМатериалы.Количество) КАК Количество,
    СУММА(ЕСТЬNULL(ПартииТоваровНаСкладахБухгалтерскийУчет.Стоимость, 0)) КАК Сумма,
    ВЫБОР
        КОГДА СУММА(ТребованиеНакладнаяМатериалы.Количество) = 0 ТОГДА 0
        ИНАЧЕ СУММА(ЕСТЬNULL(ПартииТоваровНаСкладахБухгалтерскийУчет.Стоимость, 0)) / СУММА(ТребованиеНакладнаяМатериалы.Количество)
    КОНЕЦ КАК Цена
ИЗ
    Документ.ТребованиеНакладная.Материалы КАК ТребованиеНакладнаяМатериалы
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПартииТоваровНаСкладахБухгалтерскийУчет КАК ПартииТоваровНаСкладахБухгалтерскийУчет
        ПО ТребованиеНакладнаяМатериалы.Номенклатура = ПартииТоваровНаСкладахБухгалтерскийУчет.Номенклатура
        И ТребованиеНакладнаяМатериалы.СерияНоменклатуры = ПартииТоваровНаСкладахБухгалтерскийУчет.СерияНоменклатуры
        И ТребованиеНакладнаяМатериалы.Склад = ПартииТоваровНаСкладахБухгалтерскийУчет.Склад
        И ПартииТоваровНаСкладахБухгалтерскийУчет.Регистратор = &ТекущийДокумент
ГДЕ
    ТребованиеНакладнаяМатериалы.Ссылка = &ТекущийДокумент
СГРУППИРОВАТЬ ПО
    ТребованиеНакладнаяМатериалы.Номенклатура.Код,
    ТребованиеНакладнаяМатериалы.Номенклатура.Наименование,
    ТребованиеНакладнаяМатериалы.Ссылка.Номер,
    ТребованиеНакладнаяМатериалы.Ссылка.Дата,
    ТребованиеНакладнаяМатериалы.Ссылка.Склад,
    ТребованиеНакладнаяМатериалы.Счет,
    ТребованиеНакладнаяМатериалы.СчетЗатрат,
    ТребованиеНакладнаяМатериалы.ЕдиницаИзмерения,
    ТребованиеНакладнаяМатериалы.ХарактеристикаНоменклатуры,
    ТребованиеНакладнаяМатериалы.СерияНоменклатуры

Способ 2. Использование пакета с временными таблицами (защита от задвоения)

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

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


ВЫБРАТЬ
    Материалы.Номенклатура КАК Номенклатура,
    Материалы.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    Материалы.СерияНоменклатуры КАК СерияНоменклатуры,
    Материалы.Склад КАК Склад,
    Материалы.Счет КАК Счет,
    Материалы.СчетЗатрат КАК СчетЗатрат,
    Материалы.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
    СУММА(Материалы.Количество) КАК Количество
ПОМЕСТИТЬ ВТ_Материалы
ИЗ
    Документ.ТребованиеНакладная.Материалы КАК Материалы
ГДЕ
    Материалы.Ссылка = &ТекущийДокумент
СГРУППИРОВАТЬ ПО
    Материалы.Номенклатура,
    Материалы.ХарактеристикаНоменклатуры,
    Материалы.СерияНоменклатуры,
    Материалы.Склад,
    Материалы.Счет,
    Материалы.СчетЗатрат,
    Материалы.ЕдиницаИзмерения
;

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

ВЫБРАТЬ
    ВТ_Материалы.Номенклатура.Код КАК Код,
    ВТ_Материалы.Номенклатура.Наименование КАК Наименование,
    ВТ_Материалы.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
    ВТ_Материалы.Счет КАК Счет,
    ВТ_Материалы.СчетЗатрат КАК СчетЗатрат,
    ВТ_Материалы.Количество КАК Количество,
    ЕСТЬNULL(ВТ_Стоимость.Стоимость, 0) КАК Сумма,
    ВЫБОР
        КОГДА ВТ_Материалы.Количество = 0 ТОГДА 0
        ИНАЧЕ ЕСТЬNULL(ВТ_Стоимость.Стоимость, 0) / ВТ_Материалы.Количество
    КОНЕЦ КАК Цена
ИЗ
    ВТ_Материалы КАК ВТ_Материалы
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Стоимость КАК ВТ_Стоимость
        ПО ВТ_Материалы.Номенклатура = ВТ_Стоимость.Номенклатура
        И ВТ_Материалы.ХарактеристикаНоменклатуры = ВТ_Стоимость.ХарактеристикаНоменклатуры
        И ВТ_Материалы.СерияНоменклатуры = ВТ_Стоимость.СерияНоменклатуры
        И ВТ_Материалы.Склад = ВТ_Стоимость.Склад

Способ 3. Программное сворачивание в ТаблицеЗначений

Если данные уже получены в виде коллекции ТаблицаЗначений, можно воспользоваться встроенным методом платформы Свернуть():


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

// Сворачиваем по ключевым колонкам аналитики, суммируя ресурсы
ТаблицаПечати.Свернуть("Код, Наименование, Склад, ЕдиницаИзмерения, Счет, СчетЗатрат, ХарактеристикаНоменклатуры, СерияНоменклатуры", "Количество, Сумма");

// Добавляем колонку цены и рассчитываем ее значения
ТаблицаПечати.Колонки.Добавить("Цена", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));

Для Каждого Стр Из ТаблицаПечати Цикл
    Если Стр.Количество <> 0 Тогда
        Стр.Цена = Стр.Сумма / Стр.Количество;
    Иначе
        Стр.Цена = 0;
    КонецЕсли;
КонецЦикла;

Важные рекомендации

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