При работе с периодическими регистрами сведений в 1С начинающие (и не только) разработчики часто сталкиваются со специфическим поведением виртуальной таблицы СрезПоследних. Типичный пример — регистр сведений ЦеныНоменклатурыКонтрагентов. Этот регистр, как правило, имеет несколько измерений: Номенклатура, Контрагент и ТипЦен. Когда перед нами встает задача получить просто самую последнюю цену по конкретной номенклатуре (без привязки к контрагенту или типу цен), мы пишем простой запрос к виртуальной таблице СрезПоследних, выбирая только поля Номенклатура и Цена. Однако на выходе мы получаем несколько строк для одной и той же номенклатуры. Для пользователя это выглядит как появление дублей.
В этой статье мы подробно разберем, почему так происходит, проанализируем внутренние механизмы работы платформы 1С и СУБД, а также рассмотрим четыре практических способа решения этой задачи в зависимости от объема ваших данных и архитектуры системы.
Для того чтобы эффективно решить проблему, необходимо понять её природу. Виртуальная таблица СрезПоследних строится платформой 1С на уровне СУБД. При этом расчет среза происходит строго по всему набору измерений, которые заданы для регистра сведений в конфигураторе.
Если для регистра определены измерения Номенклатура, Контрагент и ТипЦен, то система будет искать самую последнюю запись для каждого уникального сочетания этих трех полей. Даже если в самом запросе вы не выбираете поля Контрагент и ТипЦен, СУБД все равно выполнит расчет с их учетом. В результате в выборку попадут последние цены по разным контрагентам и разным типам цен для одной и той же номенклатуры. Когда эти данные выводятся в отчет без «лишних» колонок, строки кажутся идентичными по номенклатуре, но содержат разные цены.
Поскольку в языке запросов 1С отсутствуют стандартные оконные функции SQL (такие как ROW_NUMBER() OVER(PARTITION BY...)), нам приходится использовать альтернативные методы построения запросов. Рассмотрим их подробнее.
Если нам необходимо получить строго одну, самую актуальную по времени цену для каждой номенклатуры, проигнорировав остальные измерения, мы можем обратиться к реальной (физической) таблице регистра. Этот подход строится на использовании временных таблиц и группировок.
Разберем этот метод по шагам:
Номенклатура) и находим максимальную дату записи с помощью функции МАКСИМУМ(Период). Этот результат мы сохраняем во временную таблицу.Номенклатура и Период. Из физической таблицы мы забираем значение ресурса Цена.Посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
ЦеныРегистра.Номенклатура КАК Номенклатура,
МАКСИМУМ(ЦеныРегистра.Период) КАК Период
ПОМЕСТИТЬ ВТ_МаксимальныеДаты
ИЗ
РегистрСведений.ЦеныНоменклатурыКонтрагентов КАК ЦеныРегистра
ГДЕ
ЦеныРегистра.Период <= &ПериодГраницы
ГРУППИРОВАТЬ ПО
ЦеныРегистра.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_МаксимальныеДаты.Номенклатура КАК Номенклатура,
МАКСИМУМ(ЦеныРегистра.Цена) КАК Цена
ИЗ
ВТ_МаксимальныеДаты КАК ВТ_МаксимальныеДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатурыКонтрагентов КАК ЦеныРегистра
ПО ВТ_МаксимальныеДаты.Номенклатура = ЦеныРегистра.Номенклатура
И ВТ_МаксимальныеДаты.Период = ЦеныРегистра.Период
ГРУППИРОВАТЬ ПО
ВТ_МаксимальныеДаты.Номенклатура
Важный нюанс: Обратите внимание на использование группировки и функции МАКСИМУМ(Цена) во втором запросе пакета. Это критически важно. Если в одну и ту же секунду (период регистра) для одной и той же номенклатуры были внесены записи по разным контрагентам или типам цен, то простое соединение по периоду и номенклатуре снова выдаст дубли строк. Использование агрегирующей функции (например, выбор максимальной или минимальной цены) гарантирует получение ровно одной строки на каждую номенклатуру.
Предыдущий способ отлично работает на небольших базах данных. Однако если физическая таблица вашего регистра содержит миллионы записей, поиск максимума по всей таблице (даже при наличии индексов) может привести к существенному замедлению работы системы и сканированию всей таблицы (Table Scan) на уровне СУБД.
Чтобы оптимизировать этот процесс, мы можем сначала резко сократить объем обрабатываемых данных с помощью виртуальной таблицы СрезПоследних, а уже затем свернуть результат. Платформа 1С очень быстро отсекает неактуальные исторические записи на уровне СУБД при обращении к виртуальной таблице.
Проанализируем алгоритм оптимизированного метода:
СрезПоследних. На этом этапе мы получаем последние записи по всем измерениям регистра и помещаем их во временную таблицу ВТ_СрезВсех. Количество этих записей в сотни раз меньше, чем в физической таблице.ВТ_СрезВсех мы находим максимальный период для каждой номенклатуры и помещаем результат в ВТ_МаксПериод.ВТ_МаксПериод и ВТ_СрезВсех) по полям Номенклатура и Период для получения конечной цены.Посмотрим на пример кода, реализующий этот подход:
ВЫБРАТЬ
СрезРегистра.Номенклатура КАК Номенклатура,
СрезРегистра.Период КАК Период,
СрезРегистра.Цена КАК Цена
ПОМЕСТИТЬ ВТ_СрезВсех
ИЗ
РегистрСведений.ЦеныНоменклатурыКонтрагентов.СрезПоследних(&ПериодГраницы, ) КАК СрезРегистра
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_СрезВсех.Номенклатура КАК Номенклатура,
МАКСИМУМ(ВТ_СрезВсех.Период) КАК Период
ПОМЕСТИТЬ ВТ_МаксПериод
ИЗ
ВТ_СрезВсех КАК ВТ_СрезВсех
ГРУППИРОВАТЬ ПО
ВТ_СрезВсех.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_МаксПериод.Номенклатура КАК Номенклатура,
МАКСИМУМ(ВТ_СрезВсех.Цена) КАК Цена
ИЗ
ВТ_МаксПериод КАК ВТ_МаксПериод
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_СрезВсех КАК ВТ_СрезВсех
ПО ВТ_МаксПериод.Номенклатура = ВТ_СрезВсех.Номенклатура
И ВТ_МаксПериод.Период = ВТ_СрезВсех.Период
ГРУППИРОВАТЬ ПО
ВТ_МаксПериод.Номенклатура
Этот метод работает значительно быстрее на больших базах данных, так как СУБД оперирует локальными временными таблицами малого объема, избегая тяжелых блокировок и длительного сканирования физических индексов основного регистра.
Иногда перед разработчиком стоит задача решить проблему в рамках одного-единственного запроса без использования временных таблиц и пакетов запросов. Это часто требуется при разработке сложных схем компоновки данных (СКД), где использование временных таблиц может накладывать определенные ограничения или усложнять кастомизацию отчета пользователем.
В этом случае можно прибегнуть к математическому трюку, который связывает дату (период) записи и её значение в один числовой показатель. Идея заключается в следующем: чем позже сделана запись, тем меньше секунд прошло с этой даты до некоторой будущей точки отсчета (например, до конца периода отчета). Мы можем умножить эту разницу в секундах на очень большое число и прибавить цену.
Разберем математическую логику выражения:
РАЗНОСТЬДАТ(Период, &КонецПериода, СЕКУНДА) * &БольшоеЧисло + Цена
Где &БольшоеЧисло — это константа (например, 10 000 000), гарантированно превышающая любую возможную цену товара. При группировке по номенклатуре мы ищем минимальное значение этого выражения. Минимальным оно будет там, где разность дат минимальна (то есть дата записи максимально близка к &КонецПериода — самая «свежая» запись).
Для восстановления исходного значения цены из полученного минимума мы производим обратное вычитание:
ВЫБРАТЬ
ЦеныРегистра.Номенклатура КАК Номенклатура,
МИНИМУМ(РАЗНОСТЬДАТ(ЦеныРегистра.Период, &КонецПериода, СЕКУНДА) * 10000000 + ЦеныРегистра.Цена)
- МИНИМУМ(РАЗНОСТЬДАТ(ЦеныРегистра.Период, &КонецПериода, СЕКУНДА) * 10000000) КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатурыКонтрагентов КАК ЦеныРегистра
ГДЕ
ЦеныРегистра.Период <= &КонецПериода
ГРУППИРОВАТЬ ПО
ЦеныРегистра.Номенклатура
Предупреждение: Данный метод является недокументированным хаком. Он сложен для понимания другими разработчиками, затрудняет поддержку кода и может приводить к ошибкам переполнения типов в СУБД, если константа выбрана некорректно. Используйте его только в крайних случаях, когда другие методы технически нереализуемы.
Если в вашей системе запросы по получению «последней цены вообще» (без учета разрезов по контрагентам и типам цен) выполняются регулярно, встроены в критически важные бизнес-процессы (например, динамический расчет цен в документах продажи или регулярная выгрузка на сайт) и вызывают просадки производительности — это явный признак архитектурной недоработки базы данных.
Вместо постоянного написания сложных и ресурсоемких запросов, правильным решением будет оптимизация структуры данных:
ПоследниеЦеныНоменклатуры.Номенклатура, а в качестве ресурса — Цена.ЦеныНоменклатурыКонтрагентов система автоматически записывала актуальную цену и в новый, упрощенный регистр.При таком подходе вам больше не понадобятся ухищрения в запросах. Для получения актуальной цены будет достаточно написать максимально быстрый и простой запрос к виртуальной таблице нового регистра:
ВЫБРАТЬ
РегистрЦен.Номенклатура КАК Номенклатура,
РегистрЦен.Цена КАК Цена
ИЗ
РегистрСведений.ПоследниеЦеныНоменклатуры.СрезПоследних(&Период, ) КАК РегистрЦен
Это архитектурное решение позволит вашей базе данных работать на максимальной скорости, снизит нагрузку на СУБД и значительно упростит дальнейшую поддержку конфигурации.