Каждый разработчик 1С рано или поздно сталкивается с задачей анализа изменения цен. Бизнес часто требует предоставить отчет, в котором наглядно видно: какая цена была у товара ранее, какая цена установлена сейчас, и действительно ли произошло изменение. На первый взгляд задача кажется тривиальной, однако попытки написать простой запрос «в лоб» часто приводят к критическим ошибкам производительности или неверным результатам.
В этой статье мы подробно разберем, как правильно составить запрос для получения старой и новой цены номенклатуры, проанализируем ошибки популярных подходов и рассмотрим элегантное решение этой задачи с помощью Системы компоновки данных (СКД).
Проанализируем ситуацию, которая часто возникает при самостоятельном решении этой задачи. Начинающие разработчики часто пытаются решить задачу через табличные части документов установки цен или неоптимальные соединения таблиц. Выясним причину, почему так делать не стоит:
Документ.УстановкаЦенНоменклатуры.Товары). Это одна из самых распространенных ошибок. Документ может быть просто перепроведен без фактического изменения цен, либо цены могут быть изменены другими документами (например, поступлением товаров). Единственным достоверным источником информации о ценах является регистр сведений ЦеныНоменклатуры.ПЕРВЫЕ 2 с сортировкой по убыванию даты. Этот метод отлично работает, если нам нужно получить историю изменения цен строго по одной выбранной позиции номенклатуры. Однако, если требуется вывести список изменений по всему справочнику или по определенной группе товаров за один раз, этот метод полностью неприменим, так как конструкция ПЕРВЫЕ ограничит выборку всего пакета данных, а не внутри группировки по каждому товару отдельно.<>) для периодов. Соединение таблиц по условию равенства номенклатур и неравенства периодов заставляет СУБД выполнять полное сканирование таблиц (Table Scan / Index Scan). На реальной базе данных с сотнями тысяч записей это приведет к зависанию запроса и перегрузке сервера.Чтобы получить старую и новую цену для списка номенклатуры без лишней нагрузки на сервер базы данных, разберем по шагам оптимальный алгоритм построения запроса.
Шаг 1. Получение актуальной цены (Новой цены). Нам необходимо обратиться к виртуальной таблице СрезПоследних регистра сведений ЦеныНоменклатуры. Важно выбрать не только саму цену, но и Период — то есть дату, с которой эта цена начала действовать. Поместим этот результат во временную таблицу.
Шаг 2. Поиск даты предыдущего изменения цены. Для этого мы берем нашу временную таблицу с новыми ценами и соединяем ее с реальной (физической) таблицей регистра сведений ЦеныНоменклатуры. Условием соединения будет равенство номенклатуры и строгое условие, что период в физической таблице меньше периода новой цены. Сгруппировав результат по номенклатуре и выбрав максимум от периода, мы получим точную дату предыдущего изменения цены.
Шаг 3. Получение старой цены. Теперь мы соединяем полученную таблицу предыдущих дат с физической таблицей регистра по равенству номенклатуры и периода. Так мы вытаскиваем точное числовое значение старой цены.
Шаг 4. Финальное объединение и фильтрация. На последнем этапе мы соединяем новые цены со старыми ценами. Чтобы отсечь товары, у которых цена никогда не менялась (была установлена только один раз), мы используем фильтрацию. Также применим функцию ЕСТЬNULL, чтобы корректно обработать новые товары.
Посмотрим на пример готового оптимизированного запроса:
ВЫБРАТЬ
ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
ЦеныНоменклатурыСрезПоследних.Цена КАК НоваяЦена,
ЦеныНоменклатурыСрезПоследних.Период КАК ПериодНовойЦены,
ЦеныНоменклатурыСрезПоследних.ТипЦен КАК ТипЦен
ПОМЕСТИТЬ ВТ_НовыеЦены
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследних
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_НовыеЦены.Номенклатура КАК Номенклатура,
МАКСИМУМ(ЦеныНоменклатуры.Период) КАК ПредыдущийПериод
ПОМЕСТИТЬ ВТ_ПредыдущиеДаты
ИЗ
ВТ_НовыеЦены КАК ВТ_НовыеЦены
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО ВТ_НовыеЦены.Номенклатура = ЦеныНоменклатуры.Номенклатура
И ВТ_НовыеЦены.ТипЦен = ЦеныНоменклатуры.ТипЦен
И ВТ_НовыеЦены.ПериодНовойЦены > ЦеныНоменклатуры.Период
СГРУППИРОВАТЬ ПО
ВТ_НовыеЦены.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ПредыдущиеДаты.Номенклатура КАК Номенклатура,
ЦеныНоменклатуры.Цена КАК СтараяЦена
ПОМЕСТИТЬ ВТ_СтарыеЦены
ИЗ
ВТ_ПредыдущиеДаты КАК ВТ_ПредыдущиеДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО ВТ_ПредыдущиеДаты.Номенклатура = ЦеныНоменклатуры.Номенклатура
И ВТ_ПредыдущиеДаты.ПредыдущийПериод = ЦеныНоменклатуры.Период
И ЦеныНоменклатуры.ТипЦен = &ТипЦен
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_НовыеЦены.Номенклатура КАК Номенклатура,
ВТ_НовыеЦены.НоваяЦена КАК НоваяЦена,
ВТ_НовыеЦены.ПериодНовойЦены КАК ДатаИзменения,
ЕСТЬNULL(ВТ_СтарыеЦены.СтараяЦена, 0) КАК СтараяЦена
ИЗ
ВТ_НовыеЦены КАК ВТ_НовыеЦены
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СтарыеЦены КАК ВТ_СтарыеЦены
ПО ВТ_НовыеЦены.Номенклатура = ВТ_СтарыеЦены.Номенклатура
ГДЕ
ВТ_НовыеЦены.НоваяЦена <> ЕСТЬNULL(ВТ_СтарыеЦены.СтараяЦена, 0)
И ВТ_НовыеЦены.ПериодНовойЦены МЕЖДУ &НачалоПериода И &КонецПериода
Если перед вами стоит задача разработать отчет, рассмотрим подробнее, как решить ее без написания громоздких и тяжелых запросов. В СКД встроен великолепный механизм под названием «Срез последних на каждую дату». Это позволяет переложить всю рутину по расчету предыдущих состояний на саму платформу 1С.
Разберем по шагам настройку схемы компоновки данных:
ЦеныНоменклатуры за интересующий нас период. Нам нужны поля Номенклатура, Период, ТипЦен и актуальная Цена (она же новая цена).
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ПериодСреза, Номенклатура = &Номенклатура И ТипЦен = &ТипЦен)
Номенклатура = Номенклатура и ТипЦен = ТипЦен.ДобавитьКДате(Период, "Секунда", -1).Платформа 1С автоматически выполнит срез последних на момент времени, предшествующий на одну секунду текущей дате изменения цены. Это вернет нам чистую предыдущую цену без необходимости писать сложные вложенные циклы и тяжелые соединения временных таблиц в основном запросе!
Если вы работаете в современных конфигурациях, таких как ERP Управление предприятием, Комплексная автоматизация или Управление торговлей, важно учитывать архитектурные особенности хранения цен, чтобы ваш запрос не возвращал некорректные или дублирующиеся данные:
Номенклатура, но и по полю Характеристика. В противном случае запрос сопоставит цену одной характеристики со старой ценой совершенно другой характеристики этого же товара.ВидЦены (или ТипЦен). Накладывайте жесткие фильтры на конкретные виды цен или делайте по ним группировки в условиях соединения.Валюта, иначе сравнение числовых показателей будет экономически бессмысленным.В классических реляционных СУБД (MS SQL Server, PostgreSQL, Oracle) подобные задачи с историчностью строк изящно решаются с помощью аналитических оконных функций, таких как LAG() или LEAD(). Они позволяют «заглянуть» на предыдущую строку в пределах группировки без повторного сканирования и соединения таблиц.
Несмотря на то, что фирма «1С» активно развивает язык запросов (например, добавляя новые математические и строковые функции в версиях платформы 8.3.20 и выше), оконные функции в платформе 1С до сих пор не поддерживаются. Именно поэтому использование временных таблиц, группировок по максимальной дате и соединений по периоду остается единственным надежным, штатным и производительным инструментом для любого разработчика 1С при анализе истории изменения данных.