Как в запросе 1С рассчитать изменение цены в процентах за период

← На главную

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

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

Измерения: Номенклатура, Характеристика, ВидЦены;
Ресурсы: Цена;
Стандартный реквизит: Период.

Нам требуется получить список номенклатуры с ценой на начало периода, ценой на конец периода и процентом отклонения.

Анализ логических ошибок при получении цен на даты

Рассмотрим распространенную ошибку: использование виртуальной таблицы СрезПервых для получения начальной цены. Посмотрим, как ведет себя эта таблица: вызов СрезПервых(&НачалоПериода) возвращает первую запись, дата которой больше или равна переданной дате. Если цена на товар была установлена до начала анализируемого периода и внутри интервала не менялась, СрезПервых вернет пустое значение либо первую будущую установку цены, что искажает начальные данные.

Для получения актуальной действующей цены на любой момент времени (как на начало интервала, так и на конец) необходимо использовать исключительно СрезПоследних:

Для начальной цены: РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&НачалоПериода, ВидЦены = &ВидЦены);
Для конечной цены: РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&КонецПериода, ВидЦены = &ВидЦены).

Обратим внимание на передачу параметров даты: если передать в &КонецПериода дату без времени (начало дня), изменения цен, внесенные в течение последнего дня периода, не попадут в выборку. При передаче параметров во встроенном языке рекомендуется задавать границу с включением момента времени:


Запрос.УстановитьПараметр("КонецПериода", Новый Граница(КонецДня(КонецПериода), ВидГраницы.Включая));

Защита от деления на ноль и обработка отсутствующих значений

Следующая распространенная проблема — ошибка деления на ноль в выражении расчета процента (КонечнаяЦена - НачальнаяЦена) / НачальнаяЦена * 100. Если товар только появился и на начало периода цены не имел (значение равно 0 или NULL), запрос завершится аварийно.

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

Получение абсолютного значения разницы (положительного процента)

В языке запросов платформы 1С:Предприятие отсутствует встроенная математическая функция вычисления модуля числа (ABS). Если перед нами стоит задача отображать исключительно положительную величину отклонения (без минуса), разберем, как реализовать модуль числа через условную конструкцию:


ВЫБОР
    КОГДА (КонЦена - НачЦена) < 0 ТОГДА -(КонЦена - НачЦена)
    ИНАЧЕ (КонЦена - НачЦена)
КОНЕЦ

Решение 1. Оптимальный запрос с использованием временных таблиц и индексации

Рассмотрим наиболее надежный и масштабируемый вариант реализации. Стандарты разработки 1С рекомендуют соединять срезы регистров через временные таблицы с явной индексацией по полям соединения. Это позволяет оптимизатору СУБД построить эффективный план выполнения запроса даже на больших объемах данных.

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

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


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

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    Характеристика
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ЦеныКонец.Номенклатура КАК Номенклатура,
    ЦеныКонец.Характеристика КАК Характеристика,
    ЦеныКонец.Цена КАК КонЦена
ПОМЕСТИТЬ ВТ_ЦеныКонец
ИЗ
    РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&КонецПериода, ВидЦены = &ВидЦены) КАК ЦеныКонец

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    Характеристика
;

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

Разберем по шагам ключевые элементы этого запроса:

В первых двух пакетах мы выбираем актуальные цены на начало и конец периода во временные таблицы ВТ_ЦеныНачало и ВТ_ЦеныКонец, отсекая лишние записи по условию ВидЦены = &ВидЦены в параметрах виртуальной таблицы.
Инструкция ИНДЕКСИРОВАТЬ ПО ускоряет последующее соединение.
В основном запросе используется ПОЛНОЕ СОЕДИНЕНИЕ, что гарантирует вывод позиций, по которым цена была установлена только на одну из дат.
Функция ЕСТЬNULL в полях выборки защищает от пустых ссылок в измерениях и предотвращает ошибки арифметики при расчетах с ресурсами.
Функция ВЫРАЗИТЬ(... КАК ЧИСЛО(15, 2)) округляет вычисленный процент до двух знаков после запятой.

Решение 2. Расчет только по сопоставимым позициям (Внутреннее соединение)

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


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