В практике разработки на платформе 1С:Предприятие часто возникает задача анализа изменения цен во времени. Типичный пример — отчет, в котором для каждой даты изменения цены номенклатуры необходимо вывести не только вновь установленную цену, но и ту, которая действовала непосредственно перед ней (предыдущую цену). Такая задача также называется получением среза последних на каждую дату изменений.
В этой статье мы подробно разберем, как решить эту задачу различными способами: с помощью классического пакетного запроса, оптимизированного под СУБД, а также с использованием современных возможностей Системы компоновки данных (СКД), которые позволяют обойтись без написания тяжелых и сложных запросов.
Представим, что в нашей базе данных в регистре сведений ЦеныНоменклатуры для товара «Товар 1» зафиксированы следующие изменения цен:
Пользователь строит отчет за период с 01.08.18 по 08.08.18. В отчет должны попасть все изменения цен, произошедшие внутри этого периода, а также значение цены, которая предшествовала первому изменению в периоде (даже если сама эта цена была установлена за пределами выбранного интервала). На выходе мы должны получить следующую таблицу:
| Товар | Цена новая (текущая) | Цена старая (предыдущая) | Дата переоценки |
|---|---|---|---|
| Товар 1 | 30 | 25 | 07.08.18 |
| Товар 1 | 25 | 22 | 06.08.18 |
| Товар 1 | 22 | 20 | 05.08.18 |
| Товар 1 | 20 | 41 | 01.08.18 |
Обратите внимание на последнюю строку: новая цена на 01.08.18 равна 20 руб., а предыдущая цена (41 руб.) берется из записи от 05.07.18, которая находится вне выбранного пользователем периода. Это ключевая сложность, которую мы сейчас научимся обходить.
---
Построение такого запроса «в лоб» через виртуальные таблицы среза последних на каждую дату невозможно, так как язык запросов 1С не поддерживает динамические параметры в соединениях виртуальных таблиц. Поэтому мы будем использовать реальную таблицу регистра сведений и механизм временных таблиц (ВТ).
Разберем логику построения пакета запросов по шагам.
Для начала нам нужно выбрать все записи регистра сведений за интересующий нас интервал времени. Поместим их во временную таблицу ВТ_ЦеныПериода. Чтобы избежать дублирования информации, обязательно сгруппируем данные по ключевым полям измерений (номенклатура и вид цены).
ВЫБРАТЬ
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
ЦеныНоменклатуры.ВидЦены КАК ВидЦены,
ЦеныНоменклатуры.Период КАК Период,
ЦеныНоменклатуры.Цена КАК Цена
ПОМЕСТИТЬ ВТ_ЦеныПериода
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ГДЕ
ЦеныНоменклатуры.Период МЕЖДУ &НачалоПериода И &КонецПериода
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
ВидЦены,
Период
;
Важное примечание: Обратите внимание на инструкцию ИНДЕКСИРОВАТЬ ПО. Индексирование временной таблицы по полям связи критически важно для производительности СУБД при последующих неэквивалентных соединениях.
Теперь для каждого изменения цены из нашей таблицы ВТ_ЦеныПериода нам нужно найти ближайшую предыдущую дату регистрации цены в системе. Для этого мы левым соединением подключаем физическую таблицу регистра сведений РегистрСведений.ЦеныНоменклатуры по условию «период меньше периода текущего изменения» и выбираем максимум из этих дат.
ВЫБРАТЬ
ВТ_ЦеныПериода.Номенклатура КАК Номенклатура,
ВТ_ЦеныПериода.ВидЦены КАК ВидЦены,
ВТ_ЦеныПериода.Период КАК ПериодТекущий,
ВТ_ЦеныПериода.Цена КАК ЦенаТекущая,
МАКСИМУМ(ЦеныНоменклатурыИсторические.Период) КАК ПериодПредыдущий
ПОМЕСТИТЬ ВТ_СопоставлениеДат
ИЗ
ВТ_ЦеныПериода КАК ВТ_ЦеныПериода
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатурыИсторические
ПО ВТ_ЦеныПериода.Номенклатура = ЦеныНоменклатурыИсторические.Номенклатура
И ВТ_ЦеныПериода.ВидЦены = ЦеныНоменклатурыИсторические.ВидЦены
И ВТ_ЦеныПериода.Период > ЦеныНоменклатурыИсторические.Период
СГРУППИРОВАТЬ ПО
ВТ_ЦеныПериода.Номенклатура,
ВТ_ЦеныПериода.ВидЦены,
ВТ_ЦеныПериода.Период,
ВТ_ЦеныПериода.Цена
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
ВидЦены,
ПериодПредыдущий
;
Благодаря условию соединения ВТ_ЦеныПериода.Период > ЦеныНоменклатурыИсторические.Период и группировке по МАКСИМУМ(ЦеныНоменклатурыИсторические.Период), мы получаем точную дату предыдущего изменения цены для каждой строки отчета. Использование ЛЕВОГО СОЕДИНЕНИЯ гарантирует, что если у товара вообще не было ранней цены (это самая первая переоценка), запись все равно останется в результате, а предыдущая дата примет значение NULL.
На финальном этапе мы соединяем полученную таблицу ВТ_СопоставлениеДат обратно с физической таблицей регистра сведений, чтобы вытащить числовое значение цены на найденную предыдущую дату.
ВЫБРАТЬ
ВТ_СопоставлениеДат.Номенклатура КАК Номенклатура,
ВТ_СопоставлениеДат.ВидЦены КАК ВидЦены,
ВТ_СопоставлениеДат.ПериодТекущий КАК ДатаПереоценки,
ВТ_СопоставлениеДат.ЦенаТекущая КАК ЦенаНовая,
ЕСТЬNULL(ЦеныНоменклатурыПредыдущие.Цена, 0) КАК ЦенаСтарая
ИЗ
ВТ_СопоставлениеДат КАК ВТ_СопоставлениеДат
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатурыПредыдущие
ПО ВТ_СопоставлениеДат.Номенклатура = ЦеныНоменклатурыПредыдущие.Номенклатура
И ВТ_СопоставлениеДат.ВидЦены = ЦеныНоменклатурыПредыдущие.ВидЦены
И ВТ_СопоставлениеДат.ПериодПредыдущий = ЦеныНоменклатурыПредыдущие.Период
УПОРЯДОЧИТЬ ПО
Номенклатура,
ВидЦены,
ДатаПереоценки УБЫВ
Данный пакетный запрос полностью решает задачу в рамках чистого SQL, транслируемого платформой 1С. Однако у этого подхода есть свои подводные камни, связанные с производительностью СУБД.
---
Условие левого соединения вида Таблица1.Период > Таблица2.Период (неэквивалентное соединение) на уровне СУБД (особенно MS SQL Server или PostgreSQL) является ресурсоемкой операцией. Если в базе данных хранятся миллионы записей цен, такой запрос может привести к длительному сканированию индексов и таблиц (Index Scan / Table Scan), загружая процессор сервера на 100%.
Для оптимизации работы СУБД следуйте следующим правилам:
ИНДЕКСИРОВАТЬ ПО для всех временных таблиц, участвующих в соединениях.СрезПоследних, а для остальных записей внутри периода сопоставлять их смещением строк.---
Если перед вами стоит задача разработать отчет на базе Системы компоновки данных (СКД), то писать тяжелые самосоединения таблиц в запросе вообще нет необходимости. В СКД встроен мощный аналитический аппарат работы с выборкой данных, аналог оконных функций в СУБД SQL.
Для получения предыдущей цены мы можем использовать встроенную функцию СКД ВычислитьВыражение. Выполним настройку по шагам:
ВЫБРАТЬ
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
ЦеныНоменклатуры.ВидЦены КАК ВидЦены,
ЦеныНоменклатуры.Период КАК Период,
ЦеныНоменклатуры.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ГДЕ
ЦеныНоменклатуры.Период МЕЖДУ &НачалоПериода И &КонецПериода
ЦенаПредыдущаяВычислитьВыражение("Цена", "Период", "Группировка", "Предыдущая", "Предыдущая")
Как это работает: Функция СКД ВычислитьВыражение анализирует полученный результат запроса уже в оперативной памяти сервера приложений 1С. Параметр "Предыдущая" указывает системе взять значение ресурса Цена из предыдущей строки сформированного отчета в разрезе текущей группировки (например, Номенклатуры). Это происходит мгновенно и не создает никакой нагрузки на сервер СУБД, устраняя риск зависания базы данных.
---
Еще один элегантный способ в СКД — связать два набора данных напрямую через интерфейс платформы. Этот метод отлично подходит, если вам нужно сопоставить цены с документами движения (например, с табличной частью документа Реализация товаров и услуг).
Проанализируем архитектуру этого метода:
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
Продажи.Период КАК ДатаПродажи,
Продажи.Количество КАК Количество
ИЗ
РегистрНакопления.Продажи КАК Продажи
ВЫБРАТЬ
ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
ЦеныНоменклатурыСрезПоследних.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ПериодЦен, Номенклатура = &ПараметрНоменклатура) КАК ЦеныНоменклатурыСрезПоследних
НаборДанных1НаборДанных2Номенклатура = НоменклатураПериодЦен, Выражение источника — ДатаПродажи. Имя второго параметра — ПараметрНоменклатура, Выражение источника — Номенклатура.При формировании такого отчета СКД автоматически выполнит оптимизированные параметризованные запросы к срезу последних цен для каждой даты продажи индивидуально. Это исключает необходимость написания сложных соединений с условием «меньше» вручную.
---
Выбор оптимального метода зависит от архитектуры вашего решения и условий эксплуатации: