В практике разработки на платформе 1С часто возникает задача анализа динамики цен. Типичный пример — отчет, в котором нужно вывести текущую цену, предыдущую цену и процент их изменения. Если получение последней цены на дату — задача тривиальная (использование виртуальной таблицы СрезПоследних), то поиск «предпоследней» записи требует более изощренных подходов в языке запросов. В этой статье мы разберем несколько эффективных методов решения этой задачи: от классических соединений до современных инструментов, таких как АВТОНОМЕРЗАПИСИ и оконные функции.
Если ваша конфигурация работает на старых версиях платформы (ниже 8.3.12), или вы хотите использовать максимально совместимый код, подойдет классическая схема с использованием временных таблиц и агрегатных функций. Проанализируем ситуацию: чтобы найти предпоследнюю цену, нам нужно сначала найти дату последней цены, а затем найти максимальную дату среди тех, что меньше последней.
Рассмотрим по шагам реализацию этого алгоритма:
МАКСИМУМ(Период).Пример реализации такого запроса:
// Шаг 1: Получаем последнюю дату цены
ВЫБРАТЬ
Цены.Номенклатура КАК Номенклатура,
МАКСИМУМ(Цены.Период) КАК ПериодПоследний
ПОМЕСТИТЬ ВтПоследниеДаты
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК Цены
ГДЕ
Цены.Период <= &ВыбраннаяДата
СГРУППИРОВАТЬ ПО
Цены.Номенклатура;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Получаем предпоследнюю дату (максимум среди тех, что меньше последней)
ВЫБРАТЬ
Цены.Номенклатура КАК Номенклатура,
МАКСИМУМ(Цены.Период) КАК ПериодПредпоследний
ПОМЕСТИТЬ ВтПредпоследниеДаты
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК Цены
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВтПоследниеДаты КАК ВтПоследниеДаты
ПО Цены.Номенклатура = ВтПоследниеДаты.Номенклатура
И Цены.Период < ВтПоследниеДаты.ПериодПоследний
ГДЕ
Цены.Период <= &ВыбраннаяДата
СГРУППИРОВАТЬ ПО
Цены.Номенклатура;
Этот метод надежен, но может работать медленно на очень больших объемах данных, так как требует нескольких сканирований таблицы регистра.
С появлением функции АВТОНОМЕРЗАПИСИ решение подобных задач стало более элегантным. Эта функция позволяет пронумеровать строки во временной таблице. Основная хитрость здесь заключается в том, что нумерация должна происходить в строго определенном порядке, но временные таблицы в 1С сами по себе не гарантируют порядок записей.
Разберем важный нюанс: чтобы АВТОНОМЕРЗАПИСИ сработал корректно для нашей задачи, данные во временную таблицу нужно помещать с использованием конструкции ПЕРВЫЕ и УПОРЯДОЧИТЬ ПО. Выясним причину: только при наличии ПЕРВЫЕ оптимизатор SQL гарантированно выполнит сортировку перед вставкой записей и последующим присвоением номера.
Посмотрим на пример логики получения последних N записей:
ВЫБРАТЬ ПЕРВЫЕ 1000000000
Цены.Номенклатура КАК Номенклатура,
Цены.Период КАК Период,
Цены.Цена КАК Цена,
АВТОНОМЕРЗАПИСИ() КАК Порядок
ПОМЕСТИТЬ ВтПронумерованныеЦены
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК Цены
ГДЕ
Цены.Период <= &ВыбраннаяДата
УПОРЯДОЧИТЬ ПО
Номенклатура,
Период УБЫВ;
Проанализируем этот запрос: мы отсортировали цены по номенклатуре и по дате в обратном порядке. Теперь для каждой номенклатуры запись с Порядок = 1 теоретически должна быть последней. Однако, так как АВТОНОМЕРЗАПИСИ сквозной для всей таблицы, нам нужно найти минимальный номер (смещение) для каждой группы номенклатуры и вычислить относительный номер.
Если ваша платформа обновлена до версии 8.3.20 и выше, забудьте про сложные соединения и нумерацию временных таблиц. В 1С появились оконные функции, которые решают задачу поиска предпоследней записи одним легким движением.
Функция ROW_NUMBER() с конструкцией OVER(PARTITION BY ... ORDER BY ...) позволяет пронумеровать строки внутри группы (по номенклатуре). В этом случае последняя цена всегда будет иметь номер 1, а предпоследняя — номер 2.
Разберем пример запроса:
ВЫБРАТЬ
Цены.Номенклатура КАК Номенклатура,
Цены.Цена КАК Цена,
Цены.Период КАК Период,
ROW_NUMBER() OVER (PARTITION BY Цены.Номенклатура УПОРЯДОЧИТЬ ПО Цены.Период УБЫВ) КАК НомерСтроки
ПОМЕСТИТЬ ВтВсеЦеныСНомерами
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК Цены
ГДЕ
Цены.Период <= &ВыбраннаяДата;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Номенклатура,
Т1.Цена КАК ЦенаПоследняя,
Т2.Цена КАК ЦенаПредпоследняя,
ВЫБОР
КОГДА Т2.Цена = 0 ИЛИ Т2.Цена ЕСТЬ NULL ТОГДА 0
ИНАЧЕ (Т1.Цена - Т2.Цена) / Т2.Цена * 100
КОНЕЦ КАК ПроцентИзменения
ИЗ
ВтВсеЦеныСНомерами КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВтВсеЦеныСНомерами КАК Т2
ПО Т1.Номенклатура = Т2.Номенклатура И Т2.НомерСтроки = 2
ГДЕ
Т1.НомерСтроки = 1
При вычислении разницы в процентах крайне важно предусмотреть исключительные ситуации, чтобы запрос не завершился ошибкой или не выдал некорректные данные:
ВЫБОР, как показано в примере выше.ЛЕВОЕ СОЕДИНЕНИЕ вернет NULL, что также нужно обрабатывать функцией ЕСТЬNULL(..., 0).ORDER BY второе поле, например, Регистратор или НомерСтроки самого регистра.Резюме: Мы рассмотрели три способа решения задачи. Для старых платформ используйте метод с последовательным поиском максимумов. Для средних версий (от 8.3.12 до 8.3.19) эффективным будет АВТОНОМЕРЗАПИСИ во временной таблице. Для современных систем оптимальным и наиболее читаемым решением являются оконные функции.