Как получить последнюю и предпоследнюю цену по всей номенклатуре в одном запросе 1С?

← На главную

В разработке на платформе 1С:Предприятие довольно часто возникает задача сравнительного анализа данных во времени. Типичный пример — необходимость вывести по всей номенклатуре две последние цены (самую свежую и предшествующую ей), а также рассчитать процент их изменения. Для регулярного контроля рынка и получения внешних данных для пересмотра цен применяется автоматизированный мониторинг цен в интернете. На первый взгляд задача кажется простой, однако при попытке решить её в рамках одного запроса по всей базе данных (без фильтрации по конкретной номенклатуре) разработчики сталкиваются со специфическими ограничениями языка запросов 1С.

В этой статье мы подробно разберем, почему эта задача вызывает трудности, проанализируем различные подходы к её решению, построим оптимальный запрос с использованием временных таблиц и рассмотрим альтернативный (более производительный) вариант реализации с помощью Системы компоновки данных (СКД).

В чем сложность? Ограничения языка запросов 1С

В классических реляционных СУБД (например, MS SQL Server, PostgreSQL или Oracle) подобная задача решается элементарно с помощью аналитических (оконных) функций. Использование конструкции ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) или функции LAG() позволяет за один проход по таблице сопоставить текущую запись с предыдущей.

Однако в языке запросов 1С поддержка оконных функций отсутствует. Мы не можем «заглянуть» на одну строку назад внутри выборки. Из-за этого приходится строить многошаговые алгоритмы с использованием временных таблиц, группировок и соединений физических таблиц с виртуальными, что при неправильном проектировании может привести к колоссальному падению производительности на больших базах данных.

Давайте вместе разберем, как построить этот запрос правильно, избежав типичных ошибок производительности.

Разбор логики решения задачи

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

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

Давайте перейдем к практической реализации этого алгоритма в виде пакета запросов 1С.

Оптимальный запрос 1С (Пошаговый разбор)

Ниже представлен готовый структурированный запрос, который решает поставленную задачу. Обратите внимание, что мы учитываем не только саму номенклатуру, но и её характеристику. Это критически важно в реальных базах (например, в 1С:УТ, 1С:КА или 1С:ERP), так как цены часто устанавливаются в разрезе характеристик товара.


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

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

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

СГРУППИРОВАТЬ ПО
    ВТ_Последние.Номенклатура,
    ВТ_Последние.Характеристика

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

////////////////////////////////////////////////////////////////////////////////
// ШАГ 3. Получаем числовое значение предпоследней цены по найденной дате
ВЫБРАТЬ
    ВТ_ПредыдущиеДаты.Номенклатура КАК Номенклатура,
    ВТ_ПредыдущиеДаты.Характеристика КАК Характеристика,
    ВТ_ПредыдущиеДаты.ПериодПредыдущий КАК ПериодПредыдущий,
    ЦеныФизическая.Цена КАК ЦенаПредыдущая
ПОМЕСТИТЬ ВТ_ПредыдущиеЦены
ИЗ
    ВТ_ПредыдущиеДаты КАК ВТ_ПредыдущиеДаты
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныФизическая
        ПО ВТ_ПредыдущиеДаты.Номенклатура = ЦеныФизическая.Номенклатура
            И ВТ_ПредыдущиеДаты.Характеристика = ЦеныФизическая.Характеристика
            И ЦеныФизическая.ТипЦен = &ТипЦен
            И ВТ_ПредыдущиеДаты.PeriodПредыдущий = ЦеныФизическая.Период

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

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

Проанализируем важные детали реализации

Для того чтобы данный запрос работал максимально быстро и возвращал корректные данные на реальной базе, мы применили несколько важных приемов:

Индексация временных таблиц: Обратите внимание на инструкцию ИНДЕКСИРОВАТЬ ПО после каждого пакета ПОМЕСТИТЬ. Индексирование по ключевым полям связи (Номенклатура, Характеристика) позволяет СУБД мгновенно выполнять последующие соединения. Без этого на базе в десятки тысяч товаров запрос начнет серьезно «тормозить».
Фильтрация на ранних этапах: В первом же шаге при получении среза последних мы жестко ограничиваем выборку параметром ТипЦен = &ТипЦен. Это отсекает лишние данные и предотвращает избыточное сканирование таблиц.
Защита от деления на 0: В финальном пакете мы используем конструкцию ВЫБОР КОГДА ... ТОГДА. Если у товара не было более ранней цены (например, товар новый и цена устанавливалась только один раз), поле ЦенаПредыдущая вернет NULL, которое функция ЕСТЬNULL преобразует в 0. Без проверки на равенство нулю СУБД выдаст критическую ошибку деления на ноль при попытке рассчитать процент.
Левое соединение на финальном шаге: Мы соединяем таблицы через ЛЕВОЕ СОЕДИНЕНИЕ. Это гарантирует, что в итоговый отчет попадет абсолютно вся номенклатура, даже та, по которой была всего одна установка цен. У таких позиций в колонках предыдущей цены просто отобразятся нули.

Альтернативный вариант: Использование возможностей СКД

Если ваша задача — вывести эти данные в отчет для пользователей, то писать столь громоздкий запрос в самой СУБД вовсе не обязательно. Платформа 1С предоставляет мощнейший инструмент аналитики — Систему компоновки данных (СКД). В процессор компоновки встроены функции, имитирующие оконные функции SQL.

Для решения нашей задачи в СКД мы можем написать максимально простой запрос, который выгрузит просто всю историю изменения цен:


ВЫБРАТЬ
    Цены.Номенклатура КАК Номенклатура,
    Цены.Характеристика КАК Характеристика,
    Цены.Период КАК Период,
    Цены.Цена КАК Цена
ИЗ
    РегистрСведений.ЦеныНоменклатуры КАК Цены
ГДЕ
    Цены.ТипЦен = &ТипЦен

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

Рассмотрим пример настройки ресурса для получения предыдущей цены:

Выражение для ресурса ПредыдущаяЦена будет выглядеть следующим образом:

ВычислитьВыражение("Цена", "Период", "Группировка", "Предыдущая", "Предыдущая")

Давайте разберем параметры этой функции:

"Цена" — поле, значение которого мы хотим получить.
"Период" — поле, по которому осуществляется упорядочивание записей в истории.
"Группировка" — область вычисления (в нашем случае вычисления будут производиться строго в рамках группировки по конкретной номенклатуре).
"Предыдущая" — смещение начала диапазона (берем предыдущую строку).
"Предыдущая" — смещение конца диапазона.

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

Резюме

Мы детально разобрали два подхода к решению задачи получения последней и предпоследней цены в 1С. Выбор конкретного метода зависит от архитектуры вашей задачи:

Используйте чистый запрос на временных таблицах, если вам необходимо получить эти данные непосредственно в коде (например, в модуле проведения документа, в обработке заполнения или при интеграции с сайтом). Для автоматической синхронизации цен в личных кабинетах площадок применяется интеграция 1С с маркетплейсами Ozon, Wildberries и Яндекс.Маркет.

Используйте функции СКД (ВычислитьВыражение), если вы разрабатываете пользовательский отчет. Это сбережет ресурсы сервера и значительно упростит поддержку кода в будущем.