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

← На главную

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

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

Анализ ошибок исходного запроса

Посмотрим на исходный вариант запроса, который вызвал затруднения:


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

Выясним, какие неточности и ошибки допущены в этом тексте:

Отсутствие общих итогов: конструкция ПО Номенклатура рассчитывает суммы только в разрезе значений группировки. Для вывода общей строки итогов по всей таблице обязательно требуется ключевое слово ОБЩИЕ.
Отсутствие фильтрации по периоду и организации: в запросе не переданы параметры отбора, поэтому выборка строится по абсолютно всем записям базы данных.
Использование избыточной таблицы: для получения только прихода и расхода применяется таблица ОстаткиИОбороты. Расчет остатков требует дополнительных ресурсов СУБД, поэтому для оборотов оптимальнее использовать виртуальную таблицу Обороты.

Как правильно настроить секцию ИТОГИ

Для одновременного расчета общих итогов по всему результату запроса и промежуточных итогов по группировочным полям в секции ИТОГИ после ключевого слова ПО указывается служебное слово ОБЩИЕ через запятую с полями группировки:


ИТОГИ
    СУММА(КоличествоПриход),
    СУММА(КоличествоРасход)
ПО
    ОБЩИЕ,
    Номенклатура

Важно: при программной обработке такого запроса методом Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам) первая возвращаемая строка будет иметь тип ТипЗаписиЗапроса.ИтогПоВсему, затем пойдут строки ТипЗаписиЗапроса.ИтогПоГруппировке, и только на нижнем уровне выборки — ТипЗаписиЗапроса.ДетальнаяЗапись. В консоли запросов для просмотра общих итогов результат следует переключать в режим отображения дерева.

Параметры виртуальной таблицы против секции ГДЕ

Разберем важную архитектурную особенность платформы 1С: обращение к полям виртуальных таблиц.

Если попытаться написать условие вида ГДЕ ТоварыОрганизаций.Период МЕЖДУ &ДатаНачала И &ДатаКонца к виртуальной таблице Обороты или ОстаткиИОбороты, платформа вернет ошибку «Поле не найдено: Период». Поле Период в виртуальной таблице доступно только в том случае, если в ее параметрах явно задана периодичность (например, Период, День, Месяц или Регистратор).

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

Вариант 1. Оптимальное решение через виртуальную таблицу «Обороты»

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


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

Обратите внимание: секция СГРУППИРОВАТЬ ПО здесь не требуется, так как виртуальная таблица Обороты уже возвращает свернутые данные по непустым измерениям, указанным в секции ВЫБРАТЬ.

Вариант 2. Решение через физическую таблицу регистра (с явным использованием ГДЕ)

Если условие задачи строго требует фильтрации периода именно через конструкцию ГДЕ, запрос строится к физической (реальной) таблице движений регистра РегистрНакопления.ТоварыОрганизаций.

В реальной таблице нет готовых ресурсов КоличествоПриход и КоличествоРасход — вместо этого хранится направление движения в системном поле ВидДвижения. Разделим движения с помощью условной конструкции ВЫБОР:


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

Этот вариант полностью удовлетворяет учебным требованиям фильтрации через ГДЕ, корректно рассчитывает приходы и расходы по видам движений и формирует общие и группировочные итоги.