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

← На главную

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

Разберем по шагам, как правильно решить эту задачу, избежать падения производительности и какие альтернативные способы существуют в платформе 1С:Предприятие.

Решение с использованием функции НАЧАЛОПЕРИОДА

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

Пример реализации в коде:


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

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

Оптимизация: периодичность «Авто» в виртуальных таблицах

Рассмотрим более изящный способ работы с периодами. В виртуальных таблицах регистров накопления, таких как Продажи.Обороты, существует параметр периодичности. Если вместо значений День или Месяц мы установим значение Авто, платформа автоматически добавит в выборку сразу несколько полей периодов: ПериодДень, ПериодМесяц, ПериодДекада и так далее.

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

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

Описанный выше запрос использует левое соединение виртуальной таблицы оборотов с физической таблицей регистра сведений по условию "больше или равно" (ПродажиОбороты.Период >= ЦеныНоменклатуры.Период). Проанализируем ситуацию с точки зрения СУБД: этот способ известен как одна из главных причин падения производительности на больших базах данных. SQL-серверу приходится соединять каждую строку продаж со всеми историческими записями цен для данной номенклатуры, образуя декартово произведение.

Если мы пишем запрос в коде, эталонным решением будет использование временных таблиц:

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

Этот подход гарантирует высокую скорость работы даже на базах с миллионами записей.

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

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

Разберем настройку по шагам:

Создаем Набор данных 1: В нем мы выбираем только обороты продаж с датой продажи (периодичность День или Авто).
Создаем Набор данных 2: В нем мы обращаемся к виртуальной таблице ЦеныНоменклатуры.СрезПоследних.
Переходим на вкладку «Связи наборов данных».
Связываем наборы: поле Номенклатура связывается с Номенклатурой.
Самое важное: поле «Период» (или дата продажи) из первого набора передаем в качестве значения параметра Период виртуальной таблицы во второй набор.

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