При разработке аналитических отчетов в системе компоновки данных (СКД) программисты часто сталкиваются с задачей вывода динамики показателей. Типичный пример — отчет "Процент изменения цены поставщика по товарам за период". На первый взгляд задача кажется простой: достаточно вычесть из текущей цены предыдущую, разделить на предыдущую и умножить на 100. Однако на практике разработчик сталкивается с серьезными архитектурными препятствиями. Для дополнения внутренней аналитики внешними данными о конкурентах можно использовать сервис парсинга цен на маркетплейсах.
Давайте проанализируем ситуацию. Базовый регистр сведений ЦеныПоставщиков хранит записи только тогда, когда цена действительно менялась. Если в январе цена товара составляла 100 рублей, в феврале не менялась, а в марте стала 120 рублей, то в регистре будут только две записи: за январь и за март. В феврале образуется "пустота". Если мы построим обычный отчет по месяцам, то для февраля система вернет значение NULL. Из-за этого мы не сможем корректно рассчитать процент изменения ни для февраля (по отношению к январю), ни для марта (по отношению к февралю).
Для решения этой задачи нам необходимо решить две ключевые проблемы:
Рассмотрим подробнее два наиболее эффективных и профессиональных способа реализации этой задачи.
Этот подход является наиболее изящным, так как он полностью перекладывает задачу получения "среза последних на каждую дату" на плечи самого процессора компоновки данных. Нам не придется писать сложные запросы с декартовым умножением таблиц.
Разберем этот метод по шагам.
Шаг 1. Создание Набора данных №1 (Сетка периодов и товаров)
Сначала нам нужно получить базовую таблицу, в которой будут присутствовать все анализируемые месяцы и товары. Мы можем получить список месяцев из производственного календаря или таблицы регламентированных периодов, а товары — из справочника номенклатуры. Сделаем декартово умножение, чтобы для каждого товара была строка на каждый месяц.
Пример запроса для НабораДанных1:
ВЫБРАТЬ
Номенклатура.Ссылка КАК Товар,
Календарь.ПериодМесяц КАК ПериодМесяц
ИЗ
Справочник.Номенклатура КАК Номенклатура
КРОСС СОЕДИНЕНИЕ (
ВЫБРАТЬ РАЗЛИЧНЫЕ
НАЧАЛОПЕРИОДА(РегламентированныйПроизводственныйКалендарь.ДатаКалендаря, МЕСЯЦ) КАК ПериодМесяц
ИЗ
РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РегламентированныйПроизводственныйКалендарь
ГДЕ
РегламентированныйПроизводственныйКалендарь.ДатаКалендаря МЕЖДУ &НачалоПериода И &КонецПериода
) КАК Календарь
ГДЕ
НЕ Номенклатура.ЭтоГруппа
Шаг 2. Создание Набора данных №2 (Получение цен)
Второй набор данных будет обращаться к виртуальной таблице среза последних нашего регистра цен. Обратите внимание, что мы делаем отбор по параметрам, которые будут передаваться из первого набора данных для каждой строки индивидуально. Автоматизировать наполнение этого регистра данными из внешних файлов поможет сбор прайс-листов с почты, FTP и HTTP.
Пример запроса для НабораДанных2:
ВЫБРАТЬ
Цены.Номенклатура КАК Товар,
Цены.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатурыПоставщиков.СрезПоследних(&ПериодСреза, Номенклатура = &Товар) КАК Цены
Шаг 3. Настройка связи наборов данных
Переходим на вкладку "Связи наборов данных". Именно здесь происходит магия "протягивания" цен. Нам нужно связать наши наборы следующим образом:
Товар = ТоварПериодСреза связывается с полем источника ПериодМесяцТовар связывается с полем источника ТоварБлагодаря этой настройке СКД для каждой строки из НабораДанных1 (где есть конкретный товар и конкретный месяц) выполнит микро-запрос к срезу последних на этот месяц. Если в марте движений не было, СКД передаст в параметр дату конца марта и получит актуальную цену, зарегистрированную, например, еще в январе. Таким образом, у нас не останется пустых значений цен.
Шаг 4. Настройка ресурсов и формулы расчета процента
Для того чтобы рассчитать процент изменения цены к предыдущему месяцу, перейдем на вкладку "Ресурсы". Использовать обычные вычисляемые поля здесь не рекомендуется, так как расчет должен выполняться строго на уровне группировок компоновки данных.
Добавим в ресурсы поле Цена с выражением Максимум(Цена). Затем добавим поле ПроцентИзменения и пропишем для него формулу с использованием мощной встроенной функции ВычислитьВыражение:
Выбор
Когда ВычислитьВыражение("ЕстьNull(Максимум(Цена), 0)", "ПериодМесяц", , "Предыдущая", "Предыдущая") = 0
Тогда 0
Иначе ((Максимум(Цена) - ВычислитьВыражение("Максимум(Цена)", "ПериодМесяц", , "Предыдущая", "Предыдущая")) / ВычислитьВыражение("Максимум(Цена)", "ПериодМесяц", , "Предыдущая", "Предыдущая")) * 100
Конец
Давайте подробно проанализируем синтаксис этой формулы:
ВычислитьВыражение — встроенная функция СКД, позволяющая получать значения из других детальных записей или группировок."Максимум(Цена)" — первое выражение функции. Мы обязаны использовать агрегатные функции, так как расчет идет на уровне ресурса."ПериодМесяц" — имя группировки, в контексте которой рассчитывается выражение."Предыдущая", "Предыдущая") указывают смещение относительно текущей строки. Мы берем значение строго из предыдущей строки группировки (то есть за предыдущий месяц).Выбор Когда ... = 0 Тогда 0 Иначе ... Конец предохраняет отчет от критической ошибки деления на ноль в тех случаях, когда для товара еще нет истории цен (например, в самый первый месяц отчета).---
Если по каким-то причинам вы не хотите использовать связи наборов данных (например, из-за возможных проблем с производительностью при работе с огромными базами данных), всю логику можно перенести в один поисковый запрос. Этот метод сложнее в реализации, но он выдает СУБД готовый пакет данных для обработки за один проход.
Выясним алгоритм работы такого запроса:
Проанализируем пример текста запроса для единственного набора данных:
ВЫБРАТЬ
Номенклатура.Ссылка КАК Товар,
Календарь.ПериодМесяц КАК ПериодМесяц
ПОМЕСТИТЬ ВТ_ТоварыПериоды
ИЗ
Справочник.Номенклатура КАК Номенклатура
КРОСС СОЕДИНЕНИЕ (
ВЫБРАТЬ РАЗЛИЧНЫЕ
НАЧАЛОПЕРИОДА(РегламентированныйПроизводственныйКалендарь.ДатаКалендаря, МЕСЯЦ) КАК ПериодМесяц
ИЗ
РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РегламентированныйПроизводственныйКалендарь
ГДЕ
РегламентированныйПроизводственныйКалендарь.ДатаКалендаря МЕЖДУ &НачалоПериода И &КонецПериода
) КАК Календарь
ГДЕ
НЕ Номенклатура.ЭтоГруппа
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Сетка.Товар КАК Товар,
Сетка.ПериодМесяц КАК ПериодМесяц,
МАКСИМУМ(ЦеныРегистр.Период) КАК АктуальнаяДатаЦены
ПОМЕСТИТЬ ВТ_МаксимальныеДаты
ИЗ
ВТ_ТоварыПериоды КАК Сетка
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатурыПоставщиков КАК ЦеныРегистр
ПО Сетка.Товар = ЦеныРегистр.Номенклатура
И ЦеныРегистр.Период <= Сетка.ПериодМесяц
ГРУППИРОВАТЬ ПО
Сетка.Товар,
Сетка.ПериодМесяц
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Даты.Товар КАК Товар,
Даты.ПериодМесяц КАК ПериодМесяц,
ЕСТЬNULL(ЦеныРегистр.Цена, 0) КАК Цена
ИЗ
ВТ_МаксимальныеДаты КАК Даты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатурыПоставщиков КАК ЦеныРегистр
ПО Даты.Товар = ЦеныРегистр.Номенклатура
И Даты.АктуальнаяДатаЦены = ЦеныРегистр.Период
После выполнения этого запроса мы получаем абсолютно плоскую и готовую таблицу. В ней для каждого месяца гарантированно будет стоять актуальная на тот момент цена номенклатуры. Нам остается лишь передать эти данные в СКД и в настройках ресурсов написать уже знакомую нам формулу расчета процента изменения:
Выбор
Когда ВычислитьВыражение("ЕстьNull(Максимум(Цена), 0)", "ПериодМесяц", , "Предыдущая", "Предыдущая") = 0
Тогда 0
Иначе ((Максимум(Цена) - ВычислитьВыражение("Максимум(Цена)", "ПериодМесяц", , "Предыдущая", "Предыдущая")) / ВычислитьВыражение("Максимум(Цена)", "ПериодМесяц", , "Предыдущая", "Предыдущая")) * 100
Конец
Оба метода успешно решают поставленную задачу, но имеют свои архитектурные особенности:
Для большинства задач тестирования и реального управленческого учета в средних базах первый метод со связями наборов данных показывает себя как наиболее надежное и быстрое в реализации решение. Для полной роботизации подготовки и проверки подобной управленческой отчетности можно внедрить платформу One RPA.